T-SQL e caricamento dati differenziale..... E le performance ?

Immaginiamo questo scenario :
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

1. Truncate/Delete di TargetTable
2. Ri-popolamento totale partendo dai dati di SourceTable

Soluzione 2

1. Cerco le righe presenti in TargetTable ma non in SourceTable e se necessario le elimino
2. Cerco le righe differenti tra TargetTable e SourceTable e le aggiorno con i dati di SourceTable
3. Cerco le righe presenti in SourceTable ma non in TargetTable e le inserisco

Soluzione 3

1. Eseguo il Merge delle tabelle

Il costrutto Merge, introdotto dall'edizione SQL Server 2008 (ben 5 anni fa ma che purtroppo non vedo molto utilizzato) consente, con un unico comando, di effettuare Delete, Update ed Insert in una tabella con un solo comando.

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
Go

Creo 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);

GO

Ho creato nel DB 4 SP

1. dbo.ResetTable= Ripristina la situazione iniziale del test
2. dbo.Ripopola = Allina la tabella con un re-inserimento totale dei dati
3. dbo.AllineaSenzaMerge = Allinea la tabella eliminando, modificando, inserendo solo le righe necessarie in 3 step T-SQL differenti (Delete, Update, Insert)
4. dbo.AllineaConMerge = Allinea la tabella eliminando, modificando, inserendo solo le righe necessarie con il solo comando merge.

Popoliamo la tabella SourceTable di DBSource con circa 16 milioni di Righe.
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