sql

how to determine->improve performance of Oracle SP's

We have a lot of SP's and many of them take forever to execute. These SP's are pretty long and its a pain to go through the SP to find out which query is taking long. Whats the best way to find out which query is taking the most time? so that we can just concentrate on that query rather than spending time in research. Currently I am ...

How to increase last day count query performance

I have a table Products with products and table Sales with all sale operations that was done on these products. These tables are connected by Sales.PRODUCT_ID column. I would like to get 10 most often sold products today and what I did is this: SELECT product.* , COUNT( sale.ID ) SUMSELL FROM Products product LEFT JOIN Sales sale ...

Stored procedure executing another stored procedure

When a Stored Procedure has a query inside to execute another Stored Procedure, does the calling Stored Procedure stop other Stored Procedure has finished executing? I guess I'm trying to get straight in my head whether it's like synchronous and asynchronous calls. ...

Kohana HABTM self join

Hi I have a problem with using the ORM and creating a has and belongs to many self join using Kohana 2.3.4 I have this table Tasks ID | Title | etc...... I need to be able to link tasks to other tasks, a task can have multiple children, and multiple parents. So I was thinking of having this table. Tasks_Tasks ID | task_1_id | tas...

SQL Server 2000:Invalid operator for data type. Operator equals add, type equals ntext.

I've got some code that runs all lovely on SQL Server 2008, however I'm forced to try it on SQL Server 2000 server, where it falls down. Basically I'm looking to combine two columns, with a comma in-between. SELECT COALESCE(cardesc1, '') + ', ' + COALESCE(cardesc2, '') AS "Car Summary" FROM macros; ...

adding the value of a column into a LIKE statement?

hi there i have 3 tables for tags, tag categories and used tags. i'm wanting to get a list of all tags with a count of the used tags (the format of the tags used is comma separated values for each document id that has tags). i've been trying something like this but I can't get the value of the tags.tag field to be inserted into the LIK...

Extracting data from multiple servers SQL 2005 SSIS

I have created an SSIS package to connect to multiple SQL servers, create a database, a table and a stored procedure. The package also creates a job and schedules it to run every 5 minutes. The requirement is to collect performance metrics. I am using an ado object variable to get the server names and all the above tasks are in a for ea...

SQL optimization - execution plan changes based on constraint value - Why?

I've got a table ItemValue full of data on a SQL 2005 Server running in 2000 compatibility mode that looks something like (it's a User-Defined values table): ID ItemCode FieldID Value -- ---------- ------- ------ 1 abc123 1 D 2 abc123 2 287.23 4 xyz789 1 A 5 xyz78...

SQL UNION ALL problem after using UNION ALL more than 10 times

I'm getting a formatting problem if I use more than 10 UNION ALL statements in my VBA Code. If I use 10 or less everything works great. What I'm trying to do is combine 12 worksheets (Excel 2007). I have a numerical column called SC that turns into string and date if I have more than 10 UNION ALL. If I try to use ROUND with more than ...

In Oracle, will a compound index still be used if a where clause contains some additional fields?

So let's say I have a compound index on a table that indexes 4 fields. create index idx_comp_index on mytable ( fielda, fieldb, fieldc, fieldd ); If I query that table using all four of those fields in my where clause plus an additional field or two, will the index still be used? select * from mytable where fielda = 'i' and fieldb ...

PHP PDO Related: Update SQL Statement not Updating the content of Database

I am trying to implement update statement using prepared statement in php script but it appears that it is not update record in the database and am not sure why and so would appreciate if you can share some insights. Code $query = "UPDATE DatTable SET DF_PARTY_ID = :party_id, DF_PARTY_CODE = :party_code, DF_CONNECT...

How can I access mainframe data with .Net applications and SQL Queries?

We have a large amount of data stored on an IBM mainframe using VSAM files. A lot of this data is dropped on the network every night in the form of text files to be processed and dumped into FoxPro and SQL Server databases. There are also many text files produced nightly by custom applications that get uploaded to the mainframe to keep e...

LEFT INNER JOIN vs. LEFT OUTER JOIN - Why does the OUTER take longer?

We have the query below. Using a LEFT OUTER join takes 9 seconds to execute. Changing the LEFT OUTER to an LEFT INNER reduces the execution time to 2 seconds, and the same number of rows are returned. Since the same number of rows from the dbo.Accepts table are being processed, regardless of the join type, why would the outer take 3x lon...

Error when executing sql

I get the following error when I try and execute the code down below. I have added the sql string I pass as well. What am I missing? Error: Syntax error (missing operator) in query expression 'TOURID = 25e5eed3-ea84-4c30-a660-2c880253da60'. sql: "DELETE * FROM TOURS WHERE TOURID = 25e5eed3-ea84-4c30-a660-2c880253da60;" Dim connectio...

Check against a list of values in the database

Let's say TableA has a field called, FieldA. FieldA consists of a list of values with comma being the delimiter (i.e. 1,2,3,4). #MyVar# contains the value (a single value, NOT a list) I want to search for. Other than doing the followings, SELECT * FROM TableA WHERE FieldA LIKE '%,#MyVar#,%' OR FieldA LIKE '#MyVar#,%' OR Fi...

What's the best SQL scheme for this project?

Hi, i want to make a php app that let people submit photos/videos/sounds Now, everything uploaded a record will be added to SQL database with file infos in this way: Photos_table : File_Name | Picture_Type | Picture_Width | Picture_height Videos_table : File_Name | Duration | Codec Sounds_table : File_Name | Bitrate | Duration | Code...

difference between tsql, access sql and pl/sql

i am very confused please help to clarify these differences! are they all completely different languages? what is the overlap? ...

transfer SQL database (table and data)

hey all, iv created a database with some tables and populated them using SQL server 2008, i want to move the database to another machine, what would be the best way copy the database and recreate it in another location ?? by the way im connecting to localhost\SQLEXPRESS if thats important !! thanks ...

SQL - ISNULL Record Value

Hello, I have a SQL Statement where i need to display the value from another table if a joining record exists. To attempt this, I'm using ISNULL. As a demonstration, here is a sample query: SELECT FirstName, LastName, ISNULL(select top 1 birthdate from BirthRecords where [SSN]=p.SSN, false) as HasRecord FROM Person p Please note,...

some microsoft products and XML on an interview

here's the job description: Experience with data mapping/extracts tools such as Monarch and VorteXML. Familiarity with XML documents and schemas. Minimum 1-3 years of data analysis in SQL Server environment. Working knowledge of TSQL and SQL Server 2000 tools such as Enterprise Manager, SQL Query Analyzer, Profiler, DTS and BCP. Working...