Início / dba / Perguntas / 307708
Accepted
ahmed elbarbary
Asked: 2022-02-19 10:43:19 +0800 CST

Instrução de atualização muito lenta, levando até 11 minutos para atualizar 20 ou 0 linhas, mesmo com índices

  • 772

Quando eu atualizo 20 linhas ou nenhuma linha, leva 11 minutos.

Quero dizer 20 linhas ou nenhuma linha diferente m.MaximumReflowTemperatureID <> r.z2valueidentre duas tabelas.

Por que minha atualização é tão lenta, mesmo que eu atualize um pequeno número de linhas ou mesmo nenhuma linha?

Como lidar com isso?

Meu plano de execução real:

https://www.brentozar.com/pastetheplan/?id=HJlS11Fy5

Atualização de declaração que demora muito:

update r
set r.z2valueid=m.MaximumReflowTemperatureID
from [OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures] r
    inner join z2datacore.parts.manufacturingdata m with(nolock)
        on m.partid=r.zpartid
    where m.MaximumReflowTemperatureID <> r.z2valueid

ao tentar a instrução acima substituindo update por select, leva o mesmo tempo, 11 minutos.

A tabela que preciso atualizar [OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures]tem 14 milhões de linhas e a outra tabela na junção tem 15 milhões de linhas.

Exemplo de script de tabelas:

CREATE TABLE [dbo].[ManufactureMaximumReflowTemperatures](
     [ID] [int] NOT NULL,
     [zpartid] [int] NULL,
     [key] [varchar](50) NULL,
     [value] [varchar](60) NULL,
     [Z2ValueID] [int] NULL,
     [csfeatureid] [int] NULL,
     [csvalueid] [int] NULL,
 PRIMARY KEY CLUSTERED 
 (
     [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY] 
 GO   

 ALTER TABLE [dbo].[ManufactureMaximumReflowTemperatures] ADD PRIMARY KEY CLUSTERED 
 (
     [ID] 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 [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [featurenameandvalue_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [csfeatureid] ASC,
     [Z2ValueID] 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 [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [featurenames_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [csfeatureid] 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 [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [manufacturemax_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [Z2ValueID] ASC,
     [value] 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 [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [manufacturemaxvalues_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [Z2ValueID] ASC,
     [csvalueid] 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 [PRIMARY]
 GO
 CREATE NONCLUSTERED INDEX [zpartid_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [zpartid] 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 [PRIMARY]
 GO
 CREATE TABLE [Parts].[ManufacturingData](
     [LeadFinishId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     [PartID] [int] NOT NULL,
     [LeadFinishMaterial] [varchar](50) NULL,
     [CreatedDate] [datetime] NULL,
     [CreatedBy] [int] NULL,
     [ModifiedDate] [datetime] NULL,
     [Modifiedby] [int] NULL,
     [DeletedDate] [datetime] NULL,
     [DeletedBy] [int] NULL,
     [MaximumReflowTemperatureID] [int] NULL,
        
  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] 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 [Parts].[ManufacturingData] ADD  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] 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]
sql-server t-sql

1 respostas

  1. Best Answer

    Esses dois pontos do seu post serão seus maiores fatores de contribuição.

    tem 14 milhões de linhas

    onde m.MaximumReflowTemperatureID <>r.z2valueid

    Para começar, sempre que você comparar duas colunas em uma tabela, independentemente de comparações de igualdade ou desigualdade, o SQL Server terá que comparar todas as linhas da tabela para ver se duas colunas atendem à condição. Não é como um cenário em que você está fazendo uma busca de índice para encontrar uma linha com ID = 5. No seu caso, o valor que você compararia mudaria linha por linha. Portanto, não há como BUSCAR esses dados.

    Adicione ao fato de que você tem 14 milhões de linhas em uma tabela e 15 milhões de linhas na outra.

    Imagine sua consulta como um SELECT, mas sem a condição <>.

    select m.MaximumReflowTemperatureID, r.z2valueid
from [OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures] r
    inner join z2datacore.parts.manufacturingdata m with(nolock)
        on m.partid=r.zpartid

    Execute esse select e veja quanto tempo leva e quantas linhas ele retorna. Talvez pegue um plano de execução enquanto estiver nisso. Agora imagine o SQL Server tendo que puxar todas essas linhas, toda vez que você executar sua atualização, mesmo que não haja valores desiguais. Isso porque ele precisa olhar para as duas colunas, lado a lado, antes de saber se precisa atualizar essa linha.

    Comparar duas colunas quase sempre será uma consulta dolorosa. No entanto, os seguintes índices poderiam pelo menos torná-lo mais tolerável.

    CREATE NONCLUSTERED INDEX [zpartid_idx]
    ON [dbo].[ManufactureMaximumReflowTemperatures] ([zpartid])
    INCLUDE (z2valueid)

CREATE NONCLUSTERED INDEX [manufacturingdata_partid]
    ON [dbo].[manufacturingdata] ([partid])
    INCLUDE (MaximumReflowTemperatureID)

    Com esses índices, esperamos que você puxe índices menores para a memória, ou seja, menos leituras. Isso ocorre porque cada um desses índices tem duas colunas cada, em vez de todas as colunas das tabelas base. Isso deve ajudar sua consulta a ser executada mais rapidamente. Sem isso, é mais provável que você faça verificações em cluster.

    Além disso, certifique-se de tirar a dica NOLOCK.

    • 4

relate perguntas