1

Topic: MERGE vs Triggered VIEW

Kind time of days, colleagues.

Select @@ version:
Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
Aug 13 2014 11:36:34 AM
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

There was a question of productivity for creativity  authors for SSIS ETL a packet.
Situation:
There is everyday Bulk a loading in  tables from csv. Deltas are not present, . the data each time all. (Percent of changes we assume <=10 %.)
Further accordingly there is a comparing  (about 6 million) and  (new to add, existing to update, removals are not present therefore the table about 80 million).
It is implemented as follows in a packet:
Lookup tasl and if  on a condition (a unique dial-up 3 attributes) coincide - there is an insertion attempt in "fake" View (with INSTEAD OF Insert trigger').
The trigger  does Update in the necessary table Target.
Trigger body:

CREATE TRIGGER dbo.trigger_name
ON dbo. View_name
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE t
SET
t.col1 = tt.col1;
t.col2 = tt.col2;
t.col3 = tt.col3;
t.col4 = tt.col4;
t.col5 = tt.col5
.......
FROM dbo.target_table t
INNER JOINinserted tt
ON t.predicateCol1 = tt.predicateCol1
AND t.predicateCol2 = tt.predicateCol2
AND t.predicateCol3 = tt.predicateCol3
END

The example "fake" View on which hangs the trigger:

CREATE VIEW dbo. View_name
AS
SELECT TOP (1) *
FROM dbo.target_table

I insist on change of architecture in favor of classical MERGE statement + Partitioning. But belief from an underside (without demonstrative and it is feeble ) simply heap. Speak 100 times checked up - MERGE worse.
Whether in this connection at me arose : real e pluses in favor of architecture (Lookup +View + trigger instead of insert at update of the data) for productivity improving?
In advance thanks for the argumentation and speculation on a subject.

2

Re: MERGE vs Triggered VIEW

3

Re: MERGE vs Triggered VIEW

Sanych wrote:

Kind time of days, the colleague.

Select @@ version:
Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
Aug 13 2014 11:36:34 AM
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

There was a question of productivity for creativity  authors for SSIS ETL a packet.
Situation:
There is everyday Bulk a loading in  tables from csv. Deltas are not present, . the data each time all. (Percent of changes we assume <=10 %.)
Further accordingly there is a comparing  (about 6 million) and  (new to add, existing to update, removals are not present therefore the table about 80 million).
It is implemented as follows in a packet:
Lookup tasl and if  on a condition (a unique dial-up 3 attributes) coincide - there is an insertion attempt in "fake" View (with INSTEAD OF Insert trigger').
The trigger  does Update in the necessary table Target.
Trigger body:

CREATE TRIGGER dbo.trigger_name
ON dbo. View_name
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE t
SET
t.col1 = tt.col1;
t.col2 = tt.col2;
t.col3 = tt.col3;
t.col4 = tt.col4;
t.col5 = tt.col5
.......
FROM dbo.target_table t
INNER JOINinserted tt
ON t.predicateCol1 = tt.predicateCol1
AND t.predicateCol2 = tt.predicateCol2
AND t.predicateCol3 = tt.predicateCol3
END

The example "fake" View on which hangs the trigger:

CREATE VIEW dbo. View_name
AS
SELECT TOP (1) *
FROM dbo.target_table

I insist on change of architecture in favor of classical MERGE statement + Partitioning. But belief from an underside (without demonstrative and it is feeble ) simply heap. Speak 100 times checked up - MERGE worse.
Whether in this connection at me arose : real e pluses in favor of architecture (Lookup +View + trigger instead of insert at update of the data) for productivity improving?
In advance thanks for the argumentation and speculation on a subject.

Our experience (old) passage with ssis lookup + insert/update on ssis bulk + merge c insert/update gave a productivity gain in times (if not on the order).
To make "full-scale" experiment, like, simply enough.

4

Re: MERGE vs Triggered VIEW

[quote =. Evgenie] you need to think of pulling out real delta instead of repeated crunching of the same data.

If data source is the external system on which there is no influence, or changes in it under needs of "delta" are impossible, "pull out real delta" can to be very much and very inconveniently.