在慢速运行的情况下使用CTE；CASE语句的tSQL存储过程

Question

在不传递显式值的情况下，我有一个用户定义的存储过程，我只想传递所有位置(nvarchar(50))，这是表的主键‘d字段: 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
)

...do还有很多其他的东西..。

为了提高在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分钟(调用时没有限制列表)。我以为每条路都要花差不多同样的时间，因为第二条条款应该很快被解雇，而第三条条款则不应该被审查。

那怎么回事？此代码：

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秒，因为这将是一个经常执行的SP在web应用程序上使用它来填充和限制其他用户选择。基本上，我不希望这成为一个明显的瓶颈。初始运行时间约为3分钟，但在添加或更改了一些索引后，该值降至6-8秒范围。

周一(2016-08-29)，在进行重大更改之前，在没有输入参数的简单WHERE: 5s Simple WHERE和rangeEnd: 4s Simple WHERE中，@Locations设置为7元素CSV变量CASEd，其中:最多10分钟。

在重新处理CLR函数(见下面的答案)之后，星期二(2016-08-30) Simple或CASEd其中没有输入参数，或者使用rangeStart和rangeEnd: rangeStart和rangeEnd: CASEd WHERE或CASEd WHERE 7元素@Locations: 0-1s。

在将table变量@loclist迁移到temp表#loclist之后，所有测试地点/参数:0-1

Answer 1

摘要

在实现了所提供的答案中的一些建议之后，整个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重新安装Server 2008 R2来添加全文功能，这可能对我没有好处。

，但是，就像我说的，整个混乱需要0-1 S来执行，所以我现在很满意。

。

全存储过程

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 Framework3.5)基于亚当·马查尼的SQLCLR字符串拆分器：

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

Answer 2

两大性能问题：

1. 您的CSV分配器功能是一个主要的性能杀手。把它换成Jeff的DelimitedSplit8k函数。你可以读到所有关于它的这里。或者更好的是，如果您在2008+上，可以将它替换为CLR函数或表值参数。请看亚伦·伯特兰的各种CSV分配器功能的性能测试。总体来说，CLR是赢家。
2. 表变量可能是一个大的性能杀手，即使它只有1行。将其切换到临时表并向其添加聚集索引。

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

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

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

Answer 3

因为你分享了图像，所以我不能详细描述你的问题。我指出了两个执行计划的主要区别。

在第一个计划中，Server为查询创建执行计划并使用非聚集索引查找，而在第二个计划中使用索引扫描。这是增加总执行时间的罪魁祸首。

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

索引扫描：-触及表/索引中的每一行，无论是否符合条件。

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

包括塔拉·凯泽的建议，我建议

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

或

1. 提取临时表中的所有记录，在其上创建索引，并将数据插入其中，包括CASE语句，并从temp表中检索数据。创建表#TEMP (位置DataType(长度)，-估计长度开始DataType(长度)，停止DataType(长度)，WCondition DataType(长度) )；插入到#TEMP中，选择D.Location，MIN(D.Start)作为开始，MAX(D.停止播放)作为停止，当D.Stop为NULL时，当@D.Stop为NULL时为0，当@Locations为空，则为1-完整列表，因此绑定到另一个列表对我们没有任何好处。当存在时(选择1 from (从@loclist选择Location from @loclist as L as l.Location=D.Location)作为ll)，然后从部署中选择1结束作为D选择*来自#TEMP条件-Put条件谢谢