Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Outer Apply and Cross Apply performance hits Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 12:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 10, Visits: 64
I have a serch functionality that has a query based on search inputs given. Initially the query is built dynamically for the search inputs given and dump the results in Temp Table and then it does an update according to a business logic for searching cases based on various statuses. Finally, the results are fecthed from Temo Table.

In my update block I have a long query that updates various column from above Temp table by using various outer applys and cross apply blocks with various other queries.

Real problem lies in this update block as it take very long time to complete when I looked at execution plan. I have added\dropped few indexes and that improved performance at some extent. Mostly my cross apply blocks run very fast and outer applys blocks take most of the time.

I am planning to further dissect these queries using all cross applys and then finally select ALL results from Temp tables. Because, anyhow the Outer Applys do left outer join and cross Applys to an inner join. So, I think this would speed up performance without actually missing anything.

Any ideas or hits in this regard are really appreciated. Thank you.

Post #1502926
Posted Wednesday, October 9, 2013 1:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:11 AM
Points: 384, Visits: 770
The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression. the left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set. The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression.

The CROSS APPLY operator returns only those rows from left table expression if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only. Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression



In my case the need to use APPLY operator came handy when I had to use table-valued function for each row in the outer query..

have a look at below links , they might be helpful

http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/07/07/using-cross-apply-to-optimize-joins-on-between-conditions.aspx


SQL 2005/2008 DBA - MCTS/MCITP
Post #1502942
Posted Wednesday, October 9, 2013 6:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
SPs/Queries also behave bad when Spooling or work table comes in picture for heavy data temporary storage during intermediate stages. so to avoid this you can use temp table with index (if required ) to make the sql run faster.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1503060
Posted Thursday, October 10, 2013 12:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 10, Visits: 64
Thank you Kenneth and Bhuvnesh. I know how Outer Applys and Cross Applys work and did dig various things out of it. What I think is, if I can replace Outer Applys to work some better way, Left outer join is another choice but that is more hurting as I think. Cross Applys are little better as they just work on matching rows like inner joins and I have re write my query and finally call all rows from temp Table.

Like Replace Outer Apply with a Cross Apply (inner join) with Temp Table and then finally Select all rows from Temp Table. I am wondering that I shouldnt be missing anything while doing this and that is what my question is.

Here is my update block looks like. It is very long but i have just simplified it. I did breaking of code and run them in pieces and observed things like indexes execution plans. Queries improved at some extent but Outer Applys still consume lot of time.

I think I would need to break all Outer Applys in Cross Applys and then finally select all results from temp table

Update #Temp
set col1 = vvp.col1, col2 = vvp.col2, col3 = vvp.col3, col4 = vp.col1, col5 = vp.col2, col6 = sp.col1, col7 = sp.col2, col8 = ap.col1, col9 = ap.col2, col11 = kp.col1, col12 = kp.col2
from #temp t
Outer Apply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)VVP
Outer Apply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)VP
Outer Apply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)SP
Cross Apply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)AP
CrossApply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)KP

After Update block above, finally I have below query to display results to users.

select * from #temp

Please let me know any other thoughts anyone have.



Post #1503382
Posted Thursday, October 10, 2013 2:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
VickyDBA (10/10/2013)
I am wondering that I shouldnt be missing anything while doing this and that is what my question is.

Here is my update block looks like. It is very long but i have just simplified it. I did breaking of code and run them in pieces and observed things like indexes execution plans.

Two things ..

First that your posted query will not work i guess because of
Update #Temp

Second, you need to have indexes satisfing your query like indx on ID column of #temp table PLUS ID columns of other tables too .. and i think i will improve performance .


If possible , post the exec plan too in .sqlplan extension.



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1503412
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse