主页 / dba / 问题 / 176296
Accepted
Kevin3NF
Asked: 2017-06-15 09:48:05 +0800 CST

T-SQL 优化

  • 772

如果答案是“否”，我可以接受......

我正在查看是否可以对其进行任何优化...它是更大的存储过程的一部分。CGCode是 varchar(50)，Year并且Month是smallint,FEIN是char(9)

select max(id)
from Table
where 1=1
    and cgcode = 123
    and datefromparts(cast(year as char(4)),cast(month as char(2)),'01') < getdate()
    and totalcount > 0 
group by cgcode, year, month, fein

实际执行计划的逻辑读取：1,566,473

源表原始数据3200万+条记录

估计行数：640K，实际 55K，在 Group By 开始之前

隐式转换警告Year/Month/CGCode（以 形式出现bigint）

执行时间大约 7.5 秒，执行非聚集索引查找：

在此处输入图像描述

最终结果集是 114 行（为此CGCode我们测试......其他人有所不同）

Prod 中的性能在明显优于 Dev box 的硬件上大致相同。随着时间的推移，这只会变得更糟，因为它会提取早于当前月份的所有内容，以在 UI 中填充历史图表。

我还可以提供哪些其他信息？

粘贴计划

当前使用的索引：

CREATE NONCLUSTERED INDEX [COIX_Table_TotalCount] ON [dbo].[Table]
(
    [TotalCount] ASC
)
INCLUDE (   [ID],
    [CGCode],
    [Year],
    [Month],
    [FEIN]) 
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

CGCode限制数据最多...281 个值。 year只有 3-4 年的数据价值，month当然只有 12 个选项。 TotalCount是bigint, 3200 万条记录中的 17K 个不同的值。不知道TotalCount专栏的目的是什么。

sql-server sql-server-2014

3 个回答

  1. Best Answer

    首先，我会重写这个条件。事实上，它不能有效地使用任何索引。：

    datefromparts(cast(year as char(4)),cast(month as char(2)),'01') < getdate()

    重写它，以便没有函数或计算应用于列：

     ( year < year(getdate()) 
or year = year(getdate()) and month <= month(getdate())
 )

    考虑到这些部分，重写将允许使用索引(cgcode, year, month)- 或者甚至更好。(cgcode, year, month, fein)GROUP BY

    接下来，如果totalcount始终是WHERE子句的一部分或totalcount > 0始终使用特定条件，您可以通过将其添加到INCLUDE部分或使用更有针对性的过滤索引来提高效率：

    -- option A
index (cgcode, year, month, fein, id) 
      include (totalcount)

-- option B
index (cgcode, year, month, fein, id)
      where (totalcount > 0)
    • 8

  2. 我将从 Group By 中删除 cgcode，因为我知道什么是 cgcode 而它不在范围内。因此删除 cgcode (varchar(60)) 将带来巨大的推动力。

    所以如果我是正确的，我的索引也会改变。

    使用变量代替 getdate()

    declare @CurDate date=getdate()
declare @yr int=year(@CurDate ) 
declare @month tinyint=month(@CurDate )
declare @cg varchar(60)='123'

select @cg cgcode,max(id)
from Table
where  cgcode = @cg
    ( year < @yr  
or [year] = @yr and [month] <= @month
 )
    and totalcount > 0 
group by year, month, fein

    从技术上讲，为什么totalcount will be covering indexAI 不理解选项中的原因，根据该逻辑它应该在索引组内或者option B是正确的。

    • 0

  3. 找到了 ：）

    clientid 正在作为 INT 传递...导致 CGCode 字段隐式转换为 varchar。

    select max(id)
from Table
where 1=1
    and cgcode = CAST(123 as varchar(50))  ---this fixed it
    and datefromparts(cast(year as char(4)),cast(month as char(2)),'01') < getdate()
    and totalcount > 0 
group by cgcode, year, month, fein

    从 7-30 秒到 100 毫秒。我会接受的。

    • 0

相关问题