Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Unanswered: Intermittently Slow query - Left Join

    Here's a little background on the query. I have a list of documents by an id number in one table and the description of the sheets in another table. It's a one to many relationship, so for each description, there may be multiple entries in the documents table that it applies to. For example:

    Code:
    Descriptions table:
    
    ID       |   Title
    Doc1   |    Document 1
    Doc2   |    Document 2
    
    
    Documents table:
    
    ID       |   Parent
    Doc1   |   10400
    Doc2   |   10400
    Doc1   |   20189
    Doc3   |   20189
    
    View:
    
    ID       |    Parent     |    Description
    Doc1   |    10400      |    Document 1
    Doc2   |    10400      |    Document 2
    Doc1   |    20189      |    Document 1
    Doc3   |    20189      |    (null)
    So the query I am using uses a left join to combine the data from the one table into the other. There might not be an entry for the description, so for some Document entries, the description field may be blank. For some reason, certain queries take about 2 minutes longer than others who retreive 5 times the information.

    In SQL Manager, is says "Executing Query. Waiting for response from data source." After about 20 seconds it says "Retrieving Data..." then about a minute later, it finally comes up with the data. I can select another parent that has a lot more items and it comes up in about 3 seconds max.

    It's running on SQL Server 2005 with 2GB of RAM.

    Any suggestions on tracking down the reason for the slowness would be great.

    Thanks in advance!!!

    -Dan

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post the query you are executing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2006
    Posts
    5
    Code:
    SELECT
       dbo.Table1.ItemType, 
       dbo.Table1.ItemLabel, 
       dbo.Table1.ItemParent, 
       dbo.Table1.ItemID, 
       dbo.Table1.ProjectID, 
       dbo.Table1.ItemDate, 
       dbo.Table1.Active, 
       dbo.Table1.ItemBaseLabel, 
       dbo.Table2.ItemDescription
    FROM dbo.Table1 LEFT OUTER JOIN dbo.Table2 
    ON dbo.Table1.ProjectID = dbo.Table2.ProjectID 
    AND dbo.Table1.ItemBaseLabel = dbo.Table2.ItemBaseLabel
    Here you go!

    -Dan

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    where are the indices placed? have you looked at the execution plan?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Apr 2006
    Posts
    5
    That's weird... I just checked the indices on the base table and there are three. One is By ItemID. Another is by ItemLabel and another is by ItemLabel, ItemParent, Item ID, and another field. Could they be throwing off the way the data is being stored?

    There are no indices on the view and I cannot add one. It comes up with an error saying that it can't add an index because it's not schema based. Please, bear with me - I'm new to 2005. This was originally stored on MSSQL 7.0 and it worked fine there.

    Thanks!

    -Dan

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    typically you want indices on search conditions, primary keys (there is usually a clustered one there) and the foriegn keys but you should examine the execution plan first to make sure this will help. if they are already there, make sure they are not fragmented, the statistics are up to date, and then if this is a sp, recompile it. I am not working with 2K5 just yet.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Apr 2006
    Posts
    5
    I checked the indices and everything seems to be in order. I also tried it through a stored procedure and it still takes way too long.

    Any other ideas?

    -Dan

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    for the third time... have you looked at the execution plan?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Apr 2006
    Posts
    5
    Sorry. The execution plan said that a table scan on the dbo.Table2 was 100% cost. So I looked at the fields it was referencing and added a column to the Table2 index and then re-ran the execution plan and it was more divided among the tasks, now with 2 index scans.

    Went back to the query and now it's blazing fast.

    Apparently my listening skills are about as good as my databasing skills...

    Thanks Thrasymachus.

    -Dan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •