August 18, 2009 at 1:06 pm
Greetings all.
I have a Data Warehouse where there are many tables that get updates (naturally). Currently the data manipulation is within stored procedures. The updates are fairly large update statements that are generally normal format statement with a Where tbl1.col1 != tbl2.col1 or tbl1.col2 != tbl2.col2 etc etc.
To me this seems inefficient, and I wrote a select statement for test to see if I could get the same results faster, and I came up with an except statement that returns the same results but in less than a 10th of the time. The only problem is, I can't figure out how to make the except query work for an update statement, without making a gigantic subquery (or using a CTE) and joining it to initial update from/where clauses.
Naturally, if you know or think of a better way to handle changes like this, I am all ears 😀
Here is the original update statement
UPDATE fct
SET
SELECT
Division_Idx = Dim_Division.Division_Idx
, AccountingDim1_Idx = ISNULL(AccountingDim1.AccountingDim1_Idx,0)
, AccountingDim2_Idx = ISNULL(AccountingDim2.AccountingDim2_Idx,0)
, AccountingDim3_Idx = ISNULL(AccountingDim3.AccountingDim3_Idx,0)
, AccountingDim4_Idx = ISNULL(AccountingDim4.AccountingDim4_Idx,0)
, AccountingDim5_Idx = ISNULL(AccountingDim5.AccountingDim5_Idx,0)
, AccountingDim6_Idx = ISNULL(AccountingDim6.AccountingDim6_Idx,0)
, AccountingDim7_Idx = ISNULL(EGAIT7,0)
, AccountingDate = FGLEDG.EGACDT
, TransactionDate = FGLEDG.EGOCDT
, Currency_Idx = CASE WHEN AccountingDim1.Division_Code = '900' THEN 3 ELSE ISNULL(Currency.Currency_Idx,0) END
, JournalNumber = FGLEDG.EGJRNO
, JournalSequenceNumber = FGLEDG.EGJSNO
, VoucherNumber = FGLEDG.EGVONO
, VoucherSequence = FGLEDG.EGVSER
, DR_CR_Indicator = FGLEDG.EGDBCR
, Recorded_Amount = FGLEDG.EGACAM
, ForeignCurrency_Amount = FGLEDG.EGCUAM
, ExchangeRate = FGLEDG.EGARAT
, Voucher_Name = FGLEDG.EGVDSC
, Batch_Idx = (SELECT Max(Batch_Idx) FROM dbo.LoadWindow)
,ParentAccountDim1_Idx =
CASE WHEN AccountingDim1.Division_Code = '900'
THEN fct.AccountingDim1_Idx
ELSE ISNULL(ToAcct.AccountingDim1_Idx,0) END
,fct.ParentAccountDim1_Idx
FROM dbo.FGLEDG
JOIN dbo.Dim_Division AS Dim_Division
ON FGLEDG.EGDIVI = Dim_Division.Division_Code
AND FGLEDG.EGCONO = Dim_Division.Company_Code
LEFT JOIN dbo.Dim_AccountingDim1 AS AccountingDim1
ON FGLEDG.EGAIT1 = AccountingDim1.AccountingDim1_Code
AND FGLEDG.EGDIVI = AccountingDim1.Division_Code
LEFT JOIN dbo.Dim_AccountingDim2 AS AccountingDim2
ON FGLEDG.EGDIVI = AccountingDim2.Division_Code
AND FGLEDG.EGAIT2 = AccountingDim2.AccountingDim2_Code
LEFT JOIN dbo.Dim_AccountingDim3 AS AccountingDim3
ON FGLEDG.EGDIVI = AccountingDim3.Division_Code
AND FGLEDG.EGAIT3 = AccountingDim3.AccountingDim3_Code
LEFT JOIN dbo.Dim_AccountingDim4 AS AccountingDim4
ON FGLEDG.EGDIVI = AccountingDim4.Division_Code
AND FGLEDG.EGAIT4 = AccountingDim4.AccountingDim4_Code
LEFT JOIN dbo.Dim_AccountingDim5 AS AccountingDim5
ON FGLEDG.EGDIVI = AccountingDim5.Division_Code
AND FGLEDG.EGAIT5 = AccountingDim5.AccountingDim5_Code
LEFT JOIN dbo.Dim_AccountingDim6 AS AccountingDim6
ON FGLEDG.EGDIVI = AccountingDim6.Division_Code
AND FGLEDG.EGAIT6 = AccountingDim6.AccountingDim6_Code
LEFT JOIN (SELECT Currency_Idx, Division_Code, Currency_Code, Company_Code FROM dbo.Dim_Currency
WHERE IsCurrentRate = 'Y' AND ExchangeRateType_Code = 1 AND Division_Code = '') AS Currency
ON FGLEDG.EGCUCD = Currency.Currency_Code
LEFT JOIN dbo.TranslatedAccounts AS trns WITH (INDEX(ncIdx_TranslatedAccounts))
ON AccountingDim1.Company_Code = trns.Company_code
AND AccountingDim1.Division_Code = trns.Division_Code
AND AccountingDim1.AccountingDim1_Code = trns.RcvngAccountingDim1_Code
LEFT JOIN dbo.Dim_AccountingDim1 ToAcct ON ToAcct.Company_Code = 100
AND ToAcct.Division_Code = '900'
AND ToAcct.AccountingDim1_Code = trns.ToAccountDim1_Code
LEFT JOIN dbo.Fact_GeneralLedger fct
ON FGLEDG.EGJRNO = fct.JournalNumber
AND FGLEDG.EGJSNO = fct.JournalSequenceNumber
AND Dim_Division.Division_Idx = fct.Division_Idx
WHERE
fct.AccountingDim1_Idx != ISNULL(AccountingDim1.AccountingDim1_Idx,0)
OR fct.AccountingDim2_Idx != ISNULL(AccountingDim2.AccountingDim2_Idx,0)
OR fct.AccountingDim3_Idx != ISNULL(AccountingDim3.AccountingDim3_Idx,0)
OR fct.AccountingDim4_Idx != ISNULL(AccountingDim4.AccountingDim4_Idx,0)
OR fct.AccountingDim5_Idx != ISNULL(AccountingDim5.AccountingDim5_Idx,0)
OR fct.AccountingDim6_Idx != ISNULL(AccountingDim6.AccountingDim6_Idx,0)
OR fct.AccountingDim7_Idx != ISNULL(EGAIT7,0)
OR fct.AccountingDate != FGLEDG.EGACDT
OR fct.TransactionDate != FGLEDG.EGOCDT
OR fct.Currency_Idx != CASE WHEN AccountingDim1.Division_Code = '900' THEN 3 ELSE ISNULL(Currency.Currency_Idx,0) END
OR fct.JournalNumber != FGLEDG.EGJRNO
OR fct.JournalSequenceNumber != FGLEDG.EGJSNO
OR fct.VoucherNumber != FGLEDG.EGVONO
OR fct.VoucherSequence != FGLEDG.EGVSER
OR fct.DR_CR_Indicator != FGLEDG.EGDBCR
OR fct.Recorded_Amount != FGLEDG.EGACAM
OR fct.ForeignCurrency_Amount != FGLEDG.EGCUAM
OR fct.ExchangeRate != FGLEDG.EGARAT
OR fct.Voucher_Name != FGLEDG.EGVDSC
OR fct.ParentAccountDim1_Idx !=
CASE WHEN AccountingDim1.Division_Code = '900'
THEN fct.AccountingDim1_Idx
ELSE ISNULL(ToAcct.AccountingDim1_Idx,0) END
And here is the select statement that yields the same results but needs to be transformed into an update statement.
SELECT
Division_Idx = Dim_Division.Division_Idx
, AccountingDim1_Idx = ISNULL(AccountingDim1.AccountingDim1_Idx,0)
, AccountingDim2_Idx = ISNULL(AccountingDim2.AccountingDim2_Idx,0)
, AccountingDim3_Idx = ISNULL(AccountingDim3.AccountingDim3_Idx,0)
, AccountingDim4_Idx = ISNULL(AccountingDim4.AccountingDim4_Idx,0)
, AccountingDim5_Idx = ISNULL(AccountingDim5.AccountingDim5_Idx,0)
, AccountingDim6_Idx = ISNULL(AccountingDim6.AccountingDim6_Idx,0)
, AccountingDim7_Idx = ISNULL(EGAIT7,0)
, AccountingDate = FGLEDG.EGACDT
, TransactionDate = FGLEDG.EGOCDT
, Currency_Idx = CASE WHEN AccountingDim1.Division_Code = '900' THEN 3 ELSE ISNULL(Currency.Currency_Idx,0) END
, JournalNumber = FGLEDG.EGJRNO
, JournalSequenceNumber = FGLEDG.EGJSNO
, VoucherNumber = FGLEDG.EGVONO
, VoucherSequence = FGLEDG.EGVSER
, DR_CR_Indicator = FGLEDG.EGDBCR
, Recorded_Amount = FGLEDG.EGACAM
, ForeignCurrency_Amount = FGLEDG.EGCUAM
, ExchangeRate = FGLEDG.EGARAT
, Voucher_Name = FGLEDG.EGVDSC
,ParentAccountDim1_Idx =
CASE WHEN AccountingDim1.Division_Code = '900'
THEN fct.AccountingDim1_Idx
ELSE ISNULL(ToAcct.AccountingDim1_Idx,0) END
FROM dbo.FGLEDG
JOIN dbo.Dim_Division AS Dim_Division
ON FGLEDG.EGDIVI = Dim_Division.Division_Code
AND FGLEDG.EGCONO = Dim_Division.Company_Code
LEFT JOIN dbo.Dim_AccountingDim1 AS AccountingDim1
ON FGLEDG.EGAIT1 = AccountingDim1.AccountingDim1_Code
AND FGLEDG.EGDIVI = AccountingDim1.Division_Code
LEFT JOIN dbo.Dim_AccountingDim2 AS AccountingDim2
ON FGLEDG.EGDIVI = AccountingDim2.Division_Code
AND FGLEDG.EGAIT2 = AccountingDim2.AccountingDim2_Code
LEFT JOIN dbo.Dim_AccountingDim3 AS AccountingDim3
ON FGLEDG.EGDIVI = AccountingDim3.Division_Code
AND FGLEDG.EGAIT3 = AccountingDim3.AccountingDim3_Code
LEFT JOIN dbo.Dim_AccountingDim4 AS AccountingDim4
ON FGLEDG.EGDIVI = AccountingDim4.Division_Code
AND FGLEDG.EGAIT4 = AccountingDim4.AccountingDim4_Code
LEFT JOIN dbo.Dim_AccountingDim5 AS AccountingDim5
ON FGLEDG.EGDIVI = AccountingDim5.Division_Code
AND FGLEDG.EGAIT5 = AccountingDim5.AccountingDim5_Code
LEFT JOIN dbo.Dim_AccountingDim6 AS AccountingDim6
ON FGLEDG.EGDIVI = AccountingDim6.Division_Code
AND FGLEDG.EGAIT6 = AccountingDim6.AccountingDim6_Code
LEFT JOIN (SELECT Currency_Idx, Division_Code, Currency_Code, Company_Code FROM dbo.Dim_Currency
WHERE IsCurrentRate = 'Y' AND ExchangeRateType_Code = 1 AND Division_Code = '') AS Currency
ON FGLEDG.EGCUCD = Currency.Currency_Code
LEFT JOIN dbo.TranslatedAccounts AS trns
ON AccountingDim1.Company_Code = trns.Company_code
AND AccountingDim1.Division_Code = trns.Division_Code
AND AccountingDim1.AccountingDim1_Code = trns.RcvngAccountingDim1_Code
LEFT JOIN dbo.Dim_AccountingDim1 ToAcct ON ToAcct.Company_Code = 100
AND ToAcct.Division_Code = '900'
AND ToAcct.AccountingDim1_Code = trns.ToAccountDim1_Code
LEFT JOIN dbo.Fact_GeneralLedger fct
ON FGLEDG.EGJRNO = fct.JournalNumber
AND FGLEDG.EGJSNO = fct.JournalSequenceNumber
AND Dim_Division.Division_Idx = fct.Division_Idx
EXCEPT
SELECT
Division_Idx
,AccountingDim1_Idx
,AccountingDim2_Idx
,AccountingDim3_Idx
,AccountingDim4_Idx
,AccountingDim5_Idx
,AccountingDim6_Idx
,AccountingDim7_Idx
,AccountingDate
,TransactionDate
,Currency_Idx
,JournalNumber
,JournalSequenceNumber
,VoucherNumber
,VoucherSequence
,DR_CR_Indicator
,Recorded_Amount
,ForeignCurrency_Amount
,ExchangeRate
,Voucher_Name
,ParentAccountDim1_Idx
FROM dbo.Fact_GeneralLedger
Link to my blog http://notyelf.com/
August 18, 2009 at 1:15 pm
Only way I know would be turn that into a CTE/derived table, and join to it to do the update.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply