Atualizando linhas em uma tabela grande no sql server

Adicionar um índice SOW_Numberpermitirá que o SQL Server identifique muito rapidamente as linhas que precisam ser atualizadas, sem exigir uma verificação de toda a tabela (supondo que um número relativamente pequeno de linhas corresponda à WHEREcláusula, tornando o índice altamente seletivo ).

CREATE NONCLUSTERED INDEX IX_SOW_Number 
ON dbo.FPP_Temp_Revenue(SOW_Number)
INCLUDE (
        Till_Prev_Inv_Amt_In_USD
        , Cur_Inv_Amt_In_USD
        , YTD_Inv_Amt_In_USD
    );

Mesmo que você tenha SOW_Numberdefinido como parte do índice clusterizado, ele não é a coluna inicial do índice, o que significa que o SQL Server deve verificar todo o índice procurando por linhas que atendam à WHEREcláusula. Adicionar as três colunas listadas na INCLUDEcláusula permitirá que o SQL Server não execute uma pesquisa no índice clusterizado para esses valores, o que, de outra forma, seria necessário fazer, pois são usados ​​nos cálculos dentro da SETcláusula.

Eu normalmente não sugeriria adicionar um índice como este sem avaliar cuidadosamente a carga de trabalho; no entanto, estou assumindo que você não tem nenhum índice além do índice clusterizado que você identifica em seu script. Adicionar um único índice nesse caso provavelmente fará uma grande diferença no desempenho de sua atualização, sem afetar drasticamente outras partes de sua carga de trabalho.

Configurei uma mesa de teste no meu laptop para poder testar o desempenho de adicionar o índice sugerido versus não ter nenhum índice no SOW_Number.

Primeiro, criei um novo banco de dados TestDBpara este teste (estou usando o SQL Server no Linux RC3):

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON (NAME = 'TestDB_Primary', FILENAME = '/var/opt/mssql/data/TestDB_Primary.mdf', SIZE = 5GB, MAXSIZE = 10GB, FILEGROWTH = 1GB)
LOG ON (NAME = 'TestDB_Log', FILENAME = '/var/opt/mssql/log/TestDB_Log.ldf', SIZE = 5GB, MAXSIZE = 10GB, FILEGROWTH = 1GB);
GO

USE TestDB;
CREATE TABLE dbo.FPP_Invoice_Revenue(
         Project_Code varchar(10) NOT NULL
       , Project_Desc varchar(50) NULL
       , SOW_Number varchar(10) NOT NULL
       , SOW_Desc varchar(50) NULL
       , Invoice_No varchar(50) NOT NULL
       , Inv_Month int NOT NULL
       , Inv_Year int NOT NULL
       , Billing_Date smalldatetime NULL
       , Doc_Currency varchar(10) NULL
       , Vertical varchar(255) NULL
       , Till_Prev_Inv_Amt numeric(24, 10) NULL
       , Cur_Inv_Amt numeric(24, 10) NULL
       , YTD_Inv_Amt numeric(24, 10) NULL
       , Till_Prev_Inv_Amt_In_USD numeric(24, 10) NULL
       , Cur_Inv_Amt_In_USD numeric(24, 10) NULL
       , YTD_Inv_Amt_In_USD numeric(24, 10) NULL
CONSTRAINT PK_FPP_Invoice_Revenue PRIMARY KEY CLUSTERED 
(
    Project_Code ASC
    , SOW_Number ASC
    , Invoice_No ASC
    , Inv_Month ASC
    , Inv_Year ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,       
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY];
GO

Em seguida, inseri 10.000.000 de linhas para ter uma tabela suficientemente grande. Eu sei, isso é um pouco menor que sua tabela, mas fornece dados suficientes para fazer extropolações razoáveis:

TRUNCATE TABLE dbo.FPP_Invoice_Revenue;
;WITH v AS (
       SELECT v.num
       FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.FPP_Invoice_Revenue
SELECT Project_Code = CONVERT(varchar(10), ROW_NUMBER() OVER (ORDER BY v1.num, v2.num))
       , Project_Desc = CONVERT(varchar(50), CRYPT_GEN_RANDOM(50))
       , SOW_Number = v5.num /* only 10 distinct values */
       , SOW_Desc = CONVERT(varchar(50), CRYPT_GEN_RANDOM(50))
       , Invoice_No = CONVERT(varchar(50), CRYPT_GEN_RANDOM(50))
       , Inv_Month = ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 12
       , Inv_Year = 2009 + ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 8
       , Billing_Date = DATEFROMPARTS(2009 + ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 8, 1, (ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 12) + 1)
       , Doc_Currency = CONVERT(varchar(10), CRYPT_GEN_RANDOM(10))
       , Vertical = CONVERT(varchar(255), CRYPT_GEN_RANDOM(255))
       , Till_Prev_Inv_Amt = CONVERT(numeric(24, 10), 1)
       , Cur_Inv_Amt = CONVERT(numeric(24, 10), 2)
       , YTD_Inv_Amt = CONVERT(numeric(24, 10), 3)
       , Till_Prev_Inv_Amt_USD = CONVERT(numeric(24, 10), 4)
       , Cur_Inv_Amt_In_USD = CONVERT(numeric(24, 10), 5)
       , YTD_Inv_Amt_In_USD = CONVERT(numeric(24, 10), 6)
FROM v v1 --10 rows
       CROSS JOIN v v2 --100 rows
       CROSS JOIN v v3 --1000 rows
       CROSS JOIN v v4 --10000 rows
       CROSS JOIN v v5 --100000 rows
       CROSS JOIN v v6 --1000000 rows
       CROSS JOIN v v7;--10000000 rows

Em seguida, crio o índice não clusterizado que sugeri acima:

CREATE NONCLUSTERED INDEX IX_SOW_Number
ON dbo.FPP_Invoice_Revenue(SOW_Number)
INCLUDE (Till_Prev_Inv_Amt, Cur_Inv_Amt, YTD_Inv_Amt);

Isso levou cerca de 1:20 para criar no meu laptop.

Agora, a consulta:

UPDATE dbo.FPP_Invoice_Revenue
SET Till_Prev_Inv_Amt_In_USD = Till_Prev_Inv_Amt / 0.0285
    , Cur_Inv_Amt_In_USD = Cur_Inv_Amt / 0.0285
    , YTD_Inv_Amt_In_USD = YTD_Inv_Amt / 0.0285
WHERE SOW_Number = '6'; 

Os dados de teste são distribuídos uniformemente em 10 SOW_Numbervalores; WHERE SOW_Number = '6'indica que atualizaremos 1.000.000 de linhas:

(1.000.000 linhas afetadas)

O plano de execução real para a consulta de atualização:

As estatísticas de execução para isso:

Tempo de análise e compilação do SQL Server: tempo
de CPU = 0 ms, tempo decorrido = 0 ms.
Tabela 'FPP_Invoice_Revenue'. Contagem de varredura 1, leituras lógicas 6567261, leituras físicas 8920, leituras antecipadas 209603, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

(1.000.000 linhas afetadas)

Tempos de execução do SQL Server:
tempo de CPU = 9376 ms, tempo decorrido = 13689 ms.

Como você pode ver acima, a consulta levou apenas 14 segundos para atualizar 1.000.000 de linhas. Se SOW_Numberfor mais seletivo, o tempo necessário será proporcionalmente menor.

Como você identificou em sua pergunta, descartar o índice clusterizado não ajuda a aumentar o desempenho da consulta de atualização. Entenda que todos os dados em uma tabela que possui um índice clusterizado são de fato armazenados no índice clusterizado. Descartar e recriar o índice clusterizado fará com que todas as 83 milhões de linhas sejam gravadas no log enquanto os dados são movidos para dentro e para fora do índice clusterizado. Isso é muito E/S extra que não é necessário para que a atualização seja bem-sucedida.

Você SOW_Numberdefiniu como um varchar(10); se o conteúdo dessa coluna sempre tiver na verdade 10 caracteres, você pode considerar modificá-lo para char(10)em vez de varchar(10), pois isso é realmente mais eficiente. Considere isso para todas as varcharcolunas que você tem. Além disso, Verticalrealmente precisa ter 255 caracteres? Presumivelmente, Inv_Monthe Inv_Yearna verdade não são obrigados a armazenar até 2.147.483.647? Você provavelmente poderia converter essas colunas smallinte economizar 4 bytes por linha. Tendo em mente que as colunas na chave de índice clusterizado são usadas (duplicadas) em cada índice não clusterizado, a economia de espaço com o dimensionamento correto dos dados pode ser substancial.

Em vez de atualizar, e provavelmente ter que continuar atualizando essa tabela, por que não criar colunas computadas uma vez?

São cálculos simples e você pode evitar usar um gatilho no caminho.

ALTER TABLE dbo.FPP_Invoice_Revenue
ADD Till_Prev_Inv_Amt_Comp AS Till_Prev_Inv_Amt_In_USD / 0.0285714286

ALTER TABLE dbo.FPP_Invoice_Revenue
ADD Cur_Inv_Amt_Comp AS Cur_Inv_Amt_In_USD / 0.0285714286

ALTER TABLE dbo.FPP_Invoice_Revenue
ADD YTD_Inv_Amt_Comp AS YTD_Inv_Amt_In_USD / 0.0285714286

Você pode decidir se deseja persistir e/ou indexá-los, e quaisquer consultas que atualmente usam cálculos que correspondam à definição de coluna computada devem usar as colunas computadas sem alterar suas consultas.

Então, algo assim também se beneficiaria:

SELECT 1/0 
FROM  dbo.FPP_Invoice_Revenue
WHERE Till_Prev_Inv_Amt_In_USD / 0.0285714286 = 1.

É estranho para mim que não esteja usando uma varredura de índice, pois [SOW_Number] faz parte do PK. De acordo com um comentário, uma varredura de índice seria relatada como uma varredura de tabela, pois é um índice clusterizado.

Poderia adicionar um índice em [SOW_Number] como sugerido por Max (+1).

Colocar [SOW_Number] primeiro no PK pode ajudar, mas fazer isso na tabela com 83.423.460 linhas não é algo que eu gostaria de fazer.

 UPDATE FPP_Temp_Revenue
 SET    Till_Prev_Inv_Amt = Till_Prev_Inv_Amt_In_USD / 0.0285714286,
        Cur_Inv_Amt       = Cur_Inv_Amt_In_USD       / 0.0285714286,
        YTD_Inv_Amt       = YTD_Inv_Amt_In_USD       / 0.0285714286
 WHERE  SOW_Number = '20014378'

Você não está alterando nenhum valor de índice, portanto, desabilitar esse índice não terá efeito

CONSTRAINT [PK_FPP_Invoice_Revenue] PRIMARY KEY CLUSTERED 
(
[Project_Code] ASC,
[SOW_Number] ASC,
[Invoice_No] ASC,
[Inv_Month] ASC,
[Inv_Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,       
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

8 minutos não é tão ruim. Essa é uma consulta que você precisa executar com frequência? Nesse caso, uma visão pode ser mais eficaz.