May 4, 2005 at 2:08 am
Hi,
I have to fine tune some queries in the existing system. I don't have much information on these since there is no documentation available.
The following update takes 37 minutes to update 1002464 rows . Can someone help me in optimizing it or atleast some clues on how to do that
UPDATE doc
SET USDCurrencyMultiplier = ISNULL(ROUND(CASE WHEN doc.CreatedDtTm >= '1/1/1999' AND FrEu.FixedExchangeRateAmt IS NOT NULL
THEN (doc.DocumentCurrencyMultiplier / FrEu.FixedExchangeRateAmt) * EuEx.ExchangeRateAmt
ELSE (doc.DocumentCurrencyMultiplier * FrEx.ExchangeRateAmt)
END,2),0) ,
EuroCurrencyMultiplier = ISNULL(ROUND(CASE WHEN doc.CreatedDtTm >= '1/1/1999' AND FrEu.FixedExchangeRateAmt IS NOT NULL
THEN (doc.DocumentCurrencyMultiplier / FrEu.FixedExchangeRateAmt)
ELSE (doc.DocumentCurrencyMultiplier * FrEx.ExchangeRateAmt) * (1/EuEx.ExchangeRateAmt)
END,2),0) ,
CompanyCurrencyMultiplier = ISNULL(ROUND(CASE WHEN doc.CreatedDtTm >= '1/1/1999' AND FrEu.FixedExchangeRateAmt IS NOT NULL AND CoEu.FixedExchangeRateAmt IS NOT NULL
THEN (doc.DocumentCurrencyMultiplier / FrEu.FixedExchangeRateAmt) * CoEu.FixedExchangeRateAmt
WHEN doc.CreatedDtTm >= '1/1/1999' AND FrEu.FixedExchangeRateAmt IS NOT NULL AND CoEu.FixedExchangeRateAmt IS NULL
THEN ((doc.DocumentCurrencyMultiplier / FrEu.FixedExchangeRateAmt) * EuEx.ExchangeRateAmt) * (1/CoEx.ExchangeRateAmt)
WHEN doc.CreatedDtTm >= '1/1/1999' AND FrEu.FixedExchangeRateAmt IS NULL AND CoEu.FixedExchangeRateAmt IS NULL
THEN (doc.DocumentCurrencyMultiplier * FrEx.ExchangeRateAmt) * (1/CoEx.ExchangeRateAmt)
WHEN doc.CreatedDtTm >= '1/1/1999' AND FrEu.FixedExchangeRateAmt IS NULL AND CoEu.FixedExchangeRateAmt IS NOT NULL
THEN ((doc.DocumentCurrencyMultiplier * FrEx.ExchangeRateAmt) * (1/EuEx.ExchangeRateAmt)) * CoEu.FixedExchangeRateAmt
WHEN doc.CreatedDtTm < '1/1/1999'
THEN (doc.DocumentCurrencyMultiplier * FrEx.ExchangeRateAmt) * (1/CoEx.ExchangeRateAmt)
END,ISNULL(DecimalPositionsQty,2)),0),
CompanyCurrencyCode = CO.CurrencyCode ,
CurrencyCode = doc.CurrencyCode
FROM wrkBillingDocData AS doc
JOIN monthlyexchangerate as FrEx on doc.CurrencyCode = FrEx.CurrencyCode
and doc.CreatedDtTm between FrEx.FiscalMonthStartDate and FrEx.FiscalMonthEndDate
and FrEx.ExchangeRateTypeCode = 'P&L'
JOIN GLCompany as CO ON doc.MSGLCompanyCode = CO.GLCompanyCode
JOIN monthlyexchangerate as COEx on CO.CurrencyCode = COEx.CurrencyCode
and doc.CreatedDtTm between COEx.FiscalMonthStartDate and COEx.FiscalMonthEndDate
and CoEx.ExchangeRateTypeCode = 'P&L'
LEFT OUTER JOIN monthlyexchangerate as EuEx on EuEx.CurrencyCode = 'EUR'
and doc.CreatedDtTm between EuEx.FiscalMonthStartDate and EuEx.FiscalMonthEndDate
and EuEx.ExchangeRateTypeCode = 'P&L'
LEFT OUTER JOIN EuroZoneCurrencyRate AS FrEu on doc.CurrencyCode = FrEu.CurrencyCode
LEFT OUTER JOIN EuroZoneCurrencyRate AS COEu on CO.CurrencyCode = COEu.CurrencyCode
LEFT OUTER JOIN EuroZoneCurrencyRate AS EuEu on EuEu.CurrencyCode = 'EUR'
Appreciate all the help
May 4, 2005 at 3:35 am
Column doc.CreatedDtTm participates in multiple joins "between ... and ..."
Is there clustered index on this column? It should be.
_____________
Code for TallyGenerator
May 4, 2005 at 4:07 am
No. there is no clustered index on the doc.CreatedDttm column. The createddttm field is datetime field and has the format of 'yyyy-mm-dd hh:mm:ss'
There is already a clustered index on BillingDocumentNbr on doc Table
Any other suggestions ?
May 4, 2005 at 4:14 am
If createddttm is datetime then it is not in any format.
Format is used only for date representation.
The strict rule is:
Put clustered index on the column you most often use for range selection.
I don't think you ever use clause "WHERE BillingDocumentNbr between ... and ..." So clustered index on this column is useless.
Make it non-clustered and put clustered index on createddttm.
It's OK when PK is not clustered index.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply