Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having a problem with a select statement taking way too long to run. I would like to know if I'm doing something wrong or if it taking this long makes sense. I am using Microsoft SQL 2005 standard. The select I am running takes about 30 seconds to run and return about 60000 records. Here's the sql:

SELECT l.ID, l.Number, l.Date, l.FirstName + ' ' + l.LastName Name,
    isnull(l.Amount,0) Amount1, isnull(l.Interest,0) Interest1,
    isnull(l.Interest2,0) Interest2, l.LastPayment,
    l.NextDueDate, isnull(l.Aging,0) Aging, isnull(l.InterestRate,0) InterestRate,
    ls.DisplayValue Status, lt.DisplayValue Type, l.LocationID
    FROM Table1 l
    JOIN Table2 ls ON (l.Table2ID=ls.ID)
    JOIN Table3 lt ON (l.Table3ID=lt.ID)
    WHERE
    l.Table3ID IN (SELECT Value FROM fn_Split('5,4,3', ','))
    AND l.Table2ID IN (SELECT Value FROM fn_Split('8,20,9,14,18,21,22,26,13,10,27,25,23,24,16,3,11,6,4,12,19,17,5,28,7,15', ','))
    AND l.LocationID IN (SELECT Value FROM fn_Split('4,6,8,9,10,14,11,12,13', ','))

I've added indexes but nothing seems to speed it up. If I remove everything in the select except for l.ID and l.Number I can get it to complete in about 8 seconds, if that helps at all. This DBA stuff is not my area of expertise so any help at all would be greatly appreciated. Thank you.

share|improve this question
1  
Can you post the execution plan? Without it people are just guessing. –  mrdenny Jul 18 '11 at 20:38

4 Answers 4

Tough to say just from looking at it, but here's a good way to figure out where to start:

Run your query from SSMS with the execution plan turned on (right click anywhere and choose "Include Actual Execution Plan"). Read from right to left, top to bottom. Each icon will display a percentage - figure out where the query is spending the most time then start there.

General advice: try to turn scans into seeks by adding indexes; look for big discrepancies between estimated and actual number of rows - this indicates statistics are out of date; look at "Number of Executions" - I suspect those functions are executing many many times.

share|improve this answer
1  
+1 for looking at the execution plan. –  RateControl Jul 14 '11 at 18:49
    
Thank you this was very helpful. I got rid of the fn_split and now the estimated and actual number of rows are about the same. But I still have my problem of this query taking 30 seconds. According to execution plan: 11% Hash Match(right outer join)(2x), 24% filter, 53% Clustered Index Scan (dbo.Table1.PK_Table1). Is there something else I should look at? –  Dustin Jul 14 '11 at 19:28
    
I would try focusing on the 53% Clustered Index Scan, by trying different indexes. However, this will only work if the number of records you are returning is not too high relative to the number of records in the table. If you're returning a large percentage of the table, indexes will not help, SQL will decide it's faster do a scan (unless the index is a covering index). I've seen 30% thrown around as a threshold, I don't know how accurate that is, and I'm sure it depends on many other things. For starters, try adding indexes to the fields in JOIN and WHERE clauses. –  Henry Lee Jul 15 '11 at 11:56

Dustin,

Are you looking for 60K rows to be returned? If so, then your query may take 30 seconds if the requesting application or the network slow or restrict the data transfer.

Another thought involves the way you are forming your query. You might do better to remove the JOIN clauses as your WHERE clause makes the JOINS mostly redundant and change the ls.DisplayValue and lt.DisplayValue references in your SELECT clause to use subqueries. An example of this would be:

(SELECT DisplayValue FROM Table2 WHERE ID = l.Table2ID) AS [Status],
(SELECT DisplayValue FROM Table4 WHERE ID = l.Table3ID) AS [Type]

As always, after you make a change to your query, review the Actual Execution Plan to determine the effect of your change.

share|improve this answer

It could be the fn_split function; you might be better off taking that out of the SELECT, putting the results of each fn_split in a temp table and changing your IN statements to check the temp table instead.

SELECT * into #a FROM fn_Split('5,4,3', ',')
SELECT * into #b FROM fn_Split('8,20,9,14,18,21,22,26,13,10,27,25,23,24,16,3,11,6,4,12,19,17,5,28,7,15', ','))
SELECT * into #c FROM fn_Split('4,6,8,9,10,14,11,12,13', ',')

The best thing would be get those csv values passed in as a table rather than having to split.

share|improve this answer
    
Thanks for the response. That is good advice, but doesnt solve my problem. When I run the following it still takes 30 seconds to run and returns about 60000 records: SELECT l.ID, l.Number, l.Date, l.FirstName + ' ' + l.LastName Name, isnull(l.Amount,0) Amount1, isnull(l.Interest,0) Interest1, isnull(l.Interest2,0) Interest2, l.LastPayment, l.NextDueDate, isnull(l.Aging,0) Aging, isnull(l.InterestRate,0) InterestRate, ls.DisplayValue Status, lt.DisplayValue Type, l.LocationID FROM Table1 l JOIN Table2 ls ON (l.Table2ID=ls.ID) JOIN Table3 lt ON (l.Table3ID=lt.ID) –  Dustin Jul 14 '11 at 16:14
    
@Dustin Approximately how many rows in Table1 in total? –  SqlACID Jul 16 '11 at 18:41

Try this instead


SELECT l.ID, l.Number, l.Date, l.FirstName + ' ' + l.LastName Name,
isnull(l.Amount,0) Amount1, isnull(l.Interest,0) Interest1,
isnull(l.Interest2,0) Interest2, l.LastPayment, 
l.NextDueDate, isnull(l.Aging,0) Aging, isnull(l.InterestRate,0) InterestRate,
ls.DisplayValue Status, lt.DisplayValue Type, l.LocationID
FROM Table1 l
JOIN Table2 ls ON (l.Table2ID=ls.ID)
JOIN Table3 lt ON (l.Table3ID=lt.ID)
WHERE 
l.Table3ID IN (5,4,3)
AND l.Table2ID IN (8,20,9,14,18,21,22,26,13,10,27,25,23,24,16,3,11,6,4,12,19,17,5,28,7,15)
AND l.LocationID IN (4,6,8,9,10,14,11,12,13)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.