Skip to content

Instantly share code, notes, and snippets.

@mburbea

mburbea/CreateSplit.sql

Last active April 13, 2016 18:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mburbea/5e142f846d0141c714a1 to your computer and use it in GitHub Desktop.
Save mburbea/5e142f846d0141c714a1 to your computer and use it in GitHub Desktop.
Download ZIP
Hybrid Split 2015
Raw
CreateSplit.sql
if object_id('dbo.fn_split') is not null drop function fn_split;
if object_id('dbo.splitVarbinary') is not null drop function dbo.splitvarbinary;
if exists (select 1 from sys.assemblies where name='split') drop assembly split;
CREATE ASSEMBLY [Split]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300673BF4560000000000000000E00002210B010B00000C00000006000000000000AE2B0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000582B00005300000000400000A002000000000000000000000000000000000000006000000C000000202A00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40B000000200000000C000000020000000000000000000000000000200000602E72737263000000A00200000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000902B0000000000004800000002000500982100008808000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006E026F0800000A2D0D026F0900000A0373060000062A7E010000042A1330020020000000010000110274030000020A03067B020000045404067B030000045405067B04000004542A46168D03000002280A00000A80010000042A1E02280B00000A2A1E02280B00000A2ABA0273050000067D0700000402280B00000A02037D0500000402047D0600000402027B050000048E697D090000042A000000133003009000000002000011027B08000004027B090000043102162A027B080000040A027B060000040B027B050000040C027B070000040D027B09000004130409257B0200000417587D02000004090617587D030000042B230806910733190906027B08000004597D04000004020617587D08000004172A0617580A06110432D8091104027B08000004597D0400000402110417587D08000004172A1A730C00000A7A1E027B070000042A0042534A4201000100000000000C00000076342E302E33303331390000000005006C0000009C020000237E000008030000C802000023537472696E677300000000D00500000800000023555300D8050000100000002347554944000000E8050000A002000023426C6F6200000000000000020000015717A2030902000000FA253300160000010000000C00000004000000090000000900000008000000010000000C00000006000000020000000100000001000000010000000100000001000000020000000200000000000A00010000000000060052004B0006006C0059000A00A50090000A006E0153010600B80199010600E201CF011B00F60100000600250205020600450205020A006A02530106009B024B000600AF024B0000000000010000000000010001000100100015000000050001000100030010002A0000000500020005000300100031000000050005000600310078000A000600CF0025000600D20025000600D80025002100DC0028002100E3002C002100EE002F000100F60025002100FD0025005020000000009600AE000E0001006C20000000009600BD0016000300AA20000000008618C90021000700982000000000911894025C020700B220000000008618C90021000700BA20000000008318C90033000700EC2000000000E60105013A000900882100000000E1010E01210009008F2100000000E60939013E000900000001004D01000002008001000001008A01020002008E0102000300C50102000400CB01000001004D01000002008001040009001100330121002100C90021002900C90021003100C900AA004100C900B0004900C90021005100C900210019007F023A0019008A0252025900A10260020900C90021006100C900210020003B00B5002400130046002E0023006F022E002B0078022E00330081024400130078005702650204000100000045014200020009000300040010000300048000000000000000000000000000000000630200000400000000000000000000000100420000000000040000000000000000000000010084000000000003000200040002000000003C4D6F64756C653E0066706C2E64622E646C6C0055736572446566696E656446756E6374696F6E7300526573756C7400487962726964456E756D657261746F72006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F7200456D707479526573756C740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C42797465730053706C697456617262696E6172790046696C6C5F526573756C74002E63746F72004964005374617274004E756D005F6279746573005F64656C696D69746572005F726573756C74005F7374617274005F6C656E677468004D6F76654E6578740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E5265736574005265736574006765745F43757272656E740043757272656E74006279746573004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650064656C696D69746572006F626A006974656D4E756D6265720053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465007374617274006E756D0053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650066706C2E64620053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C7565002E6363746F7200417272617900476574456E756D657261746F72004E6F74496D706C656D656E746564457863657074696F6E000000032000000000003240270651EF8C46A975F42F728714280008B77A5C561934E089030612090700021209120D050A0004011C1008100810080320000102060803061D050206050306120C062002011D0505032000020320001C0328001C31010003005408074D617853697A65401F000054020D497346697865644C656E6774680054020A49734E756C6C61626C650031010003005408074D617853697A650100000054020D497346697865644C656E6774680154020A49734E756C6C61626C650005200101111D0420010108819B010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650B46696C6C5F526573756C74540E0F5461626C65446566696E6974696F6E226974656D4E756D62657220696E742C20737461727420696E742C206E756D20696E740420001D05040701120C03000001042000120909070508051D05120C080801000300000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000673BF45600000000020000001C0100003C2A00003C0C000052534453E99080C0D4853E47A2DCD6A391FCC2BF01000000643A5C4275696C644167656E745C776F726B5C353838633234326138393630666631365C7372635C66706C2E64625C6F626A5C52656C656173655C66706C2E64622E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000802B000000000000000000009E2B0000002000000000000000000000000000000000000000000000902B000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D0065000000660070006C002E00640062002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000660070006C002E00640062002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[SplitVarbinary]
(@bytes VARBINARY (8000), @delimiter TINYINT)
RETURNS
TABLE (
[itemNumber] INT NULL,
[start] INT NULL,
[num] INT NULL)
AS EXTERNAL NAME [Split].[UserDefinedFunctions].[SplitVarbinary];
Go
CREATE function [dbo].[fn_split](@string varchar(8000),@delimiter char(1))
returns table with schemabinding as
return
select itemNumber,item=substring(@string,start,num)
from dbo.SplitVarbinary(convert(varbinary(8000),@string),ascii(@delimiter));
GO
Raw
delimitedSplit8k.sql
if(OBJECT_ID('delimitedSplit8kb') is not null) drop function DelimitedSplit8Kb;
GO
CREATE FUNCTION [dbo].[DelimitedSplit8KB]
--===== Define I/O parameters
(@pString VARCHAR(8000) , @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
E2(N) AS(select 1 from E1,E1 b),
E4(N) as(select 1 from e2,e2 b),
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (select null)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1
UNION ALL
SELECT t.N+1 FROM cteTally t
WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1) ,0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO
Raw
SplitVarbinary.cs
using Microsoft.SqlServer.Server;
using System;
using System.Collections;
using System.Data.SqlTypes;
public partial class UserDefinedFunctions
{
class Result
{
public int Id;
public int Start;
public int Num;
}
private static readonly IEnumerator EmptyResult = new Result[0].GetEnumerator();
[SqlFunction(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true,
FillRowMethodName = "Fill_Result",
TableDefinition = "itemNumber int, start int, num int"
)]
public static IEnumerator SplitVarbinary(
[SqlFacet(MaxSize = 8000, IsFixedLength = false, IsNullable = false)] SqlBytes bytes,
[SqlFacet(MaxSize = 1, IsFixedLength = true, IsNullable = false)] byte delimiter)
{
return bytes.IsNull ?
EmptyResult
: new HybridEnumerator(bytes.Value, delimiter);
}
class HybridEnumerator : IEnumerator
{
private readonly byte[] _bytes;
private readonly byte _delimiter;
private readonly Result _result = new Result();
private int _start;
private readonly int _length;
// Methods
internal HybridEnumerator(byte[] bytes, byte delimiter)
{
_bytes = bytes;
_delimiter = delimiter;
_length = _bytes.Length;
}
public bool MoveNext()
{
if (_start > _length) return false;
var i = _start;
var delimiter = _delimiter;
var bytes = _bytes;
var r = _result;
var length = _length;
r.Id++;
r.Start = i + 1;
for (; i < length; i++)
{
if (bytes[i] != delimiter) continue;
r.Num = i - _start;
_start = i + 1;
return true;
}
r.Num = length - _start;
_start = length + 1;
return true;
}
void IEnumerator.Reset()
{
throw new NotImplementedException();
}
public object Current
{
get
{
return _result;
}
}
}
public static void Fill_Result(object obj, out int itemNumber, out int start, out int num)
{
var r = (Result)obj;
itemNumber = r.Id;
start = r.Start;
num = r.Num;
}
}
Raw
testscript.sql
use tempdb;
if object_id('dbo.functions','u') is not null drop table dbo.functions;
GO
CREATE TABLE dbo.functions
(
id int not null identity primary key clustered,
name sysname,
d_type char(1) not null,
chunker bit not null default(0)
)
insert into functions(name,d_type,chunker)
VALUES
('SqlBaseline','v',1),
('fn_split','v',0),
('delimitedsplit8kb','v',0),
('xmlsplit','v',0);
GO
if object_id('dbo.fn_split') is not null drop function fn_split;
if object_id('dbo.splitVarbinary') is not null drop function dbo.splitvarbinary;
if exists (select 1 from sys.assemblies where name='split') drop assembly split;
CREATE ASSEMBLY [Split]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300673BF4560000000000000000E00002210B010B00000C00000006000000000000AE2B0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000582B00005300000000400000A002000000000000000000000000000000000000006000000C000000202A00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40B000000200000000C000000020000000000000000000000000000200000602E72737263000000A00200000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000902B0000000000004800000002000500982100008808000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006E026F0800000A2D0D026F0900000A0373060000062A7E010000042A1330020020000000010000110274030000020A03067B020000045404067B030000045405067B04000004542A46168D03000002280A00000A80010000042A1E02280B00000A2A1E02280B00000A2ABA0273050000067D0700000402280B00000A02037D0500000402047D0600000402027B050000048E697D090000042A000000133003009000000002000011027B08000004027B090000043102162A027B080000040A027B060000040B027B050000040C027B070000040D027B09000004130409257B0200000417587D02000004090617587D030000042B230806910733190906027B08000004597D04000004020617587D08000004172A0617580A06110432D8091104027B08000004597D0400000402110417587D08000004172A1A730C00000A7A1E027B070000042A0042534A4201000100000000000C00000076342E302E33303331390000000005006C0000009C020000237E000008030000C802000023537472696E677300000000D00500000800000023555300D8050000100000002347554944000000E8050000A002000023426C6F6200000000000000020000015717A2030902000000FA253300160000010000000C00000004000000090000000900000008000000010000000C00000006000000020000000100000001000000010000000100000001000000020000000200000000000A00010000000000060052004B0006006C0059000A00A50090000A006E0153010600B80199010600E201CF011B00F60100000600250205020600450205020A006A02530106009B024B000600AF024B0000000000010000000000010001000100100015000000050001000100030010002A0000000500020005000300100031000000050005000600310078000A000600CF0025000600D20025000600D80025002100DC0028002100E3002C002100EE002F000100F60025002100FD0025005020000000009600AE000E0001006C20000000009600BD0016000300AA20000000008618C90021000700982000000000911894025C020700B220000000008618C90021000700BA20000000008318C90033000700EC2000000000E60105013A000900882100000000E1010E01210009008F2100000000E60939013E000900000001004D01000002008001000001008A01020002008E0102000300C50102000400CB01000001004D01000002008001040009001100330121002100C90021002900C90021003100C900AA004100C900B0004900C90021005100C900210019007F023A0019008A0252025900A10260020900C90021006100C900210020003B00B5002400130046002E0023006F022E002B0078022E00330081024400130078005702650204000100000045014200020009000300040010000300048000000000000000000000000000000000630200000400000000000000000000000100420000000000040000000000000000000000010084000000000003000200040002000000003C4D6F64756C653E0066706C2E64622E646C6C0055736572446566696E656446756E6374696F6E7300526573756C7400487962726964456E756D657261746F72006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F7200456D707479526573756C740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C42797465730053706C697456617262696E6172790046696C6C5F526573756C74002E63746F72004964005374617274004E756D005F6279746573005F64656C696D69746572005F726573756C74005F7374617274005F6C656E677468004D6F76654E6578740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E5265736574005265736574006765745F43757272656E740043757272656E74006279746573004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650064656C696D69746572006F626A006974656D4E756D6265720053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465007374617274006E756D0053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650066706C2E64620053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C7565002E6363746F7200417272617900476574456E756D657261746F72004E6F74496D706C656D656E746564457863657074696F6E000000032000000000003240270651EF8C46A975F42F728714280008B77A5C561934E089030612090700021209120D050A0004011C1008100810080320000102060803061D050206050306120C062002011D0505032000020320001C0328001C31010003005408074D617853697A65401F000054020D497346697865644C656E6774680054020A49734E756C6C61626C650031010003005408074D617853697A650100000054020D497346697865644C656E6774680154020A49734E756C6C61626C650005200101111D0420010108819B010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650B46696C6C5F526573756C74540E0F5461626C65446566696E6974696F6E226974656D4E756D62657220696E742C20737461727420696E742C206E756D20696E740420001D05040701120C03000001042000120909070508051D05120C080801000300000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000673BF45600000000020000001C0100003C2A00003C0C000052534453E99080C0D4853E47A2DCD6A391FCC2BF01000000643A5C4275696C644167656E745C776F726B5C353838633234326138393630666631365C7372635C66706C2E64625C6F626A5C52656C656173655C66706C2E64622E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000802B000000000000000000009E2B0000002000000000000000000000000000000000000000000000902B000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D0065000000660070006C002E00640062002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000660070006C002E00640062002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[SplitVarbinary]
(@bytes VARBINARY (8000), @delimiter TINYINT)
RETURNS
TABLE (
[itemNumber] INT NULL,
[start] INT NULL,
[num] INT NULL)
AS EXTERNAL NAME [Split].[UserDefinedFunctions].[SplitVarbinary];
Go
CREATE function [dbo].[fn_split](@string varchar(8000),@delimiter char(1))
returns table with schemabinding as
return
select itemNumber,item=substring(@string,start,num)
from dbo.SplitVarbinary(convert(varbinary(8000),@string),ascii(@delimiter));
GO
if object_id('dbo.SqlBaseline') is not null
drop function SqlBaseline;
GO
Create function SqlBaseline
(
@pstring varchar(8000),
@pdelimiter char(1),
@chunks int
)
returns table with schemabinding as
return
with T0(n) as (select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all select 1),
Tally(n) as (select top(@chunks-2) ROW_NUMBER() over (order by (select null)) from T0 a,T0 b,T0 c,T0 d),
cteStart(n) as (
select 1
union all
select n*datalength(@pstring)/(@chunks-1)
from tally
)
select ItemNumber =ROW_NUMBER() over (order by N),
Item=SUBSTRING(@pstring,n,datalength(@pstring)/(@chunks-1))
from cteStart;
GO
if(object_id('xmlsplit') is not null) drop function xmlsplit;
GO
create function xmlsplit(@string varchar(max),@delimiter char(1))
returns table
with schemabinding as
return
select ItemNumber = ROW_NUMBER() OVER(ORDER BY (select null)),
item = x.i.value('./text()[1]', 'varchar(8000)')
FROM(
select lead(a,0) over (order by (select 1))
from (VALUES
(convert(xml,'<r>'+replace(@string,@delimiter,'</r><r>')+'</r>',0))
) r(a)
) a(_)
cross apply _.nodes('./r') x(i)
GO
if(OBJECT_ID('delimitedSplit8kb') is not null) drop function DelimitedSplit8Kb;
GO
CREATE FUNCTION [dbo].[DelimitedSplit8KB]
--===== Define I/O parameters
(@pString VARCHAR(8000) , @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
E2(N) AS(select 1 from E1,E1 b),
E4(N) as(select 1 from e2,e2 b),
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (select null)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1
UNION ALL
SELECT t.N+1 FROM cteTally t
WHERE SUBSTRING(@pString,t.N,1) COLLATE Latin1_General_BIN = @pDelimiter COLLATE Latin1_General_BIN
),
cteLen(N1,L1) AS(
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter COLLATE Latin1_General_BIN,@pString COLLATE Latin1_General_BIN,s.N1) ,0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO
--=====================================================================================================================
-- Conditionally drop and recreate a View that will allow us to use NEWID() within a function so we can make
-- Random numbers in a function and create a function that will create constrained randomized CSV element rows.
--=====================================================================================================================
--===== Conditionally drop the objects in the code below to make reruns easier
IF OBJECT_ID('dbo.iFunction' ,'V' ) IS NOT NULL DROP VIEW dbo.iFunction;
IF OBJECT_ID('dbo.CreateCsv8K','IF') IS NOT NULL DROP FUNCTION dbo.CreateCsv8K;
GO
exec sp_executeSql N'select * into #a from delimitedsplit8kb(''a,b,c'','','');'
exec sp_executeSql N'select * into #a from fn_split(''a,b,c'','','');'
exec sp_executeSQL N'select * into #a from dbo.xmlsplit(''a,b,c'','','');'
exec sp_executesql N'select * into #a from dbo.sqlbaseline(''a,b,c'','','',3);'
GO
CREATE VIEW dbo.iFunction
--/**********************************************************************************************************************
-- Purpose:
-- This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such
-- a thing directly in the function. This view also solves the same problem for GETDATE().
-- Usage:
-- SELECT MyNewID FROM dbo.iFunction; --Returns a GUID
-- SELECT MyDate FROM dbo.iFunction; --Returns a Date
-- Revision History:
-- Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation
-- Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code. No logic changes.
--**********************************************************************************************************************/
with schemabinding
AS
SELECT MyNewID = NEWID(),
MyDate = GETDATE();
GO
CREATE FUNCTION dbo.CreateCsv8K
/**********************************************************************************************************************
Purpose:
Create a CSV table result with a programable number of rows, elements per row, minimum # of characters per element,
and maximum characters per element. The element size is random in nature constrained by the min and max characters
per element.
Usage:
SELECT * FROM dbo.CreateCsv8K(@pNumberOfRows, @pNumberOfElementsPerRow, @pMinElementwidth, @pMaxElementWidth)
Dependencies:
1. View: dbo.iFunction (Produces a NEWID() usable from within a UDF)
Programmer's Notes:
1. The randomness of the elements prevents the delimiters for showing up in the same position for each row so that
SQL Server won't figure that out and cache the information making some splitting techniques seem faster than they
really are.
2. No validation or constraints have been place on the input parameters so use with caution. This code can generate
a lot of data in a couple of heart beats.
Revision History:
Rev 00 - 11 May 2007 - Jeff Moden - Initial creation - Only returned one row and wasn't programmable.
Rev 01 - 26 Jul 2009 - Jeff Moden - Added programmable variables but would only go to 20 characters wide.
Rev 02 - 06 Mar 2011 - Jeff Moden - Converted to iTVF, added minimum element width, and made it so elements can be
virtually any size.
**********************************************************************************************************************/
--===== Declare the I/0
(
@pNumberOfRows INT,
@pNumberOfElementsPerRow INT,
@pMinElementwidth INT,
@pMaxElementWidth INT
)
RETURNS TABLE
AS
RETURN
--===== This creates and populates a test table on the fly containing a
-- sequential column and a randomly generated CSV Parameter column.
SELECT TOP (@pNumberOfRows) --Controls the number of rows in the test table
ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum,
CSV =
(--==== This creates each CSV
SELECT CAST(
STUFF( --=== STUFF get's rid of the leading comma
( --=== This builds CSV row with a leading comma
SELECT TOP (@pNumberOfElementsPerRow) --Controls the number of CSV elements in each row
','
+ LEFT(--==== Builds random length variable within element width constraints
LEFT(REPLICATE('1234567890',CEILING(@pMaxElementWidth/10.0)), @pMaxElementWidth),
ABS(CHECKSUM((SELECT MyNewID FROM dbo.iFunction)))
% (@pMaxElementWidth - @pMinElementwidth + 1) + @pMinElementwidth
)
FROM sys.All_Columns ac3 --Classic cross join pseudo-cursor
CROSS JOIN sys.All_Columns ac4 --can produce row sets up 16 million.
WHERE ac3.Object_ID <> ac1.Object_ID --Without this line, all rows would be the same.
FOR XML PATH('')
)
,1,1,'')
AS VARCHAR(8000))
)
FROM sys.All_Columns ac1 --Classic cross join pseudo-cursor
CROSS JOIN sys.All_Columns ac2 --can produce row sets up 16 million rows
;
GO
--=====================================================================================================================
-- Conditionally drop and recreate the TestResults table
--=====================================================================================================================
--===== Conditionally drop and create the TestResults table
IF OBJECT_ID('dbo.TestResults','U') IS NOT NULL DROP TABLE dbo.TestResults;
CREATE TABLE dbo.TestResults
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SplitterName VARCHAR(50),
NumberOfRows INT,
NumberOfElements INT,
MinElementLength INT,
MaxElementLength INT,
Duration float,
MinLength INT,
AvgLength INT,
MaxLength INT
);
GO
--=====================================================================================================================
-- Conditionally drop and recreate the stored procedure that tests each function and records the results.
--=====================================================================================================================
--===== Conditionally drop and create the TestResults table
IF OBJECT_ID('dbo.TestEachFunction','P') IS NOT NULL DROP PROCEDURE dbo.TestEachFunction;
GO
if(object_id('testEachFunction') is not null) drop procedure testeachfunction
GO
CREATE PROCEDURE dbo.TestEachFunction
/**********************************************************************************************************************
Purpose:
Given the number of rows and elements this testing is for, the stored procedure will test each of the split function
for duration and record the results in an table called dbo.TestResults in the current DB (which should be TempDB).
Revision History:
Rev 01 - 20 May 2014 - Michael Burbea - Modifed to allow for a bit more dynamic code allowing me to test more functions easily.
Rev 00 - 10 Apr 2011 - Jeff Moden - Initial release for testing
**********************************************************************************************************************/
--===== Declare the I/O parameters
@pNumberOfRows INT,
@pNumberOfElements INT,
@pMinElementLength INT,
@pMaxElementLength INT
AS
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Suppress the auto-display of rowcounts for appearance and speed
SET NOCOUNT ON;
--===== Declare some obviously named local variables
DECLARE @StartTime DATETIME,
@EndTime DATETIME,
@Message SYSNAME,
@MinLength INT,
@AvgLength INT,
@MaxLength INT;
--===== Preset and display the current run message
SELECT @Message = '========== '
+ CAST(@pNumberOfRows AS VARCHAR(10)) + ' Rows, '
+ CAST(@pMinElementLength AS VARCHAR(10)) + ' MinElementSize, '
+ CAST(@pMaxElementLength AS VARCHAR(10)) + ' MaxElementSize, '
+ CAST(@pNumberOfElements AS VARCHAR(10)) + ' Elements '
+ '==========';
RAISERROR(@Message,10,1) WITH NOWAIT;
--===== Calculate some statistics for the condition of the data
SELECT @MinLength = MIN(DATALENGTH(CSV)),
@AvgLength = AVG(DATALENGTH(CSV)),
@MaxLength = MAX(DATALENGTH(CSV))
FROM dbo.Csv8K;
--select CONCAT('declare @pNumberOfRows INT=100,
-- @pNumberOfElements INT=10,
-- @pMinElementLength INT=5,
-- @pMaxElementLength INT=20,@MinLength int=',@minLength,',@AvgLength int=',@AvgLength,+',@MaxLength int=',@MaxLength)
--=====================================================================================================================
-- Run the tests, By generating a dynamic sproc.
--=====================================================================================================================
DECLARE @STR NVARCHAR(MAX) ='declare @startTime datetime2,@endTime datetime2,@r int,@in int,@v varchar(8000),@n nvarchar(4000),@m nvarchar(max)'+(select
';RAISERROR(''Testing '+c.name+''',10,1) WITH NOWAIT;
DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;
--===== Start the timer
SELECT @StartTime = sysdatetime();
--===== Run the test
SELECT @r = csv.RowNum, @in = split.ItemNumber, @'+c.d_type+'= split.Item
FROM dbo.CSV8K csv
CROSS APPLY dbo.'+c.name+'(csv.CSV,char(44)'+case when c.chunker=1 then ','+convert(varchar,@pNumberOfElements+1) else '' end+') split
--===== Stop the timer and record the test
select @EndTime= sysdatetime();
INSERT INTO dbo.TestResults
(SplitterName, NumberOfRows, NumberOfElements, MinElementLength, MaxElementLength, Duration, MinLength, AvgLength, MaxLength)
SELECT '''+c.name+''',
@pNumberOfRows,
@pNumberOfElements,
@pMinElementLength,
@pMaxElementLength,
DATEDIFF(microsecond,@StartTime,@EndTime)/1e6,
MinLength = @MinLength,
AvgLength = @AvgLength,
MaxLength = @MaxLength;'
from dbo.functions c
for xml path(''));
select @str=REPLACE(@str,'&#x0D;','')
--select @STR
EXEC SP_EXECUTESQL @str,N'@pNumberOfRows INT,
@pNumberOfElements INT,
@pMinElementLength INT,
@pMaxElementLength INT,
@minLength int,
@avgLength int,
@maxLength int',@pNumberOfRows=@pNumberOfRows,
@pNumberOfElements=@pNumberOfElements,
@pMinElementLength=@pMinElementLength,
@pMaxElementLength=@pMaxElementLength,
@minLength=@minLength,
@avgLength=@avgLength,
@maxLength=@maxLength
GO
--=====================================================================================================================
-- We're ready to rock. Now, run all the tests automatically
--=====================================================================================================================
--===== Alert the operator as to how to check the run status
--===== Declare some obviously named variables
DECLARE @SQL VARCHAR(MAX);
--===== Suppress the auto-display of rowcounts
SET NOCOUNT ON;
--===== Create a "control" CTE and build all of the test commands from that
WITH cteControl (NumberOfRows, NumberOfElements, MinElementLength, MaxElementLength)
AS
(
--===== 1 to 10 characters per element
select 1000, 1, 1, 10 UNION ALL
select 1000, 2, 1, 10 UNION ALL
select 1000, 4, 1, 10 UNION ALL
select 1000, 8, 1, 10 UNION ALL
select 1000, 16, 1, 10 UNION ALL
select 1000, 32, 1, 10 UNION ALL
select 1000, 64, 1, 10 UNION ALL
select 1000, 128, 1, 10 UNION ALL
select 1000, 256, 1, 10 UNION ALL
select 1000, 512, 1, 10 UNION ALL
select 1000,1150, 1, 10 UNION ALL
--===== 10 to 20 characters per element
select 1000, 1, 10, 20 UNION ALL
select 1000, 2, 10, 20 UNION ALL
select 1000, 4, 10, 20 UNION ALL
select 1000, 8, 10, 20 UNION ALL
select 1000, 16, 10, 20 UNION ALL
select 1000, 32, 10, 20 UNION ALL
select 1000, 64, 10, 20 UNION ALL
select 1000, 128, 10, 20 UNION ALL
select 1000, 256, 10, 20 UNION ALL
select 1000, 480, 10, 20 UNION ALL
--===== 20 to 30 characters per element
select 1000, 1, 20, 30 UNION ALL
select 1000, 2, 20, 30 UNION ALL
select 1000, 4, 20, 30 UNION ALL
select 1000, 8, 20, 30 UNION ALL
select 1000, 16, 20, 30 UNION ALL
select 1000, 32, 20, 30 UNION ALL
select 1000, 64, 20, 30 UNION ALL
select 1000, 128, 20, 30 UNION ALL
select 1000, 256, 20, 30 UNION ALL
select 1000, 290, 20, 30 UNION ALL
--===== 30 to 40 characters per element
select 1000, 1, 30, 40 UNION ALL
select 1000, 2, 30, 40 UNION ALL
select 1000, 4, 30, 40 UNION ALL
select 1000, 8, 30, 40 UNION ALL
select 1000, 16, 30, 40 UNION ALL
select 1000, 32, 30, 40 UNION ALL
select 1000, 64, 30, 40 UNION ALL
select 1000, 128, 30, 40 UNION ALL
select 1000, 210, 30, 40 UNION ALL
--===== 40 to 50 characters per element
select 1000, 1, 40, 50 UNION ALL
select 1000, 2, 40, 50 UNION ALL
select 1000, 4, 40, 50 UNION ALL
select 1000, 8, 40, 50 UNION ALL
select 1000, 16, 40, 50 UNION ALL
select 1000, 32, 40, 50 UNION ALL
select 1000, 64, 40, 50 UNION ALL
select 1000, 128, 40, 50 UNION ALL
select 1000, 165, 40, 50 UNION ALL
select 1000,2000, 01, 05 UNION ALL
--===== 90 to 99 characters per element
select 1000, 1, 90, 99 UNION ALL
select 1000, 2, 90, 99 UNION ALL
select 1000, 4, 90, 99 UNION ALL
select 1000, 8, 90, 99 UNION ALL
select 1000, 16, 90, 99 UNION ALL
select 1000, 32, 90, 99 UNION ALL
select 1000, 64, 90, 99 UNION ALL
select 1000, 80, 90, 99 UNION ALL
select 1000,4000, 01, 01 UNION ALL
select 1000, 2,3000,3500
)
--===== Dynamically build all of the test commands from the above
SELECT @SQL = ISNULL(@SQL,'')+
'
IF OBJECT_ID(''dbo.Csv8K'',''U'') IS NOT NULL DROP TABLE dbo.Csv8K;
SELECT *
INTO dbo.Csv8K
FROM dbo.CreateCsv8K
('+CAST(NumberOfRows AS VARCHAR(10))+', '
+CAST(NumberOfElements AS VARCHAR(10))+', '
+CAST(MinElementLength AS VARCHAR(10))+', '
+CAST(MaxElementLength AS VARCHAR(10))+') OPTION (QUERYTRACEON 8690); --# of Rows, # of Elements, MIN element length, MAX element length
EXEC dbo.TestEachFunction '+CAST(NumberOfRows AS VARCHAR(10)) +', '
+CAST(NumberOfElements AS VARCHAR(10))+', '
+CAST(MinElementLength AS VARCHAR(10))+', '
+CAST(MaxElementLength AS VARCHAR(10))+';
'
FROM cteControl
--PRINT @SQL
--===== Run the tests
EXEC (@SQL);
GO
select SplitterName,sum(duration) [total_d],AVG(duration) [avg_d] FROM TestResults group by SplitterName
SELECT dense_rank() over (order by NumberOfRows,NumberOfElements,MinElementLength,MaxElementLength) [trialSet],
dense_rank() over (partition by NumberOfRows,NumberOfElements,MinElementLength,MaxElementLength order by duration) [rank_in_set],
* into #r FROM dbo.TestResults
select r.trialset,r.rank_in_set,r.splittername,r.duration,
Convert(decimal(8,6),r.duration-r2.Duration) [diffFrom_T-SQLBaseline],r.NumberOfElements,r.MinElementLength,r.MaxElementLength,
r.MinLength,r.MaxLength,r.AvgLength
from #r r
join #r r2
on r.trialSet = r2.trialset
and r2.SplitterName = 'SqlBaseline'
order by 1,2
drop table #r
Raw
testscript2016.sql
use tempdb;
if object_id('dbo.functions','u') is not null drop table dbo.functions;
GO
CREATE TABLE dbo.functions
(
id int not null identity primary key clustered,
name sysname,
d_type char(1) not null,
chunker bit not null default(0)
)
insert into functions(name,d_type,chunker)
VALUES
('SqlBaseline','v',1)
,('string_split','v',0)
,('stringsplit','v',0)
,('fn_split','v',0)
,('delimitedsplit8k','v',0)
,('xmlsplit','v',0);
GO
if object_id('dbo.fn_split') is not null drop function fn_split;
if object_id('dbo.splitVarbinary') is not null drop function dbo.splitvarbinary;
if exists (select 1 from sys.assemblies where name='split') drop assembly split;
CREATE ASSEMBLY [Split]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300673BF4560000000000000000E00002210B010B00000C00000006000000000000AE2B0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000582B00005300000000400000A002000000000000000000000000000000000000006000000C000000202A00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40B000000200000000C000000020000000000000000000000000000200000602E72737263000000A00200000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000902B0000000000004800000002000500982100008808000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006E026F0800000A2D0D026F0900000A0373060000062A7E010000042A1330020020000000010000110274030000020A03067B020000045404067B030000045405067B04000004542A46168D03000002280A00000A80010000042A1E02280B00000A2A1E02280B00000A2ABA0273050000067D0700000402280B00000A02037D0500000402047D0600000402027B050000048E697D090000042A000000133003009000000002000011027B08000004027B090000043102162A027B080000040A027B060000040B027B050000040C027B070000040D027B09000004130409257B0200000417587D02000004090617587D030000042B230806910733190906027B08000004597D04000004020617587D08000004172A0617580A06110432D8091104027B08000004597D0400000402110417587D08000004172A1A730C00000A7A1E027B070000042A0042534A4201000100000000000C00000076342E302E33303331390000000005006C0000009C020000237E000008030000C802000023537472696E677300000000D00500000800000023555300D8050000100000002347554944000000E8050000A002000023426C6F6200000000000000020000015717A2030902000000FA253300160000010000000C00000004000000090000000900000008000000010000000C00000006000000020000000100000001000000010000000100000001000000020000000200000000000A00010000000000060052004B0006006C0059000A00A50090000A006E0153010600B80199010600E201CF011B00F60100000600250205020600450205020A006A02530106009B024B000600AF024B0000000000010000000000010001000100100015000000050001000100030010002A0000000500020005000300100031000000050005000600310078000A000600CF0025000600D20025000600D80025002100DC0028002100E3002C002100EE002F000100F60025002100FD0025005020000000009600AE000E0001006C20000000009600BD0016000300AA20000000008618C90021000700982000000000911894025C020700B220000000008618C90021000700BA20000000008318C90033000700EC2000000000E60105013A000900882100000000E1010E01210009008F2100000000E60939013E000900000001004D01000002008001000001008A01020002008E0102000300C50102000400CB01000001004D01000002008001040009001100330121002100C90021002900C90021003100C900AA004100C900B0004900C90021005100C900210019007F023A0019008A0252025900A10260020900C90021006100C900210020003B00B5002400130046002E0023006F022E002B0078022E00330081024400130078005702650204000100000045014200020009000300040010000300048000000000000000000000000000000000630200000400000000000000000000000100420000000000040000000000000000000000010084000000000003000200040002000000003C4D6F64756C653E0066706C2E64622E646C6C0055736572446566696E656446756E6374696F6E7300526573756C7400487962726964456E756D657261746F72006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F7200456D707479526573756C740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C42797465730053706C697456617262696E6172790046696C6C5F526573756C74002E63746F72004964005374617274004E756D005F6279746573005F64656C696D69746572005F726573756C74005F7374617274005F6C656E677468004D6F76654E6578740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E5265736574005265736574006765745F43757272656E740043757272656E74006279746573004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650064656C696D69746572006F626A006974656D4E756D6265720053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465007374617274006E756D0053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650066706C2E64620053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C7565002E6363746F7200417272617900476574456E756D657261746F72004E6F74496D706C656D656E746564457863657074696F6E000000032000000000003240270651EF8C46A975F42F728714280008B77A5C561934E089030612090700021209120D050A0004011C1008100810080320000102060803061D050206050306120C062002011D0505032000020320001C0328001C31010003005408074D617853697A65401F000054020D497346697865644C656E6774680054020A49734E756C6C61626C650031010003005408074D617853697A650100000054020D497346697865644C656E6774680154020A49734E756C6C61626C650005200101111D0420010108819B010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650B46696C6C5F526573756C74540E0F5461626C65446566696E6974696F6E226974656D4E756D62657220696E742C20737461727420696E742C206E756D20696E740420001D05040701120C03000001042000120909070508051D05120C080801000300000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000673BF45600000000020000001C0100003C2A00003C0C000052534453E99080C0D4853E47A2DCD6A391FCC2BF01000000643A5C4275696C644167656E745C776F726B5C353838633234326138393630666631365C7372635C66706C2E64625C6F626A5C52656C656173655C66706C2E64622E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000802B000000000000000000009E2B0000002000000000000000000000000000000000000000000000902B000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D0065000000660070006C002E00640062002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000660070006C002E00640062002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[SplitVarbinary]
(@bytes VARBINARY (8000), @delimiter TINYINT)
RETURNS
TABLE (
[itemNumber] INT NULL,
[start] INT NULL,
[num] INT NULL)
AS EXTERNAL NAME [Split].[UserDefinedFunctions].[SplitVarbinary];
Go
CREATE function [dbo].[fn_split](@string varchar(8000),@delimiter char(1))
returns table with schemabinding as
return
select itemNumber,item=substring(@string,start,num)
from dbo.SplitVarbinary(convert(varbinary(8000),@string),ascii(@delimiter));
GO
if object_id('dbo.SqlBaseline') is not null
drop function SqlBaseline;
GO
Create function SqlBaseline
(
@pstring varchar(8000),
@pdelimiter char(1),
@chunks int
)
returns table with schemabinding as
return
with T0(n) as (select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all select 1),
Tally(n) as (select top(@chunks-2) ROW_NUMBER() over (order by (select null)) from T0 a,T0 b,T0 c,T0 d),
cteStart(n) as (
select 1
union all
select n*datalength(@pstring)/(@chunks-1)
from tally
)
select ItemNumber =ROW_NUMBER() over (order by N),
value=SUBSTRING(@pstring,n,datalength(@pstring)/(@chunks-1))
from cteStart;
GO
if(object_id('xmlsplit') is not null) drop function xmlsplit;
GO
create function xmlsplit(@string varchar(max),@delimiter char(1))
returns table
with schemabinding as
return
select ItemNumber = ROW_NUMBER() OVER(ORDER BY (select null)),
value = x.i.value('./text()[1]', 'varchar(8000)')
FROM(
select lead(a,0) over (order by (select 1))
from (VALUES
(convert(xml,'<r>'+replace(@string,@delimiter,'</r><r>')+'</r>',0))
) r(a)
) a(_)
cross apply _.nodes('./r') x(i)
GO
if(OBJECT_ID('delimitedSplit8k') is not null) drop function DelimitedSplit8K;
go
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000) , @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
E2(N) AS(select 1 from E1,E1 b),
E4(N) as(select 1 from e2,e2 b),
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (select null)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1
UNION ALL
SELECT t.N+1 FROM cteTally t
WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1) ,0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
value = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO
if object_id('stringsplit') is not null drop function dbo.stringsplit
go
create function dbo.stringsplit(@pstring varchar(8000),@delimiter char(1))
returns table
with schemabinding as
return
select itemNumber = ROW_NUMBER() over (order by (select 1)),
value = value
from string_split(@pstring,@delimiter);
go
--=====================================================================================================================
-- Conditionally drop and recreate a View that will allow us to use NEWID() within a function so we can make
-- Random numbers in a function and create a function that will create constrained randomized CSV element rows.
--=====================================================================================================================
--===== Conditionally drop the objects in the code below to make reruns easier
IF OBJECT_ID('dbo.iFunction' ,'V' ) IS NOT NULL DROP VIEW dbo.iFunction;
IF OBJECT_ID('dbo.CreateCsv8K','IF') IS NOT NULL DROP FUNCTION dbo.CreateCsv8K;
GO
exec sp_executeSql N'select * into #a from delimitedsplit8k(''a,b,c'','','');'
exec sp_executeSql N'select * into #a from fn_split(''a,b,c'','','');'
exec sp_executeSQL N'select * into #a from dbo.xmlsplit(''a,b,c'','','');'
exec sp_executesql N'select * into #a from dbo.stringsplit(''a,b,c'','','');'
exec sp_executesql N'select * into #a from string_split(''a,b,c'','','');'
exec sp_executesql N'select * into #a from dbo.sqlbaseline(''a,b,c'','','',3);'
GO
CREATE VIEW dbo.iFunction
--/**********************************************************************************************************************
-- Purpose:
-- This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such
-- a thing directly in the function. This view also solves the same problem for GETDATE().
-- Usage:
-- SELECT MyNewID FROM dbo.iFunction; --Returns a GUID
-- SELECT MyDate FROM dbo.iFunction; --Returns a Date
-- Revision History:
-- Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation
-- Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code. No logic changes.
--**********************************************************************************************************************/
with schemabinding
AS
SELECT MyNewID = NEWID(),
MyDate = GETDATE();
GO
CREATE FUNCTION dbo.CreateCsv8K
/**********************************************************************************************************************
Purpose:
Create a CSV table result with a programable number of rows, elements per row, minimum # of characters per element,
and maximum characters per element. The element size is random in nature constrained by the min and max characters
per element.
Usage:
SELECT * FROM dbo.CreateCsv8K(@pNumberOfRows, @pNumberOfElementsPerRow, @pMinElementwidth, @pMaxElementWidth)
Dependencies:
1. View: dbo.iFunction (Produces a NEWID() usable from within a UDF)
Programmer's Notes:
1. The randomness of the elements prevents the delimiters for showing up in the same position for each row so that
SQL Server won't figure that out and cache the information making some splitting techniques seem faster than they
really are.
2. No validation or constraints have been place on the input parameters so use with caution. This code can generate
a lot of data in a couple of heart beats.
Revision History:
Rev 00 - 11 May 2007 - Jeff Moden - Initial creation - Only returned one row and wasn't programmable.
Rev 01 - 26 Jul 2009 - Jeff Moden - Added programmable variables but would only go to 20 characters wide.
Rev 02 - 06 Mar 2011 - Jeff Moden - Converted to iTVF, added minimum element width, and made it so elements can be
virtually any size.
**********************************************************************************************************************/
--===== Declare the I/0
(
@pNumberOfRows INT,
@pNumberOfElementsPerRow INT,
@pMinElementwidth INT,
@pMaxElementWidth INT
)
RETURNS TABLE
AS
RETURN
--===== This creates and populates a test table on the fly containing a
-- sequential column and a randomly generated CSV Parameter column.
SELECT TOP (@pNumberOfRows) --Controls the number of rows in the test table
ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum,
CSV =
(--==== This creates each CSV
SELECT CAST(
STUFF( --=== STUFF get's rid of the leading comma
( --=== This builds CSV row with a leading comma
SELECT TOP (@pNumberOfElementsPerRow) --Controls the number of CSV elements in each row
','
+ LEFT(--==== Builds random length variable within element width constraints
LEFT(REPLICATE('1234567890',CEILING(@pMaxElementWidth/10.0)), @pMaxElementWidth),
ABS(CHECKSUM((SELECT MyNewID FROM dbo.iFunction)))
% (@pMaxElementWidth - @pMinElementwidth + 1) + @pMinElementwidth
)
FROM sys.All_Columns ac3 --Classic cross join pseudo-cursor
CROSS JOIN sys.All_Columns ac4 --can produce row sets up 16 million.
WHERE ac3.Object_ID <> ac1.Object_ID --Without this line, all rows would be the same.
FOR XML PATH('')
)
,1,1,'')
AS VARCHAR(8000))
)
FROM sys.All_Columns ac1 --Classic cross join pseudo-cursor
CROSS JOIN sys.All_Columns ac2 --can produce row sets up 16 million rows
;
GO
--=====================================================================================================================
-- Conditionally drop and recreate the TestResults table
--=====================================================================================================================
--===== Conditionally drop and create the TestResults table
IF OBJECT_ID('dbo.TestResults','U') IS NOT NULL DROP TABLE dbo.TestResults;
CREATE TABLE dbo.TestResults
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SplitterName VARCHAR(50),
NumberOfRows INT,
NumberOfElements INT,
MinElementLength INT,
MaxElementLength INT,
Duration float,
MinLength INT,
AvgLength INT,
MaxLength INT
);
GO
--=====================================================================================================================
-- Conditionally drop and recreate the stored procedure that tests each function and records the results.
--=====================================================================================================================
--===== Conditionally drop and create the TestResults table
IF OBJECT_ID('dbo.TestEachFunction','P') IS NOT NULL DROP PROCEDURE dbo.TestEachFunction;
GO
if(object_id('testEachFunction') is not null) drop procedure testeachfunction
GO
CREATE PROCEDURE dbo.TestEachFunction
/**********************************************************************************************************************
Purpose:
Given the number of rows and elements this testing is for, the stored procedure will test each of the split function
for duration and record the results in an table called dbo.TestResults in the current DB (which should be TempDB).
Revision History:
Rev 01 - 20 May 2014 - Michael Burbea - Modifed to allow for a bit more dynamic code allowing me to test more functions easily.
Rev 00 - 10 Apr 2011 - Jeff Moden - Initial release for testing
**********************************************************************************************************************/
--===== Declare the I/O parameters
@pNumberOfRows INT,
@pNumberOfElements INT,
@pMinElementLength INT,
@pMaxElementLength INT
AS
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Suppress the auto-display of rowcounts for appearance and speed
SET NOCOUNT ON;
--===== Declare some obviously named local variables
DECLARE @Message SYSNAME,
@MinLength INT,
@AvgLength INT,
@MaxLength INT;
--===== Preset and display the current run message
SELECT @Message = '========== '
+ CAST(@pNumberOfRows AS VARCHAR(10)) + ' Rows, '
+ CAST(@pMinElementLength AS VARCHAR(10)) + ' MinElementSize, '
+ CAST(@pMaxElementLength AS VARCHAR(10)) + ' MaxElementSize, '
+ CAST(@pNumberOfElements AS VARCHAR(10)) + ' Elements '
+ '==========';
RAISERROR(@Message,10,1) WITH NOWAIT;
--===== Calculate some statistics for the condition of the data
SELECT @MinLength = MIN(DATALENGTH(CSV)),
@AvgLength = AVG(DATALENGTH(CSV)),
@MaxLength = MAX(DATALENGTH(CSV))
FROM dbo.Csv8K;
--select CONCAT('declare @pNumberOfRows INT=100,
-- @pNumberOfElements INT=10,
-- @pMinElementLength INT=5,
-- @pMaxElementLength INT=20,@MinLength int=',@minLength,',@AvgLength int=',@AvgLength,+',@MaxLength int=',@MaxLength)
--=====================================================================================================================
-- Run the tests, By generating a dynamic sproc.
--=====================================================================================================================
DECLARE @STR NVARCHAR(MAX) ='declare @startTime datetime2,@endTime datetime2,@r int,@in int,@v varchar(8000),@n nvarchar(4000),@m nvarchar(max)'+(select
';RAISERROR(''Testing '+c.name+''',10,1) WITH NOWAIT;
DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;
--===== Start the timer
SELECT @StartTime = sysdatetime();
--===== Run the test
SELECT @r = csv.RowNum
-- ,@in = split.ItemNumber
,@'+c.d_type+'= split.value
FROM dbo.CSV8K csv
CROSS APPLY '+c.name+'(csv.CSV,char(44)'+case when c.chunker=1 then ','+convert(varchar,@pNumberOfElements+1) else '' end+') split
--===== Stop the timer and record the test
select @EndTime= sysdatetime();
INSERT INTO dbo.TestResults
(SplitterName, NumberOfRows, NumberOfElements, MinElementLength, MaxElementLength, Duration, MinLength, AvgLength, MaxLength)
SELECT '''+c.name+''',
@pNumberOfRows,
@pNumberOfElements,
@pMinElementLength,
@pMaxElementLength,
DATEDIFF(microsecond,@StartTime,@EndTime)/1e6,
MinLength = @MinLength,
AvgLength = @AvgLength,
MaxLength = @MaxLength;'
from dbo.functions c
for xml path(''));
select @str=REPLACE(@str,'&#x0D;','')
--select @STR
EXEC SP_EXECUTESQL @str,N'@pNumberOfRows INT,
@pNumberOfElements INT,
@pMinElementLength INT,
@pMaxElementLength INT,
@minLength int,
@avgLength int,
@maxLength int',@pNumberOfRows=@pNumberOfRows,
@pNumberOfElements=@pNumberOfElements,
@pMinElementLength=@pMinElementLength,
@pMaxElementLength=@pMaxElementLength,
@minLength=@minLength,
@avgLength=@avgLength,
@maxLength=@maxLength
GO
--=====================================================================================================================
-- We're ready to rock. Now, run all the tests automatically
--=====================================================================================================================
--===== Alert the operator as to how to check the run status
--===== Declare some obviously named variables
DECLARE @SQL VARCHAR(MAX);
--===== Suppress the auto-display of rowcounts
SET NOCOUNT ON;
--===== Create a "control" CTE and build all of the test commands from that
WITH cteControl (NumberOfRows, NumberOfElements, MinElementLength, MaxElementLength)
AS
(
--===== 1 to 10 characters per element
select 1000, 1, 1, 10 UNION ALL
select 1000, 2, 1, 10 UNION ALL
select 1000, 4, 1, 10 UNION ALL
select 1000, 8, 1, 10 UNION ALL
select 1000, 16, 1, 10 UNION ALL
select 1000, 32, 1, 10 UNION ALL
select 1000, 64, 1, 10 UNION ALL
select 1000, 128, 1, 10 UNION ALL
select 1000, 256, 1, 10 UNION ALL
select 1000, 512, 1, 10 UNION ALL
select 1000,1150, 1, 10 UNION ALL
--===== 10 to 20 characters per element
select 1000, 1, 10, 20 UNION ALL
select 1000, 2, 10, 20 UNION ALL
select 1000, 4, 10, 20 UNION ALL
select 1000, 8, 10, 20 UNION ALL
select 1000, 16, 10, 20 UNION ALL
select 1000, 32, 10, 20 UNION ALL
select 1000, 64, 10, 20 UNION ALL
select 1000, 128, 10, 20 UNION ALL
select 1000, 256, 10, 20 UNION ALL
select 1000, 480, 10, 20 UNION ALL
--===== 20 to 30 characters per element
select 1000, 1, 20, 30 UNION ALL
select 1000, 2, 20, 30 UNION ALL
select 1000, 4, 20, 30 UNION ALL
select 1000, 8, 20, 30 UNION ALL
select 1000, 16, 20, 30 UNION ALL
select 1000, 32, 20, 30 UNION ALL
select 1000, 64, 20, 30 UNION ALL
select 1000, 128, 20, 30 UNION ALL
select 1000, 256, 20, 30 UNION ALL
select 1000, 290, 20, 30 UNION ALL
--===== 30 to 40 characters per element
select 1000, 1, 30, 40 UNION ALL
select 1000, 2, 30, 40 UNION ALL
select 1000, 4, 30, 40 UNION ALL
select 1000, 8, 30, 40 UNION ALL
select 1000, 16, 30, 40 UNION ALL
select 1000, 32, 30, 40 UNION ALL
select 1000, 64, 30, 40 UNION ALL
select 1000, 128, 30, 40 UNION ALL
select 1000, 210, 30, 40 UNION ALL
--===== 40 to 50 characters per element
select 1000, 1, 40, 50 UNION ALL
select 1000, 2, 40, 50 UNION ALL
select 1000, 4, 40, 50 UNION ALL
select 1000, 8, 40, 50 UNION ALL
select 1000, 16, 40, 50 UNION ALL
select 1000, 32, 40, 50 UNION ALL
select 1000, 64, 40, 50 UNION ALL
select 1000, 128, 40, 50 UNION ALL
select 1000, 165, 40, 50 UNION ALL
select 1000,2000, 01, 05 UNION ALL
--===== 90 to 99 characters per element
select 1000, 1, 90, 99 UNION ALL
select 1000, 2, 90, 99 UNION ALL
select 1000, 4, 90, 99 UNION ALL
select 1000, 8, 90, 99 UNION ALL
select 1000, 16, 90, 99 UNION ALL
select 1000, 32, 90, 99 UNION ALL
select 1000, 64, 90, 99 UNION ALL
select 1000, 80, 90, 99 UNION ALL
--select 1000,4000, 01, 01 UNION ALL
select 1000, 2,3000,3500
)
--===== Dynamically build all of the test commands from the above
SELECT @SQL = ISNULL(@SQL,'')+
'
IF OBJECT_ID(''dbo.Csv8K'',''U'') IS NOT NULL DROP TABLE dbo.Csv8K;
SELECT *
INTO dbo.Csv8K
FROM dbo.CreateCsv8K
('+CAST(NumberOfRows AS VARCHAR(10))+', '
+CAST(NumberOfElements AS VARCHAR(10))+', '
+CAST(MinElementLength AS VARCHAR(10))+', '
+CAST(MaxElementLength AS VARCHAR(10))+') OPTION (QUERYTRACEON 8690); --# of Rows, # of Elements, MIN element length, MAX element length
EXEC dbo.TestEachFunction '+CAST(NumberOfRows AS VARCHAR(10)) +', '
+CAST(NumberOfElements AS VARCHAR(10))+', '
+CAST(MinElementLength AS VARCHAR(10))+', '
+CAST(MaxElementLength AS VARCHAR(10))+';
'
FROM cteControl
--PRINT @SQL
--===== Run the tests
EXEC (@SQL);
GO
select SplitterName,sum(duration) [total_d],AVG(duration) [avg_d] FROM TestResults group by SplitterName
SELECT dense_rank() over (order by NumberOfRows,NumberOfElements,MinElementLength,MaxElementLength) [trialSet],
dense_rank() over (partition by NumberOfRows,NumberOfElements,MinElementLength,MaxElementLength order by duration) [rank_in_set],
* into #r FROM dbo.TestResults
select r.trialset,r.rank_in_set,r.splittername, r.duration,
[diffFrom_T-SQLBaseline]=r.duration-r2.Duration ,r.NumberOfElements,r.MinElementLength,r.MaxElementLength,
r.MinLength,r.MaxLength,r.AvgLength
from #r r
join #r r2
on r.trialSet = r2.trialset
and r2.SplitterName = 'SqlBaseline'
order by 1,2
drop table #r
@mburbea
Copy link
Author

mburbea commented Mar 24, 2016

corrected script with fix for trailing delimiters. Source code updated.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment