Replacement of CROSS APPLY statement
1. I am running query in which cross apply operator is used
2. That same query exceutes more than 10 times
3. Due to cross apply it increase the time to excute the report
Software/Hardware used:
Software/Hardware used:
ASKED:
June 15, 2009 8:12 AM
UPDATED:
July 8, 2009 7:06 PM
Answer Wiki:
<b>Background Information:</b>
The CROSS APPLY does a Cartesian join between the tables. Depending on what's being joined, this can cause the run time of the query to increase as SQL is taking the number of rows in the first table times the number or rows in the second.
CROSS APPLY <i>can</i> solve some problems that would be very difficult to solve other ways, but it is often not the best answer. In some cases, it IS the best answer (I’ve used it with a linked server query in a way that resulted in hundreds of times performance improvement).
<b>Answer</b>
Are you sure that the CROSS APPLY is the correct thing to do? What result are you trying to get?
Please post the query you are using. If CROSS APPLY is using a function, please post the function declaration.
It would also probably be useful to have the DDL for the tables involved, and possibly the indexes, too. Right-click on the table and script to the clipboard.
With more information we can provide you with a better answer.
To see all answers submitted to the Answer Wiki: View Answer History.
Please post the query you are using. If CROSS APPLY is using a function, please post the function declaration.
It would also probably be useful to have the DDL for the tables involved, and possibly the indexes, too. Right-click on the table and script to the clipboard.
CROSS APPLY can solve some problems that would be very difficult to solve other ways, but it is often not the best answer. In some cases, it IS the best answer (I’ve used it with a linked server query in a way that resulted in hundreds of times performance improvement).
Clearly I didn’t know how to use this site. Please ignore my discussion comment I posted.