February 24, 2010 at 11:55 pm
This question came while working on one the query where 3-4 JOINS (actually more than that) are used to filter few records are SELECT clause is returning only data from one table. Developers have used the CROSS APPLY instead of EXISTS clause here.
Can anyone brief me about performance hit of using CORSS APPLY & EXISTS.
Abhijit - http://abhijitmore.wordpress.com
February 25, 2010 at 7:21 am
I'm not sure why they would be using CROSS APPLY, as it seems to me they could use an INNER JOIN if they are accessing tables or views. Here is what MS has to say about APPLY:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
The query optimizer is usually smart enough to determine that a CROSS APPLY is a an INNER JOIN and convert to that. The same applies with EXISTS. I know in the situation you are describing (it would be easier to comment if you included some code) that I prefer to do INNER JOIN's than EXISTS in the WHERE clause. I use EXISTS when there is only 1 table to JOIN to and JOIN's when I have multiple tables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2010 at 7:26 am
I'd have to see both versions of the query to really comment on this. Exists is pretty high performance when used correctly, but so is cross apply.
If you really want to see what the performance difference is, test both queries for speed and see. That's the best way. Just make sure the tests are thorough.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2010 at 8:18 am
I'd want to see the code because you're not exactly comparing Apples to Apples in this case. EXISTS and APPLY really are doing two completely different things within a query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2010 at 9:59 pm
Abhijit More (2/24/2010)
This question came while working on one the query where 3-4 JOINS (actually more than that) are used to filter few records are SELECT clause is returning only data from one table. Developers have used the CROSS APPLY instead of EXISTS clause here. Can anyone brief me about performance hit of using CROSS APPLY & EXISTS.
It depends.
The way the query is written (including whether you use APPLY, IN, or EXISTS) is often irrelevant. The query optimizer does not take the query text literally. It transforms your code into logic operations, and optimizes from there, using a wide variety of provably-true transformations. It is possible to write the same logical requirement in many ways, and often they will produce exactly the same (or trivially different) execution plan. Identical execution plans produce identical results (all things being equal).
So, depending on the exact query, there may be no difference, or all the difference in the world.
For those reasons, the question is faulty and has no correct answer, at least not in the way you might have been expecting.
Paul
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply