# Data import “upsert” from a SSIS package

I have the following "upsert" trigger in SQL Server 2005, which is handling a large data import from a SSIS package. It is taking a very long time to complete - I suspect due to the concatenation and CASTing of keys.

Is there a better, faster way of doing this?

CREATE TRIGGER [dbo].[MP_VOD_UPSERT]
ON  [dbo].[vwMP_VOD]
AS
BEGIN

SET NOCOUNT ON;

UPDATE [dbo].[vwMP_VOD]
SET
[Org] = inserted.Org
,[id_vend] = inserted.id_vend
,[id_org_orig] = inserted.id_org_orig
,[id_vchr_nbr] = inserted.id_vchr_nbr
,[id_vchr_line] = inserted.id_vchr_line
,[id_vchr_line_dist] = inserted.id_vchr_line_dist
,[id_glac_exp] = inserted.id_glac_exp
,[text_ref_4] = inserted.text_ref_4
,[amt_exp_hcur] = inserted.amt_exp_hcur
,[amt_rem_hcur] = inserted.amt_rem_hcur
,[id_tax] = inserted.id_tax
,[code_type_chrg] = inserted.code_type_chrg
,[id_vchr_line_chrg] = inserted.id_vchr_line_chrg
FROM inserted
WHERE inserted.ORG + inserted.ID_VEND + inserted.ID_ORG_ORIG + CAST(inserted.ID_VCHR_NBR AS VARCHAR) + CAST(inserted.ID_VCHR_LINE AS VARCHAR) + CAST(inserted.ID_VCHR_LINE_DIST AS VARCHAR) + inserted.CODE_TYPE_CHRG + inserted.ID_VCHR_LINE_CHRG IN
(
SELECT ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG
FROM inserted
INTERSECT
SELECT ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG
FROM [dbo].[vwMP_VOD]
)

INSERT INTO [dbo].[vwMP_VOD]
SELECT * FROM inserted
WHERE ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG IN
(
SELECT ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG
FROM inserted
EXCEPT
SELECT ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG
FROM [dbo].[vwMP_VOD]
)

END

• Would it be quicker to add the compound key as an extra column (in the table or view) and compute it for each row in SSIS before trying to upsert? (10k+ rows existing & 10k+ rows to upsert) – Adeptus May 12 '16 at 23:53
• Tried compound key column, didn't help. – Adeptus May 16 '16 at 1:00
• Please do not edit your code after receiving answers, even if it's just to comment something out. – Insane May 18 '16 at 7:13

I can pretty much guarantee it's the concatenation that's the problem. Use the columns as columns; you don't want to handcuff the optimizer when it puts together your query plan. Here's a pretty typical "upsert" pattern:

  UPDATE [dbo].[vwMP_VOD]
SET [Org] = inserted.Org
,[id_vend] = inserted.id_vend
,[id_org_orig] = inserted.id_org_orig
,[id_vchr_nbr] = inserted.id_vchr_nbr
,[id_vchr_line] = inserted.id_vchr_line
,[id_vchr_line_dist] = inserted.id_vchr_line_dist
,[id_glac_exp] = inserted.id_glac_exp
,[text_ref_4] = inserted.text_ref_4
,[amt_exp_hcur] = inserted.amt_exp_hcur
,[amt_rem_hcur] = inserted.amt_rem_hcur
,[id_tax] = inserted.id_tax
,[code_type_chrg] = inserted.code_type_chrg
,[id_vchr_line_chrg] = inserted.id_vchr_line_chrg
FROM inserted
INNER JOIN (
SELECT ORG, ID_VEND, ID_ORG_ORIG, ID_VCHR_NBR, ID_VCHR_LINE, ID_VCHR_LINE_DIST, CODE_TYPE_CHRG, ID_VCHR_LINE_CHRG
FROM [dbo].[vwMP_VOD]
) AS V
ON inserted.Org = V.Org
AND inserted.ID_VEND = V.ID_VEND
AND inserted.ID_ORG_ORIG = V.ID_ORG_ORIG
AND inserted.ID_VCHR_NBR = V.ID_VCHR_NBR
AND inserted.ID_VCHR_LINE = V.ID_VCHR_LINE
AND inserted.ID_VCHR_LINE_DIST = V.ID_VCHR_LINE_DIST
AND inserted.CODE_TYPE_CHRG = V.CODE_TYPE_CHRG
AND inserted.ID_VCHR_LINE_CHRG = V.ID_VCHR_LINE_CHRG;

INSERT INTO [dbo].[vwMP_VOD]
SELECT inserted.*
FROM inserted
LEFT JOIN (
SELECT ORG, ID_VEND, ID_ORG_ORIG, ID_VCHR_NBR, ID_VCHR_LINE, ID_VCHR_LINE_DIST, CODE_TYPE_CHRG, ID_VCHR_LINE_CHRG
FROM [dbo].[vwMP_VOD]
) AS V
ON inserted.Org = V.Org
AND inserted.ID_VEND = V.ID_VEND
AND inserted.ID_ORG_ORIG = V.ID_ORG_ORIG
AND inserted.ID_VCHR_NBR = V.ID_VCHR_NBR
AND inserted.ID_VCHR_LINE = V.ID_VCHR_LINE
AND inserted.ID_VCHR_LINE_DIST = V.ID_VCHR_LINE_DIST
AND inserted.CODE_TYPE_CHRG = V.CODE_TYPE_CHRG
AND inserted.ID_VCHR_LINE_CHRG = V.ID_VCHR_LINE_CHRG
WHERE V.Org IS NULL;


What I've done here is to take the your WHERE IN condition and replicate it with a join instead.

In the "update" case, you wanted all rows in the INSERTED table that are also in the [dbo].[vwMP_VOD] table. Textbook inner join. Joining on the key columns should be pretty quick. If not, make sure your table has a PRIMARY KEY defined on these columns; if you have a surrogate key (ID column) instead, then you can still add a UNIQUE INDEX on the columns used to identify the rows in this upsert.

In the "insert" case, you want all rows in the INSERTED table that are not in [dbo].[vwMP_VOD]. To avoid concatenating all the key columns together, I simulated the effects of the EXCEPT operation by using a LEFT JOIN from INSERTED to [dbo].[vwMP_VOD], joining on all key columns, and adding a WHERE condition that checks that a non-nullable column from [dbo].[vwMP_VOD] is null. That reliably indicates that there was no row present in the right-hand table ([dbo].[vwMP_VOD]).

• Hi! Welcome to Code Review. Can you please explain in more detail what has changed? – TheCoffeeCup May 17 '16 at 15:28
• Sure thing, TheCoffeeCup. I added a short explanation as requested. – Chris Bergin May 17 '16 at 16:13
• Should MERGE statement be used ? It might help to decrease the time of jointure. – Mincong Huang Jun 3 '16 at 21:32
• In my testing, the MERGE statement was always significantly slower in our environment using our data. However, you should test in your own environment with your own data. It's always a good idea to test alternatives using a representative data source to make sure you get the right solution for your situation. – Chris Bergin Jun 5 '16 at 20:25

Here's how I got it working.

1. ADD PRIMARY KEYS. This involved some cleaning up of the data, as there were some duplicates. Why this wasn't done in the first place, I don't know (I "inherited" the system).
2. Use the key fields as criteria for the joins (as in Chris Bergin's answer)
3. Don't forget to make the SSIS packages FIRE_TRIGGERS when inserting, otherwise the UPSERT trigger will be ignored.

If anyone is using SQL Server 2008 or upper version, then the conditional behavior described for the MERGE (Transact-SQL) statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match.

CREATE TRIGGER dbo.MP_VOD_MERGE
ON dbo.vwMP_VOD
AS
BEGIN

SET NOCOUNT ON;

MERGE dbo.vwMP_VOD AS target
USING inserted AS source
ON  target.Org = source.Org
AND target.id_vend = source.id_vend
AND target.id_org_orig = source.id_org_orig
AND target.id_vchr_nbr = source.id_vchr_nbr
AND target.id_vchr_line = source.id_vchr_line
AND target.id_vchr_line_dist = source.id_vchr_line_dist
AND target.code_type_chrg = source.code_type_chrg
AND target.id_vchr_line_chrg = source.id_vchr_line_chrg

WHEN MATCHED THEN
UPDATE
SET Org = source.Org
,id_vend = source.id_vend
,id_org_orig = source.id_org_orig
,id_vchr_nbr = source.id_vchr_nbr
,id_vchr_line = source.id_vchr_line
,id_vchr_line_dist = source.id_vchr_line_dist
,id_glac_exp = source.id_glac_exp
,text_ref_4 = source.text_ref_4
,amt_exp_hcur = source.amt_exp_hcur
,amt_rem_hcur = source.amt_rem_hcur
,id_tax = source.id_tax
,code_type_chrg = source.code_type_chrg
,id_vchr_line_chrg = source.id_vchr_line_chrg
WHEN NOT MATCHED THEN
INSERT INTO
(
-- col_1
-- col_2
-- col_3
)
VALUES
(
-- source.col_1
-- source.col_2
-- source.col_3
)

END