February 24, 2010 at 9:35 am
CONSIDER query1:
select top(100) *
FROM table0 a
FULL OUTER JOIN table1 b
ON (a.id = b.id)
FULL OUTER JOIN table2 c
ON (c.id = b.id OR c.id = a.id);
AND query2:
select top(100) *
FROM table0 a
LEFT JOIN table1 b
ON (a.id = b.id)
LEFT JOIN table2 c
ON (c.id = b.id OR c.id = a.id)
LEFt JOIN table3 d
ON (d.id = c.id OR d.id = b.id OR d.id = a.id)
LEFT JOIN table4 e
ON (e.id = d.id OR e.id = c.id OR e.id = b.id OR e.id = a.id)
LEFt JOIN table5 f
ON (f.id = e.id OR f.id = d.id OR f.id = c.id OR f.id = b.id OR f.id = a.id)
Relevant informations:
1) id is clustered index on all six tables
2) in each table there are some rows with id not present in any other table; there are also some rows with id which are in 2, 3, 4 or 5 tables only.
3) 98% of rows has ID present in all six tables
4) there is about 2 millions records in each table
5) yes, I need full outer join
My question is: why is the query2 like million times faster than query1? That's preposterous. With added 'top(100) *' the query2 takes like 1 minute, few seconds. The query1 I've stopped after half an hour. Also the estimated execution plan says that query1 will take 100% of the batch time while query2 - 0%. Again - ridiculous.
February 24, 2010 at 11:29 am
they dont appear to be doing the same thing. I've found that full outer joins are very intense.
You may want to beak it up into multiple querries. That's what i would do at least. More readable that way as well.
February 24, 2010 at 1:46 pm
just a thought...
try to run the query with maxdop hint.
----------
Ashish
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy