在sql server中更新大表中的行

添加索引SOW_Number将使 SQL Server 可以非常快速地识别需要更新的行，而无需扫描整个表（假设相对少量的行匹配该WHERE子句，从而使索引具有高度选择性）。

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
    );

即使您已SOW_Number定义为聚集索引的一部分，它也不是索引的前导列，这意味着 SQL Server 必须扫描整个索引以查找满足该WHERE子句的行。添加子句中列出的三列INCLUDE将允许 SQL Server 不对这些值执行对聚集索引的查找，否则它需要在SET子句内的计算中使用它们时执行查找。

如果不仔细评估工作量，我通常不会建议添加这样的索引；但是，我假设您除了在脚本中标识的聚集索引之外没有任何索引。在这种情况下添加单个索引很可能会对更新的性能产生非常大的影响，而不会显着影响工作负载的其他部分。

我在我的笔记本电脑上设置了一个测试平台，这样我就可以测试添加建议索引与没有索引的性能SOW_Number。

首先，我TestDB为此测试创建了一个新数据库（我在 Linux RC3 上使用 SQL Server）：

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

接下来，我插入了 10,000,000 行以获得足够大的表。我知道，这比你的表小一点，但它提供了足够的数据来进行合理的推断：

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

接下来，我创建上面建议的非聚集索引：

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

这花了大约 1:20 在我的笔记本电脑上创建。

现在，查询：

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'; 

测试数据均匀分布在 10 个SOW_Number值上；WHERE SOW_Number = '6'表示我们将更新 1,000,000 行：

（1000000 行受影响）

更新查询的实际执行计划：

执行统计：

SQL Server 解析和编译时间：
CPU 时间 = 0 毫秒，运行时间 = 0 毫秒。
表“FPP_Invoice_Revenue”。扫描计数 1，逻辑读取 6567261，物理读取 8920，预读读取 209603，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

（1000000 行受影响）

SQL Server 执行时间：
CPU 时间 = 9376 毫秒，经过时间 = 13689 毫秒。

从上面可以看出，查询只用了大约 14 秒就更新了 1,000,000 行。如果SOW_Number更具选择性，则所需的时间将成比例地减少。

正如您在问题中所确定的，删除聚集索引无助于提高更新查询的性能。了解具有聚集索引的表中的所有数据实际上都存储在聚集索引中。删除并重新创建聚集索引将导致所有 8300 万行都写入日志，同时将数据移入和移出聚集索引。这是更新成功所不需要的大量额外 I/O。

您已SOW_Number定义为varchar(10); 如果该列的内容实际上总是 10 个字符长，您可能会考虑将其修改为 achar(10)而不是varchar(10)，因为这实际上更有效。varchar为您拥有的所有列考虑这一点。此外，Vertical实际上是否需要 255 个字符长？据推测，Inv_Month实际上Inv_Year不需要存储最多 2,147,483,647？您可能会将这些列转换为smallint每行并节省 4 个字节。请记住，在每个非聚集索引中都使用（重复）聚集索引键中的列，通过调整数据大小可以节省大量空间。

与其更新，而且很可能必须不断更新此表，为什么不创建一次计算列呢？

它们是简单的计算，您可以避免使用触发器。

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

您可以决定是否对它们进行持久化和/或索引，并且当前使用与计算列定义匹配的计算的任何查询都应使用计算列而不更改您的查询。

所以这样的事情也会受益：

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

我很奇怪它没有使用和索引扫描，因为 [SOW_Number] 是 PK 的一部分。根据评论，索引扫描将被报告为表扫描，因为它是聚集索引。

可以按照 Max (+1) 的建议在 [SOW_Number] 上添加索引。

在 PK 中将 [SOW_Number] 放在首位可能会有所帮助，但对包含 83,423,460 行的表格执行此操作并不是我愿意参与的。

 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'

您没有更改任何索引值，因此禁用该索引将无效

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分钟还不错。这是您需要经常运行的查询吗？如果是这样，视图可能会更有效。