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.