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.

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQL Server, SQLServerPedia Syndication. Bookmark the permalink.

3 Responses to Sometimes temp tables can be your best friend

  1. 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!

  2. Simran says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *