Adding field, time goes from .5 to 12.5

Question

Adding field, time goes from .5 to 12.5

Steve Dingle

SSChasing Mays

Points: 601
More actions
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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

RichB SSCrazy Eights Points: 9651 More actions · Answer 2

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!

Steve Dingle SSChasing Mays Points: 601 More actions · Answer 3

Thanks wangkhar, that did in fact help

Much Thanks,

Steve Dingle

Hans Lindgren SSChampion Points: 10496 More actions · Answer 4

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