Skip to content

Instantly share code, notes, and snippets.

@mburbea

mburbea/string_split_perf_into_table_is_poor.sql

Created April 13, 2016 18:25
Show Gist options
  • Save mburbea/52b3f7ecba78dfe515d663a672b4fd5b to your computer and use it in GitHub Desktop.
Save mburbea/52b3f7ecba78dfe515d663a672b4fd5b to your computer and use it in GitHub Desktop.
Download ZIP
Sql 2016's string_split function screams when you don't need to save the elements into a table. If you do need to do that, then it's slow! Almost as slow as an optimized t-sql split func.
Raw
string_split_perf_into_table_is_poor.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
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),
item = 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)),
item = value
from string_split(@pstring,@delimiter);
go
if OBJECT_ID('ifunction') is not null drop view dbo.iFunction;
go
CREATE VIEW dbo.iFunction
with schemabinding
AS
SELECT MyNewID = NEWID(),
MyDate = GETDATE();
GO
if OBJECT_ID('createcsv8k') is not null drop function dbo.CreateCsv8K;
go
CREATE FUNCTION dbo.CreateCsv8K
(
@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
CROSS JOIN sys.All_Columns ac2
;
GO
exec sp_executesql N'select * into #a from fn_split(''1,2,3'','','')'
exec sp_executesql N'select * into #a from stringsplit(''1,2,3'','','')'
exec sp_executesql N'select * into #a from delimitedsplit8k(''1,2,3'','','')'
select * into c from CreateCsv8K(100000,20,20,30) OPTION (QUERYTRACEON 8690)
DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;
go
create table #k(
rowNum int,
itemNumber int,
item varchar(8000)
)
declare @ datetime2=sysdatetime()
insert into #k
select rowNum,itemNumber,item
from c
cross apply dbo.[DelimitedSplit8K](csv,',') s
option(maxdop 1)
select splitter = 'delimitedsplit8k',duration = datediff(ms,@,sysdatetime())/1e3
drop table #k
DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;
go
create table #k(
rowNum int,
itemNumber int,
item varchar(8000)
)
declare @ datetime2=sysdatetime()
insert into #k
select rowNum,itemNumber,item
from c
cross apply dbo.[fn_split](csv,',') s
option(maxdop 1)
select splitter = 'fn_split',duration = datediff(ms,@,sysdatetime())/1e3
drop table #k
DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;
go
create table #k(
rowNum int,
itemNumber int,
item varchar(8000)
)
declare @ datetime2=sysdatetime()
insert into #k
select rowNum,itemNumber,item
from c
cross apply dbo.stringsplit(csv,',') s
option(maxdop 1)
select splitter = 'stringsplit',duration = datediff(ms,@,sysdatetime())/1e3
drop table #k
go
drop table c
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment