Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. 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 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.

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.

Comments

Posted by Jason E Bacani on 11 October 2011

I think of this as a 'divide and conquer' strategy.  While there are other costs in using the temp table, the savings in time seems to override those costs.  

Great example!

Just my two and a half cents.

Leave a Comment

Please register or log in to leave a comment.