4
\$\begingroup\$

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) + 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
\$\endgroup\$
3
  • \$\begingroup\$ 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) \$\endgroup\$ – Adeptus May 12 '16 at 23:53
  • \$\begingroup\$ Tried compound key column, didn't help. \$\endgroup\$ – Adeptus May 16 '16 at 1:00
  • 1
    \$\begingroup\$ Please do not edit your code after receiving answers, even if it's just to comment something out. \$\endgroup\$ – Insane May 18 '16 at 7:13
4
\$\begingroup\$

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

\$\endgroup\$
4
  • \$\begingroup\$ Hi! Welcome to Code Review. Can you please explain in more detail what has changed? \$\endgroup\$ – TheCoffeeCup May 17 '16 at 15:28
  • \$\begingroup\$ Sure thing, TheCoffeeCup. I added a short explanation as requested. \$\endgroup\$ – Chris Bergin May 17 '16 at 16:13
  • \$\begingroup\$ Should MERGE statement be used ? It might help to decrease the time of jointure. \$\endgroup\$ – Mincong Huang Jun 3 '16 at 21:32
  • \$\begingroup\$ 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. \$\endgroup\$ – Chris Bergin Jun 5 '16 at 20:25
2
\$\begingroup\$

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.
\$\endgroup\$
1
\$\begingroup\$

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 
INSTEAD OF INSERT
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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.