Speed up 'instead of INSERT' Trigger

Question

[replaced the initial placed code in the question. I thought the use of a FKHelper would increase speed, but it didn't. See sepupic his comment. Here is my first used code en schema, before the use of FKHelper]

I have a table for a client which has a couple of million rows (about 8-10). I need to use some kind of Insert Ignore and or On Duplicate Key update statements like MySQL has.

This is the table:

USE [BackOffice]
GO

/****** Object:  Table [BackOffice].[EsmaInstrumentsTable]    Script Date: 2019-11-12 15:35:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [BackOffice].[EsmaInstrumentsTable](
    [Isin] [varchar](12) NOT NULL,
    [FullName] [varchar](254) NULL,
    [ShortName] [varchar](128) NULL,
    [CFICode] [varchar](6) NOT NULL,
    [Currency] [varchar](3) NOT NULL,
    [ISOExchangeName] [varchar](4) NOT NULL,
    [FirstTradeDT] [datetime2](7) NOT NULL,
    [LastTradeDT] [datetime2](7) NOT NULL,
    [ExpiryDT] [datetime2](7) NOT NULL,
    [Multiplier] [decimal](16, 8) NOT NULL,
    [OptionType] [varchar](4) NOT NULL,
    [ExerciseStyle] [varchar](6) NOT NULL,
    [DeliveryType] [varchar](20) NOT NULL,
    [StrikePrice] [decimal](16, 6) NOT NULL,
    [UnderLyingISIN] [varchar](12) NOT NULL,
    [CompanyCountry] [varchar](2) NULL,
    [InstrTerminated] [tinyint] NULL,
 CONSTRAINT [EsmaInstrumentsTablePrimaryKey] PRIMARY KEY CLUSTERED 
(
    [Isin] ASC,
    [CFICode] ASC,
    [ISOExchangeName] ASC,
    [ExpiryDT] ASC,
    [StrikePrice] ASC,
    [Currency] ASC,
    [UnderLyingISIN] ASC,
    [OptionType] ASC,
    [ExerciseStyle] ASC,
    [DeliveryType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Because I wanted to reduce the searches in columns, I made a "PKHelper" column. This is the Primary Key.

as a trigger I made:

USE [BackOffice]
GO
/****** Object:  Trigger [BackOffice].[CheckBeforeInsertOnEsmaInstrumentsTable]    Script Date: 2019-11-12 15:38:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create TRIGGER [BackOffice].[CheckBeforeInsertOnEsmaInstrumentsTable] on [BackOffice].[EsmaInstrumentsTable]
instead of INSERT
AS
BEGIN
  SET NOCOUNT ON

    MERGE
    INTO    BackOffice.EsmaInstrumentsTable _old
    USING   inserted _new
    ON      
    _new.Isin = _old.Isin
    and
    _new.ExpiryDT = _old.ExpiryDT
    and
    _new.StrikePrice = _old.StrikePrice
    and
    ISNULL(_new.Currency,'') = ISNULL(_old.Currency,'')
    and
    ISNULL(_new.CFICode,'') = ISNULL(_old.CFICode,'')
    and
    ISNULL(_new.OptionType,'') = ISNULL(_old.OptionType,'')
    and
    ISNULL(_new.ExerciseStyle,'') = ISNULL(_old.ExerciseStyle,'')
    and
    ISNULL(_new.DeliveryType,'') = ISNULL(_old.DeliveryType,'')
    and
    ISNULL(_new.ISOExchangeName,'') = ISNULL(_old.ISOExchangeName,'')
    WHEN NOT MATCHED THEN
    INSERT  (Isin,FullName,ShortName,CFICode,Currency,ISOExchangeName,FirstTradeDT,LastTradeDT,ExpiryDT,Multiplier,OptionType,ExerciseStyle,DeliveryType,StrikePrice,UnderLyingISIN,CompanyCountry,InstrTerminated)
    VALUES  (Isin,FullName,ShortName,CFICode,Currency,ISOExchangeName,FirstTradeDT,LastTradeDT,ExpiryDT,Multiplier,OptionType,ExerciseStyle,DeliveryType,StrikePrice,UnderLyingISIN,CompanyCountry,InstrTerminated)
    WHEN MATCHED THEN
    Update set 
    _old.FullName = ISNULL(_new.FullName,_old.FullName),
    _old.ShortName = ISNULL(_new.ShortName,_old.ShortName),
    _old.FirstTradeDT = ISNULL(_new.FirstTradeDT,_old.FirstTradeDT),
    _old.LastTradeDT = ISNULL(_new.LastTradeDT,_old.LastTradeDT),
    _old.UnderLyingISIN = ISNULL(_new.UnderLyingISIN,_old.UnderLyingISIN),
    _old.CompanyCountry = ISNULL(_new.CompanyCountry,_old.CompanyCountry),
    _old.InstrTerminated = ISNULL(_new.InstrTerminated,_old.InstrTerminated);
END;

And my insert statement looks like this

WITH NewData AS 
(SELECT * FROM (VALUES 
('FRENX4805854','POAS1.2003.03656.C','ENXT FR/O 20200320 C A ALS 36.56','OCASPS','EUR','XMON','2019-03-18 01:00:00.000','2020-03-20 17:30:00.000','2020-03-20 00:00:00.000',100,'CALL','AMER','PHYS',36.56,'FR0010220475','FR',0)
-- up to 2500 rows
) x 
(Isin,FullName,ShortName,CFICode,Currency,ISOExchangeName,FirstTradeDT,LastTradeDT,ExpiryDT,Multiplier,OptionType,ExerciseStyle,DeliveryType,StrikePrice,UnderLyingISIN,CompanyCountry,InstrTerminated) 
) INSERT INTO 
EsmaInstrumentsTableNew(Isin,FullName,ShortName,CFICode,Currency,ISOExchangeName,FirstTradeDT,LastTradeDT,ExpiryDT,Multiplier,OptionType,ExerciseStyle,DeliveryType,StrikePrice,UnderLyingISIN,CompanyCountry,InstrTerminated) SELECT Isin,FullName,ShortName,CFICode,Currency,ISOExchangeName,FirstTradeDT,LastTradeDT,ExpiryDT,Multiplier,OptionType,ExerciseStyle,DeliveryType,StrikePrice,UnderLyingISIN,CompanyCountry,InstrTerminated 
FROM NewData

I use this way of inserting, to be able to add more than 1000 rows per statement (limit is set at 1000) Unfortunately this takes quite a while. any suggestions to speed thinks up? (CPU is always around 12.5% on a 4 Core 8 Logical processors machine. and 100/8 is 12.5)

kind regards,

Matthijs

>>>Because I wanted to reduce the searches in columns, I made a "PKHelper" column<<< It's useless here. Pseudotables **inserted** and **deleted** are not indexed and cannot be indexed. — sepupic, Nov 12 '19 at 14:33
After running the first test, this kind of figures....it even seems to be slower. Will update my post with the code I used before this "solution" — user369122, Nov 12 '19 at 14:34
Why do you insist on trigger? Rewrite your Inserts in order to not insert what already exists, and create appropriate index — sepupic, Nov 12 '19 at 14:36
...And it's still not clear what combination of fields should be unique, all the columns??? It's very "strange" table where PK is made of all the fields, it's very poor design, something goes wrong here — sepupic, Nov 12 '19 at 14:39
Unfortunately the PK indeed needs all of these columns to make sure a row is unique. If either one differs, it's a different instrument. (that's why I came up with the PKHelper in the first place) — user369122, Nov 12 '19 at 14:45
Besides normalizing some of the columns and use a int foreign key, what could help speed up the trigger? — user369122, Nov 12 '19 at 14:58
Why do you have all these `ISNULL` tests? If a column is a part of the primary key, it can't be nullable by definition... — Zohar Peled, Nov 12 '19 at 15:00
Also, you might want to check out [this Aaron Bertrand's answer](https://stackoverflow.com/a/52780490/3094533). I don't know if it will improve performance, but it's worth a try. — Zohar Peled, Nov 12 '19 at 15:05
Thanks for the tip Zohar. Will play around with it and do some testing for this particular table — user369122, Nov 12 '19 at 15:09

Speed up 'instead of INSERT' Trigger

0 Answers0