Here is a slightly more concise version of the query - most importantly it only joins against each table once. Also I moved the DATE_INFO join to a simple variable - I do not see any need to involve the DATE_INFO table at all. What other answer could it provide, other than today's date at midnight?
Now, that should absolutely lead to a more efficient query plan, but to see a difference in performance, you'd have to take a closer look at where your current indexes are falling short, and/or ensure statistics are up to date. I haven't looked at the actual plan, but you should probably re-generate a new plan based on this more efficient query anyway.
By Aaron Bertrand ♦ 1.7k answered May 02, 2014 at 08:58 PM
Lots of key lookups in there, so it looks like there's an opportunity to either add indexes or add columns to existing indexes. Also, I see table2.index4 called twice in the execution plan. So it might be possible to re-arrange some of the code so that operation is only needed once.
However, it's really hard to tell much about the query without 1) any of the real code, 2) an actual execution plan. If you can provide those, it'd help the analysis quite a lot.
Hope this helps,
By Kevin 141 answered May 02, 2014 at 06:54 PM