代码之家 › 专栏 › 技术社区 › Mitesh Jain

创建替换字符串查询的函数会减慢存储过程的速度

tsql sql-server

Mitesh Jain · 技术社区 · 6 年前

我创建了以下存储过程:

CREATE PROCEDURE [dbo].[GetYearsByMake] 
    @Make VARCHAR(50)
AS
BEGIN
   SELECT DISTINCT [year] 
   FROM make_model 
   WHERE active = 1 
     AND isUnique = 1 
     AND [dbo].[ReplaceUrlEscapeChars](make) = @Make 
   ORDER BY [year] DESC
END

replaceurlescapechars是我创建的函数,用于将包含特殊字符的数据替换为single-

CREATE FUNCTION [dbo].[ReplaceUrlEscapeChars] 
    (@MyString AS VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    IF (@MyString LIKE '%[^0-9a-zA-Z\s-]%')
    BEGIN
        SET @MyString = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
            (Replace(Replace(Replace(Replace(@MyString,'<','-'),'>','-'),'#','-'),'{','-'),'}','-'),'|','-'),'\','-'),'^','-'),'~','-'),'[','-'),']','-'),';','-')
            ,':','-'),'@','-'),'&','-'),'$','-'),'/','-'),'.','-');
    END

    SET @MyString = LTRIM(RTRIM(@MyString));
    RETURN @MyString
END

但是当我用上面的函数替换查询中的每个字符串时,检索数据的查询速度很慢。

我如何优化这个?

有没有其他快速获取数据的方法?

2 回复 | 直到 6 年前

Alan Burstein 6 年前

@lad2025发布的是一条路。也就是说,在计算/持久化列不是一个选项的情况下,将标量udf转换为内联表值函数(itvf)会使您受益。itvf的性能比用户定义的标量函数(标量udf)好得多,原因有很多,包括它们不像标量函数那样杀死并行性。

您可以将标量udf重写为itvf,如下所示:

CREATE FUNCTION dbo.itvfReplaceUrlEscapeChars(@MyString as varchar(Max))
RETURNS TABLE AS RETURN
SELECT 
  CASE WHEN @MyString LIKE '%[^0-9a-zA-Z\s-]%' 
       THEN LTRIM(RTRIM(
            Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
            Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
            @MyString,'<','-'),'>','-'),'#','-'),'{','-'),'}','-'),'|','-'),'\','-')
            ,'^','-'),'~','-'),'[','-'),']','-'),';','-'),':','-'),'@','-'),'&','-')
            ,'$','-'),'/','-'),'.','-')))
       ELSE LTRIM(RTRIM(@MyString)) 
  END AS cleanedString;
GO

你可以这样称呼它:

CREATE PROC dbo.GetYearsByMake @Make VARCHAR(50)
AS
BEGIN
  SELECT DISTINCT [year] 
  FROM make_model
  CROSS APPLY dbo.itvfReplaceUrlEscapeChars(make)
  WHERE active=1 AND isUnique=1 
  AND cleanedString = @Make 
  ORDER BY [year] DESC;
END

值得注意的是,使用varchar(max)数据类型非常昂贵。如果您可以使用varchar(8000)或更低的版本,那么您也将看到巨大的性能提升。在make_模型上使用类似这样的过滤非聚集索引可能也会有帮助:

CREATE NONCLUSTERED INDEX nc_make_model_poc ON make_model(year)
WHERE active = 1 AND isUnique = 1;

Lukasz Szozda 6 年前

而不是自己的函数和嵌套多个 REPLACE 我会加入 TRANSLATE - SQL Server 2017+ :

CREATE PROCEDURE  [dbo].[GetYearsByMake] @Make VARCHAR(50)
AS
BEGIN
  SELECT DISTINCT [year] 
  FROM make_model
  WHERE active=1 AND isUnique=1 
    AND TRANSLATE(make, '<>#{}|','------') = @Make 
  ORDER BY [year] DESC;
END

DBFiddle Demo

更好的方法是添加生成的持久化列 normalized_make 在上面创建索引。

ALTER TABLE make_model
ADD normalized_make AS 
  (CAST(TRIM(TRANSLATE(make, '<>#{}|','------')) AS VARCHAR(100))) PERSISTED;

CREATE INDEX indx_name ON make_model(normalized_make);

SELECT *
FROM make_model
WHERE normalized_make = @Make;

编辑:

ALTER TABLE make_model
ADD normalized_make AS 
  (CAST(RTRIM(LTRIM(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
            (Replace(Replace(Replace(Replace(Make,'<','-'),'>','-'),'#','-'),'{','-'),'}','-'),'|','-'),'\','-'),'^','-'),'~','-'),'[','-'),']','-'),';','-')
            ,':','-'),'@','-'),'&','-'),'$','-'),'/','-'),'.','-'))) AS VARCHAR(100))) PERSISTED;

DBFiddle Demo2