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.