Início / dba / Perguntas / 307942
Accepted
ahmed elbarbary
Asked: 2022-02-25 13:43:54 +0800 CST

Como aprimorar a atualização para ser mais rápida para uma pequena contagem de linhas atualizadas?

  • 772

Estou trabalhando no SQL Server 2019 e enfrento um problema em que, ao fazer uma atualização, leva 20 segundos para apenas 162 linhas.

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

Amostra de scripts

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

Índices que tenho na tabela #package

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

Tabela de atributos do pacote

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]

Tabela Nop_AcceptedValuesOption

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

Então, como melhorar a instrução de atualização para ser mais rápida?

sql-server query-performance

3 respostas

  1. Best Answer

    Adicionando à resposta de Brendan, troque essa conversão

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

    para

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

    E você deve poder substituir a verificação de columnstore não clusterizado por 162 pesquisas de índice. Se você não obtiver um plano de loop aninhado, tente inner loop join.

    • 10

  2. Você deve evitar envolver funções em torno de colunas em suas WHEREcláusulas. Ao envolver um , o SQL Server precisa executar essa função CONVERTem AcceptedValuesOptionIDtodas as linhas da tabela antes de saber se o valor é igual a PA.Value.

    Com isso dito, esta é provavelmente a parte de sua WHEREcláusula que está causando sua lentidão.

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

    Duas coisas que vejo acontecendo.

    1. Os dados desta tabela estão sendo recuperados com uma varredura de índice não clusterizado.
    2. Em seguida, é realizada uma correspondência de hash para combiná-la com os resultados das outras duas tabelas.

    Veja se há outra maneira de unir a essa tabela, onde as colunas em ambos os lados da união são do mesmo tipo de dados.

    Outra opção pode ser alterar o tipo de dados em AVO.AcceptedValuesOptionID. Mas alterar os tipos de dados em uma tabela existente é arriscado. Você precisa ter certeza de não quebrar outras consultas no processo.

    Além disso, se você conhece os cenários específicos em que sempre pode esperar PA.valueconter dados INT, considere criar uma tabela temporária e inserir esses subconjuntos de linhas na tabela temporária. Você criaria essa tabela temporária com essa coluna como um tipo de dados INT e, em seguida, usaria a tabela temporária na junção original, em vez da tabela base.

    • 6

  3. Isso parece um problema pelo qual fui mordido no passado, ainda tenho as cicatrizes.

    Tente isto:

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

    Aqui está a coisa, PA.Value é n vachar, mas você converte AVO.AcceptedValuesOptionID em um varchar. Isso faz com que o SQL Server primeiro converta cada PA.Value em varchar antes de comparar. E esta é uma conversão cara porque precisa levar em conta todos os tipos de regras de equivalência para caracteres não ASCII.

    (As sugestões nas outras respostas ainda podem melhorar ainda mais o desempenho, mas suspeito fortemente que esse seja o grande culpado.)

    • -1

relate perguntas