SQLServerCentral.com / SQL Server 2008 / SQL Server 2008 Performance Tuning / Outer Apply and Cross Apply performance hits / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 07:14:55 GMT20RE: Outer Apply and Cross Apply performance hitshttp://www.sqlservercentral.com/Forums/Topic1502926-3387-1.aspx[quote][b]VickyDBA (10/10/2013)[/b][hr]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. [/quote]Two things .. First that your posted query will not work i guess because of [code="sql"]Update #Temp[/code]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. Thu, 10 Oct 2013 02:40:06 GMTBhuvneshRE: Outer Apply and Cross Apply performance hitshttp://www.sqlservercentral.com/Forums/Topic1502926-3387-1.aspxThank 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 #Tempset 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.col2from #temp tOuter Apply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)VVPOuter Apply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)VPOuter Apply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)SPCross Apply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)APCrossApply (select Col1, col2... from Table1 inner join table2 where t.id = table1.id...)KPAfter Update block above, finally I have below query to display results to users. select * from #tempPlease let me know any other thoughts anyone have. Thu, 10 Oct 2013 00:34:15 GMTVickyDBARE: Outer Apply and Cross Apply performance hitshttp://www.sqlservercentral.com/Forums/Topic1502926-3387-1.aspxSPs/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.Wed, 09 Oct 2013 06:25:15 GMTBhuvneshRE: Outer Apply and Cross Apply performance hitshttp://www.sqlservercentral.com/Forums/Topic1502926-3387-1.aspxThe 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 expressionIn 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 [url]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[/url]Wed, 09 Oct 2013 01:28:46 GMTKenny JoziOuter Apply and Cross Apply performance hitshttp://www.sqlservercentral.com/Forums/Topic1502926-3387-1.aspxI 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. Wed, 09 Oct 2013 00:43:13 GMTVickyDBA