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
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
