Início > Tunning Queries - Optimize > Sometimes temp tables can be your best friend

Sometimes temp tables can be your best friend

Link:http://sqlserverpedia.com/blog/sql-server-bloggers/sometimes-temp-tables-can-be-your-best-friend/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+sqlserverpedia+%28SQLServerPedia%29

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.

  1. Ainda sem comentários.
  1. No trackbacks yet.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

%d bloggers like this: