SQL Server Performance

Why is this query taking forever!!?

Discussion in 'T-SQL Performance Tuning for Developers' started by Buzby, Feb 24, 2003.

  1. Buzby New Member

    [<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.
  2. bradmcgehee New Member

    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
  3. blackblade New Member

    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?
  4. chilisauce New Member

    Is table 'tbl_Loan' part of the view 'vw_Sales' ?

    -theSauce

Share This Page