I have an situation the tbl1 part itself is fast and the tbl2 itself is fast also, since the effective filters gives usage of partitioned clustered index. But in combination they sometimes work horrible regarding performance.
Rewrite as outer apply didn't work.
Force Order hint didn't work
Index hint didn't work, since tbl1/2 are views to other database (on same server)
from tbl1 a
left outer join ( select b.* from tbl2 b /* really effective filters here */ ) c on c.col1=a.col1
If split the statements in two, where the inline part is selected into #table first, I got good performance again.
Besides of trying update statistcs with fullscan;
is there another way to avoid splitting this statement?
B. D. Jensen
View Complete Post