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]
INSTEAD OF INSERT
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) + COD

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]).

StackExchange Code Review Q#128148, answer score: 4