October 9, 2013 at 12:43 am
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.
October 9, 2013 at 1:28 am
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/
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
October 9, 2013 at 6:25 am
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;-)
October 10, 2013 at 12:34 am
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.
October 10, 2013 at 2:40 am
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;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply