Otimize esta consulta SQL para desempenho

Question

ahmed elbarbary

Asked: 2022-03-04 15:40:46 +0800 CST2022-03-04 15:40:46 +0800 CST 2022-03-04 15:40:46 +0800 CST

ao atualizar uma tabela grande, demora muito para atualizar

772

Eu trabalho no sql server 2019

ao atualizar a tabela Z2DataCore.parts.SourcingNotMappedParts , achei lento e muito tempo ao atualizar

plano de execução estimado

plano de execução real

A instrução de atualização leva 26,5 minutos para concluir a atualização da contagem de linhas 692488, então Como tornar mais rápido.

statment gerar processo lento como abaixo:

UPDATE s 
SET s.PriorityLevel = 'I2'   
FROM Z2DataCore.parts.SourcingNotMappedParts s 
INNER JOIN extractreports.dbo.alldistSuppliersourceid g
    ON g.SourcingNotMappedPartsID = s.SourcingNotMappedPartsID

contagem de linhas afetadas para atualização será 692488

tabela SourcingNotMappedParts que eu preciso atualizá-lo tem 71 milhões de linhas como geral.

tabela extractreports.dbo.SourcingNotMappedPartsIDI1que vou obter dados para atualizar é 692488 .

tabela extractreports.dbo.SourcingNotMappedPartsIDI1tem apenas coluna SourcingNotMappedPartsIDcomint datatype

tabela extractreports.dbo.SourcingNotMappedPartsIDI1tem apenas um índice como abaixo

 CREATE clustered INDEX SourcingNotMappedPartsIDI1_IDX ON extractreports.dbo.SourcingNotMappedPartsIDI1(SourcingNotMappedPartsID)



USE [Z2DataCore]
 GO
 /****** Object:  Table [Parts].[SourcingNotMappedParts]    Script Date: 3/4/2022 12:05:36 AM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [Parts].[SourcingNotMappedParts](
  [SourcingNotMappedPartsID] [int] IDENTITY(1,1) NOT NULL,
  [SearchPart] [nvarchar](200) NULL,
  [GivenManufacture] [nvarchar](200) NULL,
  [CompanyId] [int] NULL,
  [SourceTypeID] [int] NULL,
  [PartStatus] [nvarchar](50) NULL,
  [StockId] [int] NULL,
  [SourceUrl] [nvarchar](2000) NULL,
  [PartId] [int] NULL,
  [GroupID] [int] NULL,
  [PartStatusID] [int] NULL,
  [MatchStatus] [nvarchar](200) NULL,
  [GivenPartNumber_Non] [nvarchar](200) NULL,
  [GivenManufacturer_Non] [nvarchar](200) NULL,
  [signatureID] [int] NULL,
  [VCompanyId] [int] NULL,
  [PriorityLevel] [nvarchar](10) NULL,
  [NotMappedCode] [int] NULL,
  CONSTRAINT [PK_Parts.SourcingNotMappedParts] PRIMARY KEY CLUSTERED 
 (
  [SourcingNotMappedPartsID] 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
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IDX_MatchStatus_StatusID]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_MatchStatus_StatusID] ON [Parts].[SourcingNotMappedParts]
 (
  [PartStatusID] ASC,
  [MatchStatus] 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
 /****** Object:  Index [IDX_Part_status_ID]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_Part_status_ID] ON [Parts].[SourcingNotMappedParts]
 (
  [PartStatusID] 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
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IDX_SourceURL]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_SourceURL] ON [Parts].[SourcingNotMappedParts]
 (
  [SourceUrl] 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
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IDX_SourcingNotMappedParts_GroupID_SearchPart]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_GroupID_SearchPart] ON [Parts].[SourcingNotMappedParts]
 (
  [GroupID] ASC,
  [SearchPart] ASC
 )
 INCLUDE (  [signatureID]) 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
 /****** Object:  Index [IDX_SourcingNotMappedParts_PartId]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_PartId] ON [Parts].[SourcingNotMappedParts]
 (
  [PartId] 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
 /****** Object:  Index [IDX_SourcingNotMappedParts_SignatureID]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_SignatureID] ON [Parts].[SourcingNotMappedParts]
 (
  [signatureID] ASC
 )
 INCLUDE (  [PartId]) 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
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IX_NotMapped_NonalphaPartCompany]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_NonalphaPartCompany] ON [Parts].[SourcingNotMappedParts]
 (
  [GivenPartNumber_Non] ASC,
  [VCompanyId] 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
 /****** Object:  Index [IX_NotMapped_NotMappedCode]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_NotMappedCode] ON [Parts].[SourcingNotMappedParts]
 (
  [NotMappedCode] 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
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IX_NotMapped_PriorityLevel]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_PriorityLevel] ON [Parts].[SourcingNotMappedParts]
 (
  [PriorityLevel] 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
 /****** Object:  Index [IX_NotMapped_SourceType]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_SourceType] ON [Parts].[SourcingNotMappedParts]
 (
  [SourceTypeID] ASC,
  [CompanyId] 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
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IX_NotMapped_VCompanyId_sourcetypeid]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_NotMapped_VCompanyId_sourcetypeid] ON [Parts].[SourcingNotMappedParts]
 (
  [VCompanyId] ASC,
  [SourceTypeID] ASC,
  [PriorityLevel] ASC
 )
 INCLUDE (  [GivenPartNumber_Non]) 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
 SET ANSI_PADDING ON
    
 GO
 /****** Object:  Index [IX_SourcingNotMappedParts_VCompanyId]    Script Date: 3/4/2022 12:05:37 AM ******/
 CREATE NONCLUSTERED INDEX [IX_SourcingNotMappedParts_VCompanyId] ON [Parts].[SourcingNotMappedParts]
 (
  [VCompanyId] ASC
 )
 INCLUDE (  [CompanyId],
  [SourceTypeID],
  [StockId],
  [GivenPartNumber_Non],
  [PriorityLevel]) 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

então como resolver a questão da lentidão, por favor?

verifiquei o status atualizado, encontrei o tipo de espera como abaixo:

então isso está relacionado ao lento ou não relacionado.

1 respostas

Voted

J.D. · Answer 1 · 2022-03-05T04:48:22+08:00

Seu plano de execução real atualizado também está indicando que você não tem índices em sua extractreports.dbo.alldistSuppliersourceidtabela porque está usando uma operação de Verificação de Tabela com relação a ela.

Deve ter, no mínimo, um índice clusterizado. Se esse índice clusterizado faz sentido estar no campo em que sua consulta está se juntando no momento SourcingNotMappedPartsID(dependendo da frequência com que você consulta por essa coluna), você pode criá-lo assim:

USE extractreports;

CREATE CLUSTERED INDEX IX_alldistSuppliersourceid_ ON dbo.alldistSuppliersourceid (SourcingNotMappedPartsID);

Se SourcingNotMappedPartsIDfor exclusivo extractreports.dbo.alldistSuppliersourceid, você também poderá especificar a palavra- UNIQUEchave após a palavra- CREATEchave.

Caso contrário, se outro campo fizer sentido para ser o índice clusterizado em extractreports.dbo.alldistSuppliersourceid, use o script acima, substituindo essa coluna, para criar o índice clusterizado e você pode criar um índice não clusterizado adicional nele, assim:

USE extractreports;

CREATE NONCLUSTERED INDEX IX_alldistSuppliersourceid_ ON dbo.alldistSuppliersourceid (SourcingNotMappedPartsID);

Novamente, usando a UNIQUEpalavra-chave se for aplicável também.

Esse índice deve pelo menos ajudar seu desempenho até certo ponto.

ao atualizar uma tabela grande, demora muito para atualizar

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

ao atualizar uma tabela grande, demora muito para atualizar

1 respostas

relate perguntas