ChrisM@Work (10/17/2016)

---

Rechana Rajan (10/17/2016)

---

ChrisM@Work (10/17/2016)

---

There are at least two issues here which will unnecessarily prolong an INSERT transaction using this process.

Firstly, as Jeff pointed out, the SELECT. Use the OUTPUT clause instead, like this:

DECLARE

@0 bigint,

@1 bigint,

@2 varchar(50),

@3 bit,

@4 varchar(50),

@5 datetime2(7),

@6 varchar(50),

@7 datetime2(7)

INSERT dbo.Transaction ([TransactionId], [ConID], [TransactionStatus], [Status], [CreatedUserId], [CreatedDate], [UpdatedUserId], [UpdatedDate])

OUTPUT inserted.[Oid]

VALUES (@0, @1, @2, @3, @4, @5, @6, @7)

One less read of the table.

Secondly, the Transaction table has a couple of foreign keys:

Transaction.TransactionId has to match a PK value in BTrans (BTrans. oid)

Transaction.ConId has to match a PK value in DOCons (DOCons.oid)

In each case, the PK is clustered and the plan shows a clustered index seek. This is an unnecessarily wide index when all you want to do is check that the PK exists. A more efficient index for this purpose would be a unique non-clustered index on the PK alone. Shared locks are taken during these reads so this may be significant.

There are implicit conversions from DATETIME2 to DATETIME for two of the parameters, I don't think this is significant.

The version of the plan which you posted wasn't retrieved from cache so may not be the same as the version which is causing the problem.

Interesting: both of the clustered index seek operators (for the FK lookups) have "forced index" = True in their property sheets.

Thanks Chris.

I tried creating unique NC index on the oid column but the execution plan is still using the clustered index. I am doing something wrong.

No, you're not. For the constraint to pick up the new index, you will need to drop and recreate it. The IndexID is held in the system view sys.foreign_keys.