主页 / dba / 问题 / 232698
Accepted
Learning_DBAdmin
Asked: 2019-03-22 01:11:23 +0800 CST

性能改进在生产中出错，在测试中运行良好

  • 772

我们有一个供应商支持的应用程序，并且有一段代码在过程中进行非常繁重的逻辑读取和耗时，因此我建议他们稍微调整查询以减少 IO 和时间，它在在数据和性能方面进行测试，但是它在生产中失败并返回不同的数据，我们不得不回滚更改。需要您的专家建议。

这已经在测试环境中对数据进行了 3 个多月的测试，我们从未遇到任何数据问题。部署后立即开始在生产中出现少量故障，并产生不一致的数据。

现有查询：

SELECT @Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10)) 
FROM table_name WITH(NOLOCK) 
WHERE s_mode='value'

建议查询：

SELECT @Ref = ref_clnt+1 FROM table_name WITH(NOLOCK) 
WHERE RefNo = (SELECT MAX(RefNo) FROM table_name WHERE s_mode = 'value')

表的DDL如下：

CREATE TABLE [dbo].[table_name](
    [RefNo] [dbo].[udt_RefNo] NOT NULL,
    [S_Mode] [varchar](10) NOT NULL,
    [ref_clnt] [varchar](50) NULL)
CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED 
(
    [RefNo] ASC
)

仅提供查询中使用的定义中的那些列。

Udt_RefNo是用户定义的数据类型：

CREATE TYPE [dbo].[udt_RefNo] FROM [char](16) NOT NULL
GO

SQL Server 版本：Microsoft SQL Server 2014 (SP3) 版权所有 (c) Microsoft Corporation 企业版（64 位）。

非聚集索引覆盖列如下图：

CREATE NONCLUSTERED INDEX [ncidx_table_name_1] ON [dbo].[table_name]
(
    [S_Mode] ASC,
    [S_Status] ASC
)
INCLUDE (   [ref_clnt])

请按要求查找查询计划：

执行计划 开启统计IO和时间后的Reads Number of Reads对比：

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 2732, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 157 ms,  elapsed time = 161 ms.

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

供应商已返回建议作为

SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'

问题是——如何测试它们，因为它们似乎都适用于大多数情况，但仅在少数情况下失败。我不太了解该应用程序及其供应商支持的应用程序，因此无法获得太多细节，例如业务逻辑如何为底层过程工作。

sql-server sql-server-2014

2 个回答

  1. Best Answer

    您的重写与原始查询不具有相同的语义，因此不同的结果并不奇怪。

    这是真的，无需担心NOLOCK提示，即使（以某种方式）保证最高ref_clnt值与具有最高RefNo值的行相关联。请参阅此 db<>fiddle 示例。

    与计算机打交道时，精度很重要，因此您需要仔细考虑数据类型和边缘情况。最大RefNo计算将使用字符串排序语义，因此 '999' 的排序高于 '1000'。查询之间还有其他几个重要的区别。我不打算列出所有这些，但NULL处理是另一个例子。

    两个版本的代码中也存在许多错误。ref_clnt如果返回任何 -1000000000 或更低的值，原始文件将失败，因为它不适合varchar(10). 符号使长度为 11。

    安全改进原始版本代码的最简单方法是在计算列上添加索引：

    ALTER TABLE dbo.table_name 
ADD ref_cc AS 
    ISNULL(CAST(ref_clnt AS integer), 0);

CREATE NONCLUSTERED INDEX i 
ON dbo.table_name (S_Mode, ref_cc);

    数据库<>小提琴演示

    然后执行计划可以直接查找ref_clnt给定值的最高行（按整数排序）S_Mode：

    执行计划

    供应商的原始 SQL 的质量可能仍然值得商榷，但至少它会运行得更快并产生相同的结果。

    供应商的新建议：

    SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'

    ...仍然有问题，至少在理论上是这样，因为ISNULL使用第一个参数的数据类型，所以整数文字0被隐式转换为varchar(50).

    如果不为NULL，则返回check_expression的值；否则，如果类型不同， replacement_value在隐式转换为check_expression的类型后返回。如果replacement_value比check_expression长，则可以截断replacement_value。

    仍然对MAX字符串进行操作，这可能会产生意想不到的结果。在任何情况下，如果没有（不同的）计算列索引，表达式仍然不可搜索。

    • 7

  2. 我猜@Ref是varchar(10)。Ref_clnt是varchar(50)，你正在向它添加一个。

    SELECT @Ref = ref_clnt+1

    ...将生成隐式转换。

    我已将此问题作为警告显示在执行计划中，它影响了我们应用程序的性能。我见过的大多数情况都是这样使用 coalesce 语句的：

    coalesce(field_name, 0) = 0

    应该是什么时候：

    coalesce(field_name, '0') = '0' or coalesce(field_name, '') = ''

    ...因为field_name是varchar。

    • 0

相关问题