Why is this query taking forever!!? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Why is this query taking forever!!?

[<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />!]<br /><br />Please help! Why does this query take forever to run!? <br /><br />Here are the facts:<br /><br />I am writing a system that looks after customer loans.<br />I have a table (let’s call it tbl_Loan). This contains the customer number and the corresponding loan reference for that customer. <br /><br />eg; CustomerNo = 123456, LoanID = 101010<br /><br />I have a view (let’s call it vw_Sales) which contains thousands and thousands of records – five years of invoice lines to be exact.<br />It has the columns CustomerNo, InvoiceNo, Date, Volume, Value etc etc<br /><br />If I write the following query:<br /><br />SELECT *<br />FROM vw_Sales S<br />INNER JOIN tbl_Loan L ON S.CustomerNo=L.CustomerNo<br />WHERE S.CustomerNo=’123456’ AND InvoiceDate&gt;=’3-May-2002′<br /><br />I get my results (all 28 lines!) within a second.<br /><br />However, if I write the query:<br /><br />SELECT *<br />FROM vw_Sales S<br />INNER JOIN tbl_Loan L ON S.CustomerNo=L.CustomerNo<br />WHERE S.LoanID=101010 AND InvoiceDate&gt;=’3-May-2002′<br /><br />I am expecting exactly the same results as the first query but this time the query takes over 8 minutes! Why is this?<br /><br />I have tried adding indexes, removing indexes, changing the order of the query etc etc etc but I can’t speed it up!<br /><br />Help me!!<br /><br />Thanks<br /><br />Mark "Buzby" Beeton<br /><br />I’m moving to Theory.<br />Everything works there.
Without seeing the execution plans, my guess is that the second query is doing a table scan, which is why it runs much slower. Is it possible for you to post the execution plans of both queries so that we can see them? Also, I assume you have indexes on CustomerNO, LoadID, and InvoiceDate? Which of these keys is the clustered key? ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Maybe I’m not understanding this correctly, but it looks like all the conditions in your WHERE clause correspond to fields in table S, vw_Sales. Why are you using a join at all? Unless you are trying to pull the LoanID also. Is the field LoanID or CustomerNo a key field?
Is table ‘tbl_Loan’ part of the view ‘vw_Sales’ ? -theSauce
]]>