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)
CodeId
in@childrenIds
is unique you can define that column asPRIMARY KEY
in thedbo.CodeIdList
definition and it may help with theJOIN
. If it is not unique, see stackoverflow.com/questions/886050/… how to create an index on a table variable.