时间戳

首页 > 解决方案 > 使用函数 fn_decompress 选择的缓慢性能

sql - 使用函数 fn_decompress 选择的缓慢性能

问题描述

SQL Server 查询结果在 40 GB 数据库上非常慢。fn_decompress可能为每个 LIKE 语句的解压缩数据调用函数（这对性能不利）。

我的想法是每行只调用一次数据解压缩函数，然后将值保存到变量@decompData并进行比较。

SELECT  ID, CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) AS Data
FROM     dbo.ApplLogG2MessagesDataXML
WHERE  (CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%alfa@gmail.com%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%mitcherl@better.com%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%mismatch@woobie.net%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%kopii@lerhard.info%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%metablock@gmail.com%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%peter@och.fr%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%info@lepit.de%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%nimrod@gmail.com%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%janine.boecher@gmail.cz%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%lipican@ninestor.eu%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%lejby.fejby@auto.it%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%kamil@senemil.cz%' OR
            CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) LIKE '%mikahekinen@word.buz%')

我对更好性能的想法是：

    SELECT  ID, CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) AS Data
        FROM     dbo.ApplLogG2MessagesDataXML
        WHERE 
@decompData =  CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX))
@decompData LIKE '%alfa@gmail.com%' OR 
@decompData LIKE '%mitcherl@better.com%' OR
@decompData LIKE '%mismatch@woobie.net%' OR 
....next emails conditions LIKE

标签： sqlsql-serverwhere-clause

解决方案

关键是你使用了CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX))14 次。只需在子选择中声明一次，并在 where 条件中使用别名。

像那样：

SELECT * FROM (
    SELECT  ID, CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX)) AS Data
    FROM dbo.ApplLogG2MessagesDataXML
) as innerTable
WHERE  (
    `Data` LIKE '%alfa@gmail.com%' OR
    `Data` LIKE '%mitcherl@better.com%' OR
    `Data` LIKE '%mismatch@woobie.net%' OR
    `Data` LIKE '%kopii@lerhard.info%' OR
    `Data` LIKE '%metablock@gmail.com%' OR
    `Data` LIKE '%peter@och.fr%' OR
    `Data` LIKE '%info@lepit.de%' OR
    `Data` LIKE '%nimrod@gmail.com%' OR
    `Data` LIKE '%janine.boecher@gmail.cz%' OR
    `Data` LIKE '%lipican@ninestor.eu%' OR
    `Data` LIKE '%lejby.fejby@auto.it%' OR
    `Data` LIKE '%kamil@senemil.cz%' OR
    `Data` LIKE '%mikahekinen@word.buz%'
)

您使用 14 次导致的问题CAST(dbo.fn_decompress(dbo.ApplLogG2MessagesDataXML.Data) AS nvarchar(MAX))是您重新计算了 14 次相同的数据。所以你的系统需要计算你的 40GB 的 14 倍。这不是真正的性能。

推荐阅读