4

I'm working in SQL Server 2019 and facing an issue where when making an update, it takes 20 seconds for only 162 rows.

Update FT SET
    ft.ValueName=avo.name
FROM #package FT 
inner join parts.Nop_PackageAttribute PA with(nolock)
    on PA.PackageID=ft.PackageID
        and PA.[Key]=FT.ZfeatureId 
inner join Nop_AcceptedValuesOption AVO with(nolock)
    ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value 
where FT.AcceptedValueID is not null

Scripts sample

 create table #package
 (
 id int PRIMARY KEY IDENTITY(1,1),
 ZfeatureId INT NULL,
 AcceptedValueID INT NULL,
 PackageID INT NULL,
 ValueName NVARCHAR(2000) default ''
 )

Indexes I have on the #package table

 create nonclustered index IDX_PackageID on #package(PackageID) include (ZfeatureId,AcceptedValueID , ValueName)
 create index acceptedvaluesidpackage_idx on #package(AcceptedValueID)

Package attributes table

ALTER TABLE [Parts].[Nop_PackageAttribute] ADD  CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED 
(
    [PackageAttributeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [_dta_index_Nop_PackageAttribute_8_578153155__K2_K1_K3_4] ON [Parts].[Nop_PackageAttribute]
(
    [PackageID] ASC,
    [PackageAttributeID] ASC,
    [Key] ASC
)
INCLUDE (   [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]

CREATE NONCLUSTERED INDEX [IDX_Key] ON [Parts].[Nop_PackageAttribute]
(
    [Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]


CREATE NONCLUSTERED INDEX [IDX_PakageID] ON [Parts].[Nop_PackageAttribute]
(
    [PackageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [IX_Nop_PackageAttribute_Key] ON [Parts].[Nop_PackageAttribute]
(
    [Key] ASC
)
INCLUDE (   [PackageID],
    [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]


CREATE TABLE [Parts].[Nop_PackageAttribute](
    [PackageAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [PackageID] [int] NOT NULL,
    [Key] [int] NOT NULL,
    [Value] [nvarchar](max) NOT NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [DeletedDate] [datetime] NULL,
    [DeletedBy] [int] NULL,
 CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED 
(
    [PackageAttributeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
) ON [Customer] TEXTIMAGE_ON [PRIMARY]

Nop_AcceptedValuesOption table

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD  CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
(
    [AcceptedValuesOptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [_dta_index_Nop_AcceptedValuesOption_5_1669580986__col__] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesOptionID],
    [AcceptedValuesID],
    [Name],
    [DisplayOrder],
    [Description],
    [CreatedDate],
    [CreatedBy],
    [ModifiedDate],
    [ModifiedBy],
    [DeletedDate],
    [DeletedBy],
    [Is_Split],
    [AcceptedValuesOption_Value],
    [AcceptedValuesOption_Unit]
)WITH (DROP_EXISTING = OFF) ON [Customer]

CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K1_3] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesOptionID] ASC
)
INCLUDE (   [Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K2_K4_1_3] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesID] ASC,
    [DisplayOrder] ASC
)
INCLUDE (   [AcceptedValuesOptionID],
    [Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160824-070515] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160829-015901] ON [dbo].[Nop_AcceptedValuesOption]
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO

CREATE TABLE [dbo].[Nop_AcceptedValuesOption](
    [AcceptedValuesOptionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AcceptedValuesID] [int] NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
    [DisplayOrder] [int] NOT NULL,
    [Description] [varchar](250) NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [DeletedDate] [datetime] NULL,
    [DeletedBy] [int] NULL,
    [Is_Split] [int] NULL,
    [AcceptedValuesOption_Value] [float] NULL,
    [AcceptedValuesOption_Unit] [nvarchar](20) NULL,
    [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
(
    [AcceptedValuesOptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
) ON [Customer]

GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD  CONSTRAINT [DF_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] CHECK CONSTRAINT [FK_Nop_AcceptedValuesOption_Nop_AcceptedValues]
GO

So, how to enhance the update statement to be faster?

0

3 Answers 3

10

Adding to Brendan’s answer, swap this conversion

inner join  Nop_AcceptedValuesOption AVO with(nolock) 
ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value 

to

inner join  Nop_AcceptedValuesOption AVO 
ON AVO.AcceptedValuesOptionID = try_cast(PA.Value as int)

And you should be able to replace the non-clustered columnstore scan with 162 index lookups. If you don't get a nested loop plan, try inner loop join.

2
  • As I think about this one, wouldn't putting a try_cast around a PA.Value make that column susceptible to the same issues as having a convert around AVO.AcceptedValuesOptionID? I understand why it's an acceptable tradeoff in this specific query plan, due to the row count coming back. But, I wouldn't want people to blindly think that this is always the right answer, and that just shifting the data type conversion from one column to the other is always the right thing to do. Curious on your thoughts? Commented Mar 12, 2022 at 18:41
  • Yes. Optimal would be to not have an EAV table with nvarchar(values) that match to int columns in other tables. But the best of a bad design is to force the conversion onto the smaller table. And it's try_cast than enables that. Commented Mar 12, 2022 at 19:26
6

You should avoid wrapping functions around columns in your WHERE clauses. By wrapping a CONVERT around AcceptedValuesOptionID, SQL Server has to run that function on every row in the table before it can know if the value is equal to PA.Value.

With that said, this is most likely the portion of you WHERE clause that is causing your slowness.

convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value

Two things I see happening.

  1. The data from this table is being retrieved with a nonclustered index scan.
  2. Then a hash match is performed to then combine it with the results of the other two tables.

See if there is another way to join to that table, where the columns on both sides of the join are the same data type.

Another option could be to change the data type on AVO.AcceptedValuesOptionID. But, changing data types in an existing table is risky. You'd need to make sure that you do not break other queries in the process.

Additionally, if you know the specific scenarios where you can always expect PA.value to contain INT data, you could consider creating a Temp table and inserting those subsets of rows into the temp table. You would create this temp table with that column as an INT data type, and then use the temp table in the original join, instead of the base table.

-2

This looks like a problem I've been bitten by in the past, I still have the scars.

Try this:

convert(nvarchar(20),AVO.AcceptedValuesOptionID)=PA.Value

Here's the thing, PA.Value is nvachar, but you convert AVO.AcceptedValuesOptionID to a varchar. This causes SQL Server to first convert every PA.Value to varchar before comparing. And this is an expensive conversion because it needs to take into account all sorts of equivalency rules for non-ascii characters.

(The suggestions in the other answers may still improve performance further, but I strongly suspect this is the big culprit.)

1
  • 2
    nvarchar has a higher data type precedence than varchar, so the varchar will be converted to nvarchar for comparison. Not ideal, but not a significant cause of the bad plan. From the plan:ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),CONVERT(varchar(20),[Z2DataCore].[dbo].[Nop_AcceptedValuesOption].[AcceptedValuesOptionID] as [AVO].[AcceptedValuesOptionID],0),0)"> Commented Feb 25, 2022 at 18:21

Your Answer

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

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