Sometimes temp tables can be your best friend
Here is a quick example of when using temporary tables will give you a big performance gain. I recently was given this query to improve, as it was taking over 15 hours to run:
SELECT *
FROM
(
SELECT * FROM
GL_Star_FactsDetails GL
LEFT JOIN DimAccount ACCT on GL.Account = ACCT.AccountRapidDecision
where GL.LedgerTypeCode = ‘AA’
) FD
INNER JOIN vwDimBusinessUnit BU ON FD.BusinessUnitCode = BU.BusinessUnitCode
INNER JOIN DimCurrencyExchangeRates EX ON BU.CurrencyCode = EX.CurrencyCode AND FD.DateKey = EX.DateKey
GL_Star_FactsDetails has 16 million records, DimAccount has 5,000 records, DimCurrencyExchangeRates has 70,000 records, and vwDimBusinessUnit is a complex view (it has a UNION ALL and multiple joins) that returns 10,000 records.
These are not big tables, so why is this query taking so long? A simple look at the query execution plan tells the story: it has 80 operations! It is simply a matter of the query optimizer having too many tables and too many joins to come up with an efficient query plan. So, use temp tables to simplify it for the query optimizer:
–substitute a temp table for the view
select *
into #TempDimBusinessUnit
from vwDimBusinessUnit
–substitute a temp table for the sub-query
SELECT *
into #TempFactDetails
FROM
GL_Star_FactsDetails GL
LEFT JOIN DimAccount ACCT on GL.Account = ACCT.AccountRapidDecision
where GL.LedgerTypeCode = ‘AA’
–and use the two temp tables in the main query
SELECT *
into #temp
FROM
#TempFactDetails FD
INNER JOIN #TempDimBusinessUnit BU ON FD.BusinessUnitCode = BU.BusinessUnitCode
INNER JOIN DimCurrencyExchangeRates EX ON BU.CurrencyCode = EX.CurrencyCode AND FD.DateKey = EX.DateKey
select * from #temp
This rewrite resulted in a much simpler query plan with the query taking only 12 minutes. While there are some negatives with using temp tables, in this case the benefits make it very worthwhile.
I’ve had similar in the past, which I blogged about here: http://thelonedba.wordpress.com/2011/05/10/t-sql-tuesday-018-ctes-theyre-not-always-good-for-you/
This time, it wasn’t a subquery such as you had, but a rather long-winded combination of CTEs and VIEWs, and I found that creating temporary tables from those caused the query behind a report to go from 2 hour run-time to 40 seconds.
Remember, kids, CTEs & VIEWs aren’t always good for you!
Great point Thomas, thanks for contributing!
I’ve had a recent issue where the query had to process 20 million rows, read XML data and extract a particular tag’s value, which was later used to join to another table and using temporary tables was definitely the most optimal solution.
thanks for sharing this though.