SQL Server查询调优：新建表插入数据执行过慢问题排查

在SQL Server中执行Update查询时耗时极长，大数据量场景下甚至会停止运行。尝试通过创建带索引的新表，插入数据后再读取，但新表创建的SELECT INTO语句已执行1小时仍未完成，不确定当前建表操作是否正确。同事指出原查询中JOIN时使用collate Arabic_CI_AS是性能瓶颈。

Update UsedTrs set Deleted=b.Deleted from
 UsedTrs a inner join UsedTrs_Live b
 on(a.ItemID=b.ItemID)
 where (a.ArabicDate=b.ArabicDate collate Arabic_CI_AS) and (a.Deleted!=b.Deleted) and
 (a.Code=b.Code) and (a.ItemType=b.ItemType) and 
 (a.ArabicDate>dbo.WesternToArabic(GETDATE()-90))

select [id]
  ,[CreateDate]
  ,[TrsUserID]
  ,[UsedAmount]
  ,[ItemID]
  ,[ItemType]
  ,[Code]
  ,[Deleted]
  ,[ArabicDate]collate Arabic_CI_AS as [ArabicDate]
  ,[CostTypeFlag]
Into UsedTrs_Live_use_for_sp_TrsAmounts
From TrsUsed_Live 
Where ArabicDate> dbo.WesternToArabic(GETDATE()-5)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[WesternToArabic](@indate DATETIME) RETURNS CHAR(10)
AS
BEGIN
    DECLARE
        @OutDate CHAR(10),
        @DD     NUMERIC, 
        @S      INT, 
        @M      INT, 
        @R      INT, 
        @TMP    VARCHAR(10), 
        @tmp1   NUMERIC, 
        @tmp2   NUMERIC

    SET @tmp1 = CONVERT(NUMERIC, DATEADD(yy, 200, DATEADD(hh, -12, @indate)))
    SET @tmp2 = CONVERT(NUMERIC, CAST('18360320' AS DATETIME))
    SET @DD = @tmp1- @tmp2
    SET @S = ( FLOOR(@DD/46751)) * 128  +  1015
    SET @DD =  @Dd - FLOOR(@DD/ 46751) * 46751
    SET @S = ( FLOOR(@DD/12053)) * 33  + @S
    SET @DD =  @DD - FLOOR(@Dd/12053) * 12053
    IF @DD > 1826 
    BEGIN
        SET @DD = @DD - 1826
        SET @S  = @S  +  5  +  FLOOR(@DD /1461) * 4
        SET @DD = @Dd - FLOOR(@Dd/1461) * 1461
    END
    IF @DD > 365
    BEGIN
        SET @DD = @DD - 1
        SET @S = @S  +  FLOOR(@DD / 365)
        SET @DD =  @Dd - FLOOR(@DD/365) * 365
    END
    IF @DD > 185
    BEGIN
        SET @DD = @DD - 186
        SET @R = @Dd - FLOOR(@DD/30) * 30  +  1
        SET @M = FLOOR(@DD/30)  + 7
    END
    ELSE
    BEGIN
        SET @R = @Dd - FLOOR(@DD/31) * 31  +  1
        SET @M = FLOOR(@DD/31)  + 1;
    END
    IF @M < 10
        SET @tmp = CAST(@S AS CHAR(4)) + '/0' + CAST(@M AS CHAR(1))
    ELSE
        SET @tmp = CAST(@S AS CHAR(4)) + '/' + CAST(@M AS CHAR(2))
    IF @R < 10
        SET @outdate = @tmp + '/0'  + CAST(@R AS CHAR(1))
    ELSE
        SET @outdate = @tmp + '/'  + CAST(@R AS CHAR(2))

    RETURN(@OutDate)
END

1. 解决SELECT INTO慢的问题 #

• 预计算过滤条件：标量函数WesternToArabic在WHERE子句中会逐行执行，导致性能暴跌。先计算出截止日期的常量值，再用于过滤：

  DECLARE @CutoffDate CHAR(10) = dbo.WesternToArabic(GETDATE()-5)
  select [id]
    ,[CreateDate]
    ,[TrsUserID]
    ,[UsedAmount]
    ,[ItemID]
    ,[ItemType]
    ,[Code]
    ,[Deleted]
    ,[ArabicDate]collate Arabic_CI_AS as [ArabicDate]
    ,[CostTypeFlag]
  Into UsedTrs_Live_use_for_sp_TrsAmounts
  From TrsUsed_Live 
  Where ArabicDate> @CutoffDate
  

• 检查原表索引：确认TrsUsed_Live表的ArabicDate字段是否有索引。如果没有，临时创建非聚集索引加速过滤：

  CREATE NONCLUSTERED INDEX IX_TrsUsed_Live_ArabicDate ON TrsUsed_Live(ArabicDate)
  

  数据导入完成后可根据需要删除该临时索引。

2. 原Update查询的性能优化 #

• 调整JOIN条件：将过滤条件尽可能放到JOIN ON子句中，减少中间结果集的大小：

  Update a
  set Deleted = b.Deleted
  from UsedTrs a 
  inner join UsedTrs_Live b
      on a.ItemID = b.ItemID
      and a.Code = b.Code
      and a.ItemType = b.ItemType
      and a.ArabicDate collate Arabic_CI_AS = b.ArabicDate
  where a.Deleted != b.Deleted
    and a.ArabicDate > dbo.WesternToArabic(GETDATE()-90)
  

• 创建复合索引：给两张表创建覆盖JOIN和过滤条件的复合索引，避免键查找：

  -- 给UsedTrs表创建索引
  CREATE NONCLUSTERED INDEX IX_UsedTrs_JoinFilter ON UsedTrs(ItemID, Code, ItemType, ArabicDate)
  INCLUDE (Deleted)
  
  -- 给UsedTrs_Live表创建索引
  CREATE NONCLUSTERED INDEX IX_UsedTrs_Live_JoinFilter ON UsedTrs_Live(ItemID, Code, ItemType, ArabicDate)
  INCLUDE (Deleted)
  

3. 新表的索引策略 #

SELECT INTO创建的表没有任何索引，数据导入完成后必须创建必要的索引，示例如下（根据后续查询需求调整）：

-- 聚集索引（通常选择查询最频繁的主键或组合键）
CREATE CLUSTERED INDEX IX_UsedTrs_Live_Use_ItemID ON UsedTrs_Live_use_for_sp_TrsAmounts(ItemID)

-- 覆盖查询的非聚集索引
CREATE NONCLUSTERED INDEX IX_UsedTrs_Live_Use_ArabicDate ON UsedTrs_Live_use_for_sp_TrsAmounts(ArabicDate)
INCLUDE (Code, ItemType, Deleted)

4. 日期转换函数优化 #

标量值函数的性能始终不如内联表值函数（ITVF），建议将WesternToArabic改为内联表值函数版本，调用时使用CROSS APPLY提升性能：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[WesternToArabic_ITVF](@indate DATETIME)
RETURNS TABLE
AS
RETURN
(
    WITH CTE_Dates AS (
        SELECT 
            tmp1 = CONVERT(NUMERIC, DATEADD(yy, 200, DATEADD(hh, -12, @indate))),
            tmp2 = CONVERT(NUMERIC, CAST('18360320' AS DATETIME))
    ),
    CTE_DD AS (
        SELECT DD = tmp1 - tmp2 FROM CTE_Dates
    ),
    CTE_Step1 AS (
        SELECT 
            S = (FLOOR(DD/46751)) * 128 + 1015,
            DD = DD - FLOOR(DD/46751) * 46751
        FROM CTE_DD
    ),
    CTE_Step2 AS (
        SELECT 
            S = (FLOOR(DD/12053)) * 33 + S,
            DD = DD - FLOOR(DD/12053) * 12053
        FROM CTE_Step1
    ),
    CTE_Step3 AS (
        SELECT 
            S = CASE WHEN DD > 1826 THEN S + 5 + FLOOR((DD - 1826)/1461)*4 ELSE S END,
            DD = CASE WHEN DD > 1826 THEN (DD - 1826) - FLOOR((DD - 1826)/1461)*1461 ELSE DD END
        FROM CTE_Step2
    ),
    CTE_Step4 AS (
        SELECT 
            S = CASE WHEN DD > 365 THEN S + FLOOR((DD - 1)/365) ELSE S END,
            DD = CASE WHEN DD > 365 THEN (DD - 1) - FLOOR((DD - 1)/365)*365 ELSE DD END
        FROM CTE_Step3
    ),
    CTE_MonthDay AS (
        SELECT 
            S,
            R = CASE WHEN DD > 185 THEN (DD - 186) - FLOOR((DD - 186)/30)*30 + 1 ELSE DD - FLOOR(DD/31)*31 + 1 END,
            M = CASE WHEN DD > 185 THEN FLOOR((DD - 186)/30) + 7 ELSE FLOOR(DD/31) + 1 END
        FROM CTE_Step4
    ),
    CTE_Tmp AS (
        SELECT 
            S,
            tmp = CAST(S AS CHAR(4)) + '/' + CASE WHEN M < 10 THEN '0' + CAST(M AS CHAR(1)) ELSE CAST(M AS CHAR(2)) END,
            R
        FROM CTE_MonthDay
    )
    SELECT OutDate = tmp + '/' + CASE WHEN R < 10 THEN '0' + CAST(R AS CHAR(1)) ELSE CAST(R AS CHAR(2)) END
    FROM CTE_Tmp
)
GO

调用示例：

DECLARE @CutoffDate CHAR(10)
SELECT @CutoffDate = OutDate FROM dbo.WesternToArabic_ITVF(GETDATE()-5)

内容的提问来源于stack exchange，提问作者Shahab16667