对于 Azure 上运行 ERP 系统的 Microsoft SQL Server 2017 (RTM-CU20)(因此无法在此处更改代码)。
我有一个超过 4200 万行的表。这是一个机器操作统计跟踪表。用户按设备、日期、班次和统计类型(运行时间、吨等)输入运行统计数据。
该表具有这样的主键结构(请不要评论 nchar 而不是 nvarchar 或将日期存储为字符串,这就是 ERP 供应商提供它的方式,而这正是我必须使用的):
equip nchar(12) This is a 12 character equipment number
key_type nchar(1) One of two values E for equipment or G - always E here
shift_seq_no nchar(2) Sequence of shifts (usually 00 or 01)
stat_date nchar(8) Date, YYYYMMDD
stat_type nchar(2) A code for stat type, OH here (operating hours)
trc_seq_no nchar(3) A sequence number for when there's more than one entry per shift
似乎调用代码(基于 Java 的 Web 应用程序使用 JBOSS 对数据库进行 JDBC 调用)查询了在给定日期范围内安排进行预防性维护的设备列表,并寻找最新的统计信息每件设备,因为该行将包含设备的累积统计值。
然后它组合了一个非常长的 SQL 查询,可以有效地执行此操作(将 x 替换为适当的变量:
select * from table
where (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x)
or (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x)
or (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x)
or ( and on and on)
“OR”共有 73 次重复
我根据我所看到的正在运行的内容生成了两个查询。
我只有一组 73 组文字(equip = '000001234567' 和 col = 'value' 等) - 这会立即运行。实际执行计划显示嵌套循环中的聚集索引查找(聚集)和常量扫描,显示 73 次执行 1 行读取和不到 1 秒的运行时间
然后,我采用相同的查询并声明了 428 个变量并将它们插入到查询中,而不是 428 个文字。实际的执行计划在一个步骤中显示了聚集索引扫描(聚集)(执行计划说 SELECT -> 过滤器 -> 聚集索引扫描)。该提示显示在 SSMS 中运行时读取的所有 42,271,100 行,运行时间为 5 分 34 秒。这是一个基于 Web 的应用程序,通过在线应用程序进行调试跟踪需要 8 分钟以上。您可以想象这对于在线屏幕来说是不可接受的。
既然长篇大论已经完成,是否有人对 SQL Server 调整有任何建议,以便使该查询通过变量运行,就像它使用文字运行一样,因为我无法触及代码。
我想我也会把它发回给供应商进行分析,但我希望智囊团的人可能有一个更快的解决方案的想法(因为我的 Google Fu 目前让我失望了)。
我在查询视图时看过几篇关于这种情况的文章,一般的解决方案似乎是“添加 WITH (RECOMPILE) 提示”,但我在这里没有使用视图,而是直接查询表。
在此先感谢您的任何帮助。我主要是 Oracle 人,并且对 SQL Server 有足够的了解,因此很危险。
造成这种情况的根本原因很简单。查询优化器进行行估计以确定它将如何解决查询。行估计基于所涉及列的统计信息。当您提供优化器文字时,它会使用精确值来查看行估计的统计信息。在这种情况下,这些估计会产生一个为您提供搜索的计划,这意味着它发现对于特定值只有少数匹配的行,并且它使用索引来查找这些行。
当您给优化器一个变量时,它无法知道变量中的值是什么。因此,它不是查看特定值的统计数据并基于该值进行行估计,而是使用基于统计数据的整体选择性的平均值。在这种情况下,平均值向优化器表明很多行可能匹配,因此扫描是更有效的检索机制。
我要尝试的第一件事是更新统计数据。它们可能已经过时,并且带有变量的行估计值不准确。要获得最佳统计信息,请使用 FULL SCAN 选项。这将花费更长的时间并使用更多的资源。
接下来,可能将查询更改为存储过程并使用参数而不是局部变量可能会产生更好的计划。这是因为参数值在编译时通过称为参数嗅探的过程已知(对不起,我没有命名)。由于参数嗅探,您可能会看到更一致的行为(尽管有时您会因为参数嗅探而看到不一致的行为)。
接下来,是的,重新编译可能会有所帮助。可能是您的数据过于偏斜,以至于行估计永远不会始终正确。每次执行时都重新编译意味着将生成一个对传递的值唯一的计划。此外,重新编译意味着现在已知变量值并应用嗅探过程。
查询也可能只需要一些重组。很多这样的 OR 带有长长的过滤条件列表,听起来像是一个包罗万象的查询。重新编译可以帮助你,但你也可能更好地重组为嵌套过程(其中 73 个虽然很多),或者使用动态 T-SQL 来构建单个查询而不是长的 OR 语句列表。
您拥有的最后一件事是您使用的是 SQL Server 2017。您可以启用查询存储并使用计划强制以使其使用性能更好的计划。