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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply