DBTarget è un DB OLAP che contiene una tabella TargetTable ed è utilizzato per effettuare Business Intelligence
DBSource è il DB OLTP che contiene una tabella SourceTable ed è utilizzato dai programmi aziendali di gestione.
Abbiamo la necessità di allineare il contenuto di TargetTable con quello di SourceTable utilizzando T-SQL.
Ho a disposizione 3 strade
Soluzione 1
- Truncate/Delete di TargetTable
- Ri-popolamento totale partendo dai dati di SourceTable
- Cerco le righe presenti in TargetTable ma non in SourceTable e se necessario le elimino
- Cerco le righe differenti tra TargetTable e SourceTable e le aggiorno con i dati di SourceTable
- Cerco le righe presenti in SourceTable ma non in TargetTable e le inserisco
- Eseguo il Merge delle tabelle
Vediamo un paio di esempi.
Creo DBSource con la Sp di popolamento iniziale e quella di simulazione modifica dati
Create Database DBSource Go Use DBSource Go Create table SourceTable ( id int primary key clustered ,campo1 varchar(25) ,campo2 varchar(25) ,campo3 varchar(25) ) GO --Popolo la tabella con circa 16 Milioni di righe Create Procedure CreateSourceData as Truncate table SourceTable --Popolamento iniziale ;With Cte as (Select 1 Riga Union All Select 1) ,Cte4 as (Select A.Riga From Cte A, Cte B) ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B) ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B) ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B) ,CteManyRows as (Select A.Riga From Cte65536 A, Cte256 B) ,Results as (Select ROW_NUMBER() Over(Order By Riga)Riga From CteManyRows) Insert into SourceTable (id, campo1, campo2, campo3) Select Riga ,'Campo1 ' + Cast(Riga as Varchar(19)) ,'Campo2 ' + Cast(Riga as Varchar(19)) ,'Campo3 ' + Cast(Riga as Varchar(19)) from Results Go --Cancello, Modifico, inserisco il numero di righe --specificato nel parametro @RowNum --Le righe coinvolte sono reperite casualmnte grazie all'Order by NewId() Create Procedure ModifySourceData @RowNum int as --Cancellazione Random Delete A From SourceTable A Inner Join (Select Top(@RowNum) id from SourceTable Order By NEWID()) B On A.id = B.id --Modifica Random Update A Set Campo2 = A.campo3 From SourceTable A Inner Join (Select Top(@RowNum) id from SourceTable Order By NEWID()) B On A.id = B.id --Inserimento di nuove righe ;With Cte as (Select 1 Riga Union All Select 1) ,Cte4 as (Select A.Riga From Cte A, Cte B) ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B) ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B) ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B) ,CteManyRows as (Select A.Riga From Cte65536 A, Cte256 B) ,Results as (Select ROW_NUMBER() Over(Order By Riga)Riga From CteManyRows) Insert into SourceTable (id, campo1, campo2, campo3) Select Riga + 1000000000 ,'Campo1 ' + Cast(Riga + 1000000000 as Varchar(19)) ,'Campo2 ' + Cast(Riga + 1000000000 as Varchar(19)) ,'Campo3 ' + Cast(Riga + 1000000000 as Varchar(19)) from Results Where Riga <= @RowNum GoCreo ora DBTarget con le SP di popolamento iniziale e di allineamento
Create Database DBTarget Go Use DBTarget Go Create table TargetTable ( id int primary key clustered ,campo1 varchar(25) ,campo2 varchar(25) ) GO --popola la tabella TargetTable con circa 16 milioni Create Procedure dbo.ResetTable as Truncate table TargetTable ;With Cte as (Select 1 Riga Union All Select 1) ,Cte4 as (Select A.Riga From Cte A, Cte B) ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B) ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B) ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B) ,CteManyRows as (Select A.Riga From Cte65536 A, Cte256 B) ,Results as (Select ROW_NUMBER() Over(Order By Riga)Riga From CteManyRows) Insert into TargetTable (id, campo1, campo2) Select Riga ,'Campo1 ' + Cast(Riga as Varchar(19)) ,'Campo2 ' + Cast(Riga as Varchar(19)) from Results Go --Allinea la tabella TargetTable facendone la truncate e ripopolandola totalmente da SourceTable Create Procedure dbo.Ripopola as Truncate table TargetTable Insert into TargetTable Select id, Campo1, Campo2 From [DBSource].[dbo].[SourceTable] Go --Allinea la tabella TargetTable effettuando --1 Delete righe non più presenti in Source --2 Update Righe modificate in Source --3 Insert nuove righe da Source Create Procedure dbo.AllineaSenzaMerge as Delete T from TargetTable T Where T.id not in (Select id from [DBSource].[dbo].[SourceTable]) --Cerco presenti ma differenti Update T Set T.campo1 = S.campo1 ,T.campo2 = S.campo2 from TargetTable T Inner join [DBSource].[dbo].[SourceTable] S On T.id = S.id Where T.campo1 <> S.campo1 Or T.campo2 <> S.campo2 --Cerco nuovi Insert Into TargetTable Select S.id ,S.campo1 ,S.campo2 from [DBSource].[dbo].[SourceTable] S Where S.id not in (Select id from TargetTable) GO --Allinea la tabella TargetTable con il solo -- omando MERGE Create Procedure dbo.AllineaConMerge as Merge Into TargetTable Target Using (Select id,Campo1,campo2 from [DBSource].[dbo].[SourceTable]) Source On Target.id = Source.id When not matched by Source then Delete When Matched and (target.campo1 != source.campo1 Or target.campo2 != source.campo2) Then Update Set target.campo1 = source.campo1 ,target.campo2 = source.campo2 When not Matched by target then Insert (id,campo1,campo2) values(source.id, source.campo1, source.campo2); GOHo creato nel DB 4 SP
- dbo.ResetTable= Ripristina la situazione iniziale del test
- dbo.Ripopola = Allina la tabella con un re-inserimento totale dei dati
- dbo.AllineaSenzaMerge = Allinea la tabella eliminando, modificando, inserendo solo le righe necessarie in 3 step T-SQL differenti (Delete, Update, Insert)
- dbo.AllineaConMerge = Allinea la tabella eliminando, modificando, inserendo solo le righe necessarie con il solo comando merge.
N.B. Il caricamento potrebbe essere lento....
Use DBSource Go Exec CreateSourceData Go
Esempio 1 : Popolamento TargetTable da zero
Proviamo ad allineare Target con la SP dbo.Ripopola
Use DBTarget Go Set statistics Io On Set Statistics Time On Go Exec dbo.Ripopola Go Set statistics time off Set statistics io Off GO
La Sp impiega circa 30 secondi per inserire circa 16 milioni di record (Il disco della macchina è un SSD ;-) )
Proviamo ad allinearla con la SP dbo.AllineaSenzaMerge
Use DBTarget Go Truncate table TargetTable Go Set statistics Io On Set statistics Time On GO Exec dbo.AllineaSenzaMerge GO Set statistics time off Set statistics io Off GO
Il tempo impiegato è di circa 35 secondi...
Proviamo ora con il comando Merge
Use DBTarget Go Truncate table TargetTable Go Set statistics Io On Set statistics Time On GO Exec dbo.AllineaConMerge GO Set statistics time off Set statistics io Off GO
Il Merge impiega circa 44 Secondi.......
In una situazione come questa è ovviamente meglio effettuare il popolamento utilizzando un Insert into dalla tabella sorgente.
Gli step di Delete e di Update sono inutili quindi è ovvio che le Sp AllineaSenzaMerge e AllineaConMerge siano meno performanti nonostante il numero di operazioni I/O sia lo stesso per tutte 3 le Sp......
Esempio 2 : Allineamento TargetTable con SourceTable ( 3000000 righe modificate)
Ora che le tabelle Source e Target sono allineate, simuliamo un utilizzo della tabella Source con operazioni di Delete, Update e Insert.
Simulo la cancellazione, la modifica e l'inserimento di 1000000 di record nella tabella SourceTable.
In tutto sono 3000000 le righe "modificate".
Use DBSource Go Exec ModifySourceData 1000000 Go
Allineamento con Truncate e Re-inserimento massivo
Use DBTarget Go Set statistics Io On Set Statistics Time On Go Exec Ripopola Go Set statistics time off Set statistics io Off GO
Dato che il numero di righe in SourceTable non è cambiato il tempo d'esecuzione della Sp è praticamente il medesimo del test precedente, circa 30 secondi.
Proviamo con la SP dbo.AllineaSenzaMerge
N.B. La tabella viene riportata alla condizione precedente con la sp dbo.ResetTable
Use DBTarget Go Exec dbo.ResetTable Go Set statistics Io On Set statistics Time On GO Exec dbo.AllineaSenzaMerge GO Set statistics time off Set statistics io Off GO
Stavolta ha impiegato circa 32 secondi...
Proviamo il Merge
Use DBTarget Go Exec dbo.ResetTable Go Set statistics Io On Set statistics Time On GO Exec dbo.AllineaConMerge GO Set statistics time off Set statistics io Off GO
Il Merge ha girato in 24 secondi.....
La spiegazione sta nel numero di operazioni di I/O effettuate.
La sp dbo.Ripopola deve ricostruire da zero la tabella, quindi implementa moltissimo I/O.
La sp dbo.AllineaSenzaMerge per ognuno dei 3 step deve cercare le righe coinvolte e modificarle, quindi moltissimo I/O....
Il Merge implementa un solo passaggio e quindi una sola lettura delle tabelle, mentre Update, Delete ed Insert vengono implementati all'unisono....
Dopotutto meno I/O faccio più veloce vado ed il Merge in questo caso ci aiuta...
Ciao
Luca
Nessun commento:
Posta un commento