Update a table faster than an INNER JOIN

Question

I have a SQL Server 2012 stored procedure with an inner join that takes 7,387ms to execute (I saw it in Activity Monitor):

[...]
declare @childrenIds as dbo.CodeIdList;

[...]
Update
    dbo.Code
set
    dbo.Code.CommissioningFlag = 21
From 
    dbo.Code
INNER JOIN
    @childrenIds c
ON
    dbo.Code.CodeId =  c.CodeId

CodeIdList is:

CREATE TYPE [dbo].[CodeIdList] AS TABLE (
    [CodeId] INT NULL
);

Is there a faster way to update the Code table than using an INNER JOIN?

I'm not a dba so I don't know if you need more details about my question. If you need them, please ask.

I have run sp_helpindex on Code table with these results:

index_name     | index_description
PK_CODE        | clustered, unique, primary key located on PRIMARY   | CodeId
UC_CODE_SERIAL | nonclustered, unique, unique key located on PRIMARY | Serial

Code table creation script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [ ...]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)

At this moment there are 1.006.896 rows in the Code table.

Execution plan:

XML version at http://pastebin.com/9yXsRfva

SQL Server version info:

Microsoft SQL Server Management Studio                      11.0.6020.0 Herramientas cliente de Microsoft Analysis Services         11.0.6020.0 Microsoft Data Access Components (MDAC)                      6.1.7601.17514
Microsoft MSXML                                              3.0 4.0 6.0
Microsoft Internet Explorer                                  9.11.9600.18314
Microsoft .NET Framework                                     4.0.30319.42000
Sistema operativo                                            6.1.7601

Select @@VERSION:

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 15:36:27
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on
Windows NT 6.1  (Build 7601: Service Pack 1)

Answer 1

I have added this index and it has sped up the join to 0ms average duration:

CREATE INDEX [IDX_FLAG_LEVEL_CODE]
     ON Code (CommissioningFlag, AggregationLevelId)
     INCLUDE (Serial, CodeId);

I found the solution by chance, while resolving my previous question.

Answer 2

You didn't indicate your service pack level, but you might want to check out this post from Aaron Bertrand regarding a 2012 Service Pack 2 trace flag that 'might' improve performance when joining against table variables. According to the post:

"If a table variable is joined with other tables in SQL Server, it may result in slow performance due to inefficient query plan selection because SQL Server does not support statistics or track number of rows in a table variable while compiling a query plan."

Additionally, it would be interesting to see if using a temporary table instead of a table variable might make a difference.

Posting the XML of the 'actual' execution plan(s) might give us more information as to how your join is being performed.

Answer 3

I would add a <> 21 so it can skip any that are already set

Update dbo.Code
set    dbo.Code.CommissioningFlag = 21
From   dbo.Code
JOIN   @childrenIds c
ON     dbo.Code.CodeId =  c.CodeId 
where  dbo.Code.CommissioningFlag <> 21 or dbo.Code.CommissioningFlag is null

If CodeID is unique in CodeIdList then declaring it as PK would probably help

Answer 4

The UPDATE statement

UPDATE dbo.Code
SET dbo.Code.CommissioningFlag = 21
FROM dbo.Code
INNER JOIN @childrenIds c
ON dbo.Code.CodeId = c.CodeId;

suggests @childrenIds should be unique and not null.

Try to

DECLARE @childrenIds AS table ([CodeId] int PRIMARY KEY);

and observe this constraint when populating @childrenIds (be careful not to try inserting duplicate or NULL CodeId into @childrenIds at any point).