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
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