3

I have a user-defined Stored Procedure When calling without passing explicit values, I want to just pass all Locations (nvarchar(50)), which is the primary key'd field of a table: Monitor_Locations (with ~850 entries)

One part of the SP is defined as follows (clipped).

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 a bunch of other stuff...

in order to improve the speed of the stored procedure when a restricted list of sites is sent into the SP, I wanted to replacing the WHERE clause with

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

but where the SP once took 6-8 seconds to execute, now it takes 2.5 mins (for calling without a restrictive list). I thought it would take roughly the same amount of time each way for the full list, as the second clause of the CASE should be fired very quickly and the third clause should never be examined.

So what's going on? This code:

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

Takes a ~10 minute run time with this plan:

CaseWhere

To contrast here's the WHERE D.Stop is not NULL plan (6s): StandardWhere

At one point, this SP was taking 1 second with this version, but by changing the SP and then back again, it took 6 seconds again. As mentioned in the answers, this was likely due to parameter sniffing.

Run times

My goal time for execution is less than 2 seconds, as this will be a frequently executed SP on a web application that uses this to populate and restrict other user selections. Basically, I don't want this to be a noticeable bottleneck. Initial run times were on the order of 3 minutes, but after adding or altering some indexes, this dropped to the 6-8 second range.

On Monday (2016-08-29), prior to major alterations Simple WHERE without input parameters: 5s Simple WHERE with rangeStart and rangeEnd: 4s Simple WHERE with @Locations set to a 7 element CSV variable CASEd WHERE: up to 10 minutes

After re-working the CLR function (see my answer below) Tuesday (2016-08-30) Simple or CASEd WHERE without input parameters OR Simple or CASEd WHERE with rangeStart and rangeEnd: 3s Simple or CASEd WHERE with 7 element @Locations: 0-1s

After migrating table variable @loclist to temp table #loclist All tested WHEREs/parameters: 0-1s

  • Can you please add actual execution plan of the SP which included CASE statement in WHERE clause? – Rajesh Ranjan Aug 27 '16 at 2:47
  • glad you suggested that. it gave a suggested index creation which improved the WHERE-based query from 6-8s to 1s. Will attach those XMLs shortly. – mpag Aug 27 '16 at 3:22
4

Two big performance problems:

  1. Your CSV splitter function is a major performance killer. Swap it out for Jeff Moden's DelimitedSplit8k function. You can read all about it here. Or better yet, swap it out for the CLR function or a table-valued parameter if you are on 2008+. Check out Aaron Bertrand's performance tests for the various CSV splitter functions. CLR is the winner overall.
  2. The table variable can be a big performance killer even if it only has 1 row. Switch it to a temporary table and add a clustered index to it.

In your now provided execution plans, the function shows a 0% cost, but that is not the case. The function cost is higher, but you won't see the actual cost in the execution plan unless it's an inline table valued function.

Unfortunately, although I once had a runtime of 1 sec, by changing the SP and then back again, it takes 6 seconds again.

That smells of parameter sniffing.

  • as to table valued parameters for input: I don't think I can do it because the standard way of calling this SP is by use of a PHP call, and the way of providing the values is via a URI string. I'll check out the DelimitedSplit function though. How can I compile/install/whatever the CLR version? – mpag Aug 27 '16 at 4:53
  • 2
    Single hash temp tables are automatically unique to each session. – Max Vernon Aug 27 '16 at 16:28
  • 2
    You should use the single hash which will create a local scope temp table that can only be "seen" by your process - double hash (##) temp tables are global and can be "seen" by other processes other than yours- check out this link m.sqlmag.com/t-sql/temporary-tables-local-vs-global – Scott Hodgin Aug 27 '16 at 16:29
  • 1
    @Tara - look at the results from Aaron's testing of string splitter functions at sqlperformance.com/2012/07/t-sql-queries/split-strings – Max Vernon Aug 27 '16 at 16:34
  • 1
    @MaxVernon, thanks. I added the link to my post. – Tara Kizer Aug 28 '16 at 23:59
1

enter image description here

Because you shared image, So I can't detail your problem in depth. I marked main difference between two execution plans.

In first plan SQL Server created execution plan for the query and used Non Clustered Index Seek but in second one it used Index Scan. This is main the culprit which increase total execution time.

Index Seek:- Only touches rows that qualify and pages that contain these qualifying rows.

Index Scan:- Touches every row in the table/index, whether or not it qualifies.

When you manipulate data (column) (using function or case statement) in where condition, forst SQL Server scan full index/table and then perform manipulation data and match your condition. This process increase memory utilization, disk IO and increase execution time.

Including suggestion from Tara Kizer, I would suggest to

  1. Add a column in the table and make changes in the SPs which you use to insert data into the table based on the logic you are using in WHERE condition with CASE statement ind create index on that. It will solve your problem.

Or

  1. Pull all records in a temp table, create index on that and insert data into that including CASE statement and retrieve data from the temp table.

    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
    

    Thanks

0

Summary

After implementing some of the suggestions in answers provided, the whole SP appears to execute in 0-1 second, regardless of parameter values used. Much thanks to all who helped.

If this seems to take performance hit in the future (or in binding the results of this to another table), I'll look into Rajesh's suggestion of storing a "conditional" value in a temp table.

Unresolved issues

I'm not sure why it's using a clustered index scan rather than a seek for the following:

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

whereas, this does a seek

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

Additionally over the weekend, I was looking into whether fulltext indexing would be beneficial for my LIKE x+'[_]%' join, but I was unable to figure out what the default word splitters are (does '_' with language 1033 split apart word tokens? Or just true whitespace characters?) And I don't appear to have fulltext indexing installed (SELECT * from sys.fulltext_languages returns an empty result set, as does EXEC sp_help_fulltext_system_components). As I don't have install media I'd need to wait around for IT to reinstall SQL Server 2008 R2 to add fulltext capability, which may not even benefit me.

But, as I said, the whole mess takes 0-1 s to execute, so I'm satisfied for now.

Whole Stored Procedure

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

Execution plan gif

Final plan "Final" execution plan

C# CLR function

As I've never before programmed in C#, I figured it would be a good idea to document my code modifications to a CLR that was linked in the comments here.

clr_splitString_delim (for .NET Framework 3.5) based on Adam Machanic's 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;
    }
};

Assembly of CLR into database, once the DLL has already been created in VisStudio:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.