2

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:

enter image description here

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)
1
  • 2
    If CodeId in @childrenIds is unique you can define that column as PRIMARY KEY in the dbo.CodeIdList definition and it may help with the JOIN. If it is not unique, see stackoverflow.com/questions/886050/… how to create an index on a table variable. Sep 29, 2016 at 8:18

4 Answers 4

4

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.

0
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.

1
  • The XML that you posted 'appears' to be the 'estimated' execution plan and not the 'actual' plan. The 'actual' execution plan would be more helpful. Also, your query runs in about 7 seconds. At what point would you be satisfied with the run time? Is there a business requirement that needs to query to perform better? Performance tuning should be a cost and benefit thing. Does the cost involved with tuning this query benefit you 'enough' to warrant the effort. If a query running 7 seconds is run 10,000 or more times a day, maybe the cost is worth it. If its run one a day, maybe not. Sep 29, 2016 at 15:12
1

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

1
  • 2
    If CommissioningFlag can be NULL, then with such WHERE filter rows that had CommissioningFlag = NULL would not be updated. Sep 29, 2016 at 8:29
0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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