Challenge for SQL Gurus: Grouping and outer joins
-
I checked the execution plans for each query, the first one by Carl has a cost of 49.92% vs 50.08% for Hariharan. But without the real indexed data it's hard to say which is really the fastest.
-
Michael,
The Expression:
Select ... From Table1,Table2
without a where clause is a cross join!
Therefore it should be exactly the same as the one posted by Carl with less typing
* Noel -
I totally agree with you but the execution plan still shows a smallish difference between the 2 plans... but then again this is unrealistic data so I would read a lot into it.
-
Remi,
You are correct in that the plan may be a little different but then again all those are very specific for data amount, skew in it and index presence.
As of instructions for sql server they are equivalent 100% is like the equivalency of the ANSI join syntax with the old where style
* Noel -
Noeld, Remi thanks for your response. Now just one more question (I caught this just one more question habit from my kids.
) is there a way to get the desired results without producing a Cartesian product?Thanks
Mike
-
You want a cartesian result that is not cartesian????????
-
Remi thanks. Just fuzzy thinking here in re-reading the original post I see that cartesian result is required. I must have a thing about requesting data that is not needed or creating a cartesian result when it is not needed. Speed and more Speed is always a concern. And it appears that I could use a does of NoDose to speed up the thinking process.
Mike
-
Hi guys,
sorry for the late response. many of you have expressed concern on the cartesian join (CROS JOIN).. my only intension was to achive the desired result. CROSS JOIN is always a overhead to the server, coz it will give a drastic impact on the peformance if the volume of data is high.
-Hari
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply