Post reply

Adding field, time goes from .5 to 12.5

  • December 16, 2006 at 12:52 pm

    #170778

    Hello All,

    I have been working on a query which has been running very slowly and discovered that the time goes from .5 to 12.5 by just adding a single 9 char field to the result set. The basics of the query are (there are actually 6 tables joined and about 20 fields but this is the gist) :

    SELECT P.Field1,

    P.Field2,

    P.Field3,

    P.Field4

    FROM PARENT P

    LEFT JOIN CHILD

    ON CHILD.PK = P.CHILDFK

    WHERE .....

    The above will take .5 seconds

    SELECT P.Field1,

    P.Field2,

    P.Field3,

    P.Field4,

    CHILD.9CharField

    FROM PARENT P

    LEFT JOIN CHILD

    ON CHILD.PK = P.CHILDFK

    WHERE .....

    will take 12.5 seconds.

    The result set is 211 records so I wouldn't think its a result of field size. PARENT has about 10+Mil records while CHILD has 100+K. There are index (non-clustered) for the child and the parent fields

    The question I have is ... what does this mean? I would have thought that if the LEFT JOIN portion was the problem that it would exist with or without the field... though as I write this I'm wondering, does it mean that SQL Server is actually dropping the LEFT JOIN altogether when its not really needed in the result or the WHERE?

    Any and all thoughts are greatly appreciated

    Much Thanks,

    Steve Dingle

  • December 16, 2006 at 6:29 pm

    #678539

    for both SQL's, you'll want to view the estimated execution plans. they will be different, becuase the second sql is returning data from the other table

    Is there an index on the Child table's FK column? the PK of the primary table obviously has an index, but the joins can be improved SOMETIMES if the child table has an index on the FK field.  the query plan may ignre the index in favor of a table scan, but it's something you have to test in order to improve it, i think.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 18, 2006 at 6:32 am

    #678620

    There is a chance you may be able to recover this by updating the statistics on the relevant tables.  I have had experiences similar to this, where the growth rate since the last update has been significant.  This meant that it THOUGHT it was doing something a bit clever to get the data out quickly... but it wasn't.  Updating the stats brought it back down to a much more sensible time frame, and matched the query plans together better! 

     

  • December 18, 2006 at 7:02 am

    #678634

    Thanks wangkhar, that did in fact help

    Much Thanks,

    Steve Dingle

  • December 19, 2006 at 3:50 am

    #678875

    Yes. SQL Server drops the LEFT JOIN with the table if the query optimiser decides that it can do it without any sideeffects. This is a VERY good quality for horisontal partitioning. Remember: SQL Server MUST have enough information about your meta data to be Able to understand if the join is droppable. I.e Unique indexes that guarantee that the join will not duplicate rows in the parent, foreign keys that guarantee that the join will not remove any rows from the parent (in the resultset)....

    I hope this will help!

    Hanslindgren

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply