主页 / dba / 问题 / 148074
Accepted
mpag
Asked: 2016-08-27 16:49:29 +0800 CST

带有 CTE 的 tSQL 存储过程；WHERE 中的 CASE 语句运行缓慢

  • 772

我有一个用户定义的存储过程当调用而不传递显式值时，我只想传递所有位置 (nvarchar(50))，这是表的主键字段：Monitor_Locations（约 850 个条目）

SP 的一部分定义如下（截取）。

ALTER PROCEDURE [dbo].[dev_Tech@Locs2b] ( --CREATE or ALTER
     @Locations as nvarchar(MAX) = NULL -- = 'GG1,BenBr14,BenBr00,YB_ToeDrain_Base'
    ,@rangeStart as DateTime = '1970-01-01'
    ,@rangeEnd as DateTime = '2099-12-31'
) AS BEGIN
SET NOCOUNT ON; --otherwise concrete5 chokes for multi-table returns.
DECLARE @loclist as TABLE (
    Location nvarchar(50) PRIMARY KEY
)
IF @Locations is NULL
    INSERT INTO @loclist(Location)
        SELECT Location from Monitor_Locations order by Location
ELSE --irrelevant for this question
    INSERT INTO @loclist(Location)
        SELECT
            ML.Location
        FROM Monitor_Locations as ML join
            tvf_splitstring(@Locations) as ss ON 
                ML.Location=ss.Item OR 
                ML.Location like ss.Item+'[_]%'
        ORDER BY ML.Location;
With Deploys as (
    SELECT
        D.Location,
        MIN(D.Start) as Start,
        MAX(D.[Stop]) as Stop
    FROM
        Deployments as D 
    WHERE 
        D.Stop is not NULL
)

...做一些其他的事情...

为了在将受限站点列表发送到 SP 时提高存储过程的速度，我想将 WHERE 子句替换为

WHERE 
    CASE
        WHEN D.Stop IS NULL THEN 0
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --ELSE NULL which is not 1
    END=1

但是 SP 曾经需要 6-8 秒来执行，现在需要 2.5 分钟（对于没有限制列表的调用）。我认为完整列表的每种方式花费的时间大致相同，因为 CASE 的第二个子句应该很快被触发，而第三个子句永远不会被检查。

发生什么了？这段代码：

WHERE 
    CASE
        WHEN D.Stop IS NULL THEN NULL
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --else null
    END is not null

这个计划需要大约 10 分钟的运行时间：

案例地点

对比这里的WHERE D.Stop is not NULL计划（6s）： 标准地点

有一次，这个 SP 在这个版本中花费了 1 秒，但是通过更改 SP 然后再变回去，又花了 6 秒。如答案中所述，这可能是由于参数嗅探。

运行时间

我的目标执行时间少于 2 秒，因为这将是 Web 应用程序上频繁执行的 SP，它使用它来填充和限制其他用户选择。基本上，我不希望这是一个明显的瓶颈。初始运行时间大约为 3 分钟，但在添加或更改一些索引后，该时间下降到 6-8 秒范围。

星期一 (2016-08-29)，在进行重大更改之前 没有输入参数的简单 WHERE：5 秒带有 rangeStart 和 rangeEnd 的简单 WHERE：4 秒带有 @Locations 设置为 7 元素 CSV 变量的简单 WHERE CASEd WHERE：最多 10 分钟

重新处理 CLR 函数后（见下面我的回答）星期二（2016 年 8 月 30 日）没有输入参数的简单或 CASEd WHERE 或带有 rangeStart 和 rangeEnd 的简单或 CASEd WHERE：3s 简单或 CASEd WHERE 有 7 个元素 @Locations：0 -1s

将表变量@loclist 迁移到临时表#loclist 后所有测试的 WHEREs/参数：0-1s

t-sql sql-server-2008-r2

3 个回答

  1. 两大性能问题：

    1. 您的 CSV 拆分器功能是一个主要的性能杀手。将其替换为 Jeff Moden 的 DelimitedSplit8k 函数。你可以在这里阅读所有相关内容。或者更好的是，如果您使用的是 2008+，请将其换成 CLR 函数或表值参数。查看 Aaron Bertrand对各种 CSV 拆分器函数的性能测试。CLR 是整体赢家。
    2. 表变量可能是一个很大的性能杀手，即使它只有 1 行。将其切换为临时表并为其添加聚簇索引。

    在您现在提供的执行计划中，该函数显示 0% 的成本，但事实并非如此。函数成本较高，但您不会在执行计划中看到实际成本，除非它是内联表值函数。

    不幸的是，虽然我曾经有 1 秒的运行时间，但通过更改 SP 然后再返回，它又需要 6 秒。

    闻起来有参数嗅探的味道。

    • 4

  2. 在此处输入图像描述

    因为你分享了图片，所以我无法详细说明你的问题。我标记了两个执行计划之间的主要区别。

    在第一个计划中，SQL Server 为查询创建了执行计划并使用了非聚集索引查找，但在第二个计划中它使用了索引扫描。这是增加总执行时间的主要原因。

    索引查找：- 仅触及符合条件的行和包含这些符合条件的行的页面。

    索引扫描：- 接触表/索引中的每一行，无论它是否合格。

    当您在 where 条件下操作数据（列）（使用函数或 case 语句）时，forst SQL Server 扫描完整的索引/表，然后执行操作数据并匹配您的条件。此过程会增加内存利用率、磁盘 IO 并增加执行时间。

    包括 Tara Kizer 的建议，我建议

    1. 在表中添加一列，并根据您在 WHERE 条件中使用的逻辑在用于将数据插入表中的 SP 中进行更改，并使用 CASE 语句在其上创建索引。它会解决你的问题。

    或者

    1. 提取临时表中的所有记录，在其上创建索引并将数据插入其中，包括 CASE 语句并从临时表中检索数据。

      CREATE TABLE #TEMP
(
Location DataType(LENGTH), --Estimated Length
Start DataType(LENGTH),
STOP DataType(LENGTH),
WCondition DataType(LENGTH)
);
CREATE NONCLUSTERED INDEX IDXTMP ON #TEMP(WCONDITION);

INSERT INTO #TEMP
SELECT
        D.Location,
        MIN(D.Start) as Start,
        MAX(D.[Stop]) as Stop,
        CASE
        WHEN D.Stop IS NULL THEN 0
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 END AS Wcondition
    FROM
        Deployments as D

SELECT * FROM #TEMP WHERE W Condition --Put Condition

      谢谢

    • 1
  3. Best Answer

    概括

    在实施所提供答案中的一些建议后，无论使用的参数值如何，整个 SP 似乎都在 0-1 秒内执行。非常感谢所有提供帮助的人。

    如果这似乎会在将来影响性能（或将此结果绑定到另一个表），我将研究 Rajesh 关于在临时表中存储“条件”值的建议。

    尚未解决的问题

    我不确定为什么它使用聚集索引扫描而不是寻找以下内容：

    WHERE 
    CASE
        WHEN D.Stop IS NULL THEN NULL
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN Location IN (SELECT Location from #loclist) THEN 1 --does clustered index scan
--alternate: EXISTS (SELECT 1 from (SELECT Location from #loclist as l where l.Location=D.Location) as ll) THEN 1 --does clustered index scan
    END is not null

    然而，这确实是一个寻求

    WHERE 
    D.Stop is not NULL AND 
      EXISTS (SELECT 1 from (SELECT Location from #loclist as l where l.Location=D.Location) as ll).

    此外，在周末，我正在研究全文索引是否对我的LIKE x+'[_]%'加入有益，但我无法弄清楚默认的分词器是什么（'_'语言 1033 是否将单词标记分开？或者只是真正的空白字符？）和我似乎没有安装全文索引（SELECT * from sys.fulltext_languages返回一个空的结果集，就像那样EXEC sp_help_fulltext_system_components）。由于我没有安装媒体，我需要等待 IT 重新安装 SQL Server 2008 R2 以添加全文功能，这甚至可能对我没有好处。

    但是，正如我所说，整个混乱需要 0-1 秒才能执行，所以我现在很满意。

    整个存储过程

    ALTER PROCEDURE [dbo].[dev_Tech@Locs2b] ( --CREATE or ALTER
     @Locations as nvarchar(MAX) = NULL -- = 'GG1,BenBr14,BenBr00,YB_ToeDrain_Base,SR_AbvTisdale_E1,GG5,Elephant'
    ,@rangeStart as DateTime = '1970-01-01'
    ,@rangeEnd as DateTime = '2099-12-31'
) AS BEGIN
SET NOCOUNT ON; --otherwise concrete5 chokes for multi-table returns.
CREATE TABLE #loclist (
    Location nvarchar(50) PRIMARY KEY CLUSTERED
)
IF @Locations is NULL
    INSERT INTO #loclist(Location)
        SELECT Location from Monitor_Locations-- order by Location
ELSE 
    INSERT INTO #loclist(Location)
        SELECT
            ML.Location
        FROM Monitor_Locations as ML join
                clr_splitString_delim(@Locations,',') as ss ON
                ML.Location=ss.substr OR 
                ML.Location like ss.substr+'[_]%'
--      ORDER BY ML.Location
        ;
With subsitelist as (
    SELECT
        ML.Location as intxt,
        CASE
            WHEN UPPER(t.substr) NOT IN ('RT','180RT','RT180','180','J','JST','JSAT','JSATS') 
                THEN LEFT(ML.Location,MAX(t.sEnd)) -- or MAX(t.leng+t.pos?) --look at 1167
                ELSE LEFT(ML.Location,COALESCE(MIN(t.sEnd-1),1))
        END as baseTxt,
        case WHEN UPPER(t.substr) IN ('RT','180RT','RT180') THEN '_'+t.substr END as sRT, --ELSE NULL
        case WHEN UPPER(t.substr) IN ('180','180RT','RT180') THEN '_'+t.substr END as s180,
        case WHEN UPPER(t.substr) IN ('J','JST','JSAT','JSATS') THEN '_'+t.substr END as sJ
    from 
        #loclist /*Monitor_Locations*/ as ML CROSS APPLY 
        clr_splitString_delim(ML.Location, '_') as t
    group by 
        ML.Location, t.substr
),  deploys as (
    SELECT
        D.Location,
        MIN(D.Start) as Start,
        MAX(D.[Stop]) as Stop
    FROM
        Deployments as D 
    WHERE 
-- tSQL does not use traditional short-circiting in a WHERE clause with ANDs or ORs, so no guarantee that the join to the larger list won't happen when Stop is set.
-- CASE is a way of getting around this. Unfortunately the execution plan is showing clustered index scans, rather than the optimal seeks for the CASEd version
        CASE
            WHEN D.Stop IS NULL THEN NULL
            WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
            WHEN Location in (SELECT Location from #loclist) THEN 1 -- does clustered index SCAN
--Alternate:  EXISTS (SELECT 1 from (SELECT Location from #loclist as l where l.Location=D.Location) as ll) THEN 1 -- does clustered index SCAN
        END is NOT NULL
--      D.Stop is NOT NULL AND EXISTS (SELECT 1 from (SELECT Location from #loclist as l where l.Location=D.Location) as ll) -- does clustered index SEEK
    GROUP BY
        D.Location
--      CASE WHEN D.Stop IS NULL THEN 1 END  --groups all terminating deployments together and seperates out the non-terminating deployment of that series.
), shortestBaseSiteName as (
    SELECT
        sl.intxt,
        CASE WHEN MAX(COALESCE(sl.sRT,sl.s180,sl.sJ)) IS NOT NULL THEN MIN(sl.basetxt) ELSE sl.intxt END as baseName,
        MAX(sl.sRT) as sRT,
        MAX(sl.s180) as s180,
        MAX(sl.sJ) as sJ
    FROM
        subsitelist as sl
    GROUP BY
        sl.intxt
), longestSubSiteName as (
    SELECT 
        sbs.baseName,
        MAX(sbs.intxt) as longestSS,
        MAX(sbs.sRT) as sRT,
        MAX(sbs.s180) as s180,
        MAX(sbs.sJ) as sJ
    FROM
        shortestBaseSiteName as sbs
    GROUP by sbs.baseName
), baseNames as (
    SELECT 
        intxt,
        MAX(baseTxt) as baseName
    FROM 
        subsitelist
    GROUP BY
        intxt, sRT, s180, sJ
    HAVING 
        MIN(COALESCE(sRT,s180,sJ)) is NULL
), subSiteTally as (
    SELECT
        intxt,
        MAX(sRT) as sRT,
        MAX(s180) as s180,
        MAX(sJ) as sJ
    FROM
        subsitelist
    GROUP BY
        intxt
), bigList as (
    SELECT 
        bn.baseName,
        MAX(sst.sRT) as sRT,
        MAX(sst.s180) as s180,
        MAX(sst.sJ) as sJ
    FROM
        subSiteTally as sst INNER JOIN 
            baseNames as bn on bn.intxt=sst.intxt
    GROUP BY
        bn.baseName
), smat as (
    SELECT 
        baseName as Location,
        CASE WHEN baseName in (ML.Location) THEN baseName END as l69,
        baseName+s180 as l180,
        baseName+sJ as lJ,
        baseName+sRT as lRT69,
        baseName+sRT+s180 as lRT180,
        baseName+sRT+sJ as lRTJ
    FROM
        bigList as bl inner join 
        --LEFT OUTER gives all site names, regardless if in the short list or not. RIGHT will return an all-null entry for "donkey" (which is not a site)
            #loclist as ML on bl.baseName=ML.Location
), depWithDets as (
    SELECT 
        smat.Location,
        CASE Dep.Location WHEN l69 THEN 1 END as d69,
        CASE Dep.Location WHEN l180 THEN 1 END as d180,
        CASE Dep.Location WHEN lJ then 1 END as dJ,
        CASE Dep.Location WHEN lRT69 THEN 1 END as dRT69,
        CASE Dep.Location WHEN lRT180 THEN 1 END as dRT180,
        CASE Dep.Location WHEN lRTJ THEN 1 END as dRTJ
        ,smat.l69
        ,smat.l180
        ,smat.lJ
        ,smat.lRT69
        ,smat.lRT180
        ,smat.lRTJ
    FROM
        smat INNER JOIN
            deploys as Dep ON (Dep.Location in (l69,l180,lJ,lRT69,lRT180,lRTJ))
    WHERE
        (Dep.Start > @rangeStart AND Dep.Start < @rangeEnd) OR 
        (Dep.Stop > @rangeStart AND Dep.Stop < @rangeEnd) OR
        (Dep.Start < @rangeStart AND Dep.Stop > @rangeEnd)
)
SELECT Location,
    count(d69) as bool_auton_69,
    count(d180) as bool_auton_180,
    count(dJ) as bool_auton_JSATS,
    count(dRT69) as bool_rtime_69,
    count(dRT180) as bool_rtime_180,
    count(dRTJ) as bool_rtime_JSATS
    ,min(l69) as name_auton_69
    ,min(l180) as name_auton_180
    ,min(lJ) as name_auton_JSATS
    ,min(lRT69) as name_rtime_69
    ,min(lRT180) as name_rtime_180
    ,min(lRTJ) as name_rtime_JSATS
from depWithDets
group by Location
Drop table #loclist
END

    执行计划gif

    最终方案 “最终”执行计划

    C# CLR 函数

    因为我以前从未用 C# 编程过，所以我认为将我的代码修改记录在此处评论中链接的 CLR 中是个好主意。

    clr_splitString_delim（适用于 .NET Framework 3.5）基于 Adam Machanic 的SQLCLR String Splitter：

    using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

/*
Code adapted from http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx (author: Adam Machanic)
alterations to the I/O of Adam's function:
1) change returned field "Item" to "substr"
2) add returned field "sIndex"
       * which position in the list is this substring. 0 indexed
3) add returned field "sStart"
       * At which character position the substring starts with respect to the input string
4) add returned field "sEnd"
       * At which character position the substring ends
*/    
public partial class UserDefinedFunctions
{
    public class SplitStringTable : object
    {
        public String item;
        public int index;
        public int start;
        //        public int length;
        public int end;
        public SplitStringTable()
        {
            item = "";
            index = 0;
            start = 0;
            end = 0;
        }
        public SplitStringTable(String i, int idx, int stp, int nd)
        {
            item = i;
            index = idx;
            start = stp;
            end = nd;
        }
        public override string ToString()
        {
            return item.ToString();
        }
    }
    private static SplitStringTable fill_result(String obj, int sp, int l)
    {
        return (new SplitStringTable { item = obj.ToString(), start = sp, end = l });
    }

    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName = "FillRow_Multi",
        TableDefinition = "substr nvarchar(4000),sIndex int,sStart int,sEnd int",
        IsDeterministic =true
        )
    ]
    public static SplitStringMulti SplitString_Multi( [SqlFacet(MaxSize = -1)] SqlChars Input, [SqlFacet(MaxSize = 255)] SqlChars Delimiter )
    {
        SplitStringMulti ssm = (Input.IsNull || Delimiter.IsNull) ? new SplitStringMulti(new char[0], new char[0]) : new SplitStringMulti(Input.Value, Delimiter.Value);
        return (ssm);
    }

    public static void FillRow_Multi(object obj, out SqlString substr, out SqlInt32 sIndex, out SqlInt32 sStart, out SqlInt32 sEnd)
    {
        SplitStringTable res = (SplitStringTable)obj;
        substr = new SqlString(obj.ToString());
        sIndex = res.index;
        sStart = res.start;
        sEnd = res.end;
    }

    public class SplitStringMulti : IEnumerator
    {
        private static SplitStringTable fill_result(object obj, int idx, int sp, int l)
        {
            return (new SplitStringTable { item = obj.ToString(), index = idx, start = sp, end = l });
        }
        public SplitStringMulti(char[] TheString, char[] Delimiter)
        {
            theString = TheString;
            stringLen = TheString.Length;
            delimiter = Delimiter;
            delimiterLen = (byte)(Delimiter.Length);
            isSingleCharDelim = (delimiterLen == 1);

            lastPos = 0;
            nextPos = delimiterLen * -1;
            delimOccur = 0;
            //leng = nextPos - lastPos;
        }

        #region IEnumerator Members

            public object Current
        {
            get
        {
                var item = new string(theString, lastPos, nextPos - lastPos);
                var res = fill_result(item, delimOccur-1, lastPos, nextPos);
                return res;                     
//                return new string(theString, lastPos, nextPos - lastPos);
        }
        }
        public override String ToString() {
            return new string(theString, lastPos, nextPos - lastPos);
        }
        public bool MoveNext() {
            if (nextPos >= stringLen)
                return false;
            else {
                lastPos = nextPos + delimiterLen;
                for (int i = lastPos; i < stringLen; i++) {
                    bool matches = true;
                    //Optimize for single-character delimiters
                    if (isSingleCharDelim) {
                        if (theString[i] != delimiter[0])
                            matches = false;
                    }
                    else {
                        for (byte j = 0; j < delimiterLen; j++) {
                            if (((i + j) >= stringLen) || (theString[i + j] != delimiter[j])) {
                                matches = false;
                                break;
                            }
                        }
                    }
                    if (matches) {
                        delimOccur++;
                        nextPos = i;
                        //Deal with consecutive delimiters
                        if ((nextPos - lastPos) > 0)
                            return true;
                        else {
                            i += (delimiterLen - 1);
                            lastPos += delimiterLen;
                        }
                    }
                }
                delimOccur++;
                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;
                if ((nextPos - lastPos) > 0)
                    return true;
                else
                    return false;
            }
        }

        public void Reset()
        {
            lastPos = 0;
            delimOccur = 0;
            nextPos = delimiterLen * -1;
        }

        #endregion

        public int lastPos;
        public int nextPos;
        public int delimOccur;

        private readonly char[] theString;
        private readonly char[] delimiter;
        private readonly int stringLen;
        private readonly byte delimiterLen;
        private readonly bool isSingleCharDelim;
    }
};

    在 VisStudio 中创建 DLL 后，将 CLR 组装到数据库中：

    DROP FUNCTION dbo.clr_splitString_delim
go

DROP ASSEMBLY CLRUtilities
GO

CREATE ASSEMBLY CLRUtilities FROM 'c:\DLLs\CLRUtilities.dll' 
  WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.clr_splitString_delim (
   @List      NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE ( substr NVARCHAR(4000), sIndex int, sStart int, sEnd int )
EXTERNAL NAME CLRUtilities.UserDefinedFunctions.SplitString_Multi;
GO
    • 0

相关问题