Sql-server – Speed problem with a simple select statement

database-designsql-server-2005

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.

Best Answer

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.

Related Question