Hello,
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
How would I rewrite left joins? Any examples?
Thanks.
Moving to T-SQL inside SQL Server, where you are most likely to find experts to answer this question.|||When you have that many referenced tables/joins, you will get a perf hit, espcially if you have large tables involved. The only trick in the book that helps is to filter large tables down to smaller #temp tables. Then use these #temp tables for your join. You will reduce the log need to process your large tables.
e.g.
Code Snippet
select *
into #tmp1
from large_table1
where filter=123
select *
into #tmp2
from large_table2
where filter=xyz
select *
from #tmp1 left join #tmp2 on ...
|||Before applying any kind of performance tuning to your query, first answer this question. Is there any performance issue when testing it? There is nothing particularly slow about a query with 11 left joins, if your data is structured correctly and (very important) indexex correctly.
The kinds of things you can "guess" and do (like breaking down into temp tables, as OJ states) are excellent strategies if the optimizer fails you, but 9 times out of 10, even for really complex queries, SQL Server will do whatever you are trying to do to improve performance of the joins on their own.
If you don't have a test environment with adeqate amounts of data to do performance testing, this process is a heck of a lot more work, but as a rule, I don't like to do any performance guessing, do performance testing/reactive tuning BEFORE you get to your production environment if at all possible.
