Speed problem with a simple select statement

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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)