Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««5758596061»»»

Tally OH! An Improved SQL 8K “CSV Splitter” Function Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 7:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 35,827, Visits: 32,499
Miller (4/14/2014)
Anyway, after all this I ended up with BetterSplit being about 11% faster than the DelimitedSplit8K.


I'm not sure how you came up with that number. Please explain from the results you sent me.

Also and to be sure, the DelimitedSplit8K in the test is (IIRC), the original repair from the article. A couple of folks in the discussion made some improvements on that and the current version of DelimitedSplit8K includes their enhancements (the DelimitedSplit8K in the test results isn't the latest... it's one of the others but I don't recall which one... I'll dig that info up. Might even be in the comments of the function itself (should be)).

I do thank you for doing the test. Much appreciated.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561710
Posted Monday, April 14, 2014 8:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 22, 2014 11:13 AM
Points: 67, Visits: 68
I got the results of 11% by comparing the total time to run all the tests.
Note. Some tests had bigger differences than others, and some even had the Delimited8K faster. Also, repeated executions of the test produced somewhat different results. 14% or 9% etc.
Also, when I started, I wanted a split that would work with larger strings in the range of 50000 rows which demands the varchar max. I also wanted multi-character delimiters. In this range, the BetterSplit is more than 2x faster than the Delimited8K adapted to support varchar max. That's why I thought it would be faster in the < 8000 range as well.

here is the latest function with all support for multi-byte delimiters removed.


IF OBJECT_ID('dbo.[BetterSplit]') IS NOT NULL
DROP FUNCTION dbo.BetterSplit;
GO
create FUNCTION [dbo].[BetterSplit]
(
@L VARCHAR(8000)
,@D CHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

WITH
--A,B,C used to generate large number of rows for tally
A AS(SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))A(N))
,B(N)AS(SELECT 1FROM A,A B)
,C(N)AS(SELECT 1FROM B A,B)

--Tally table
,T(N)AS(SELECT TOP(ISNULL(DATALENGTH(@L),0))ROW_NUMBER()OVER(ORDER BY(SELECT 1))FROM C)

--Start of each delimiter
,S AS(SELECT S=1UNION ALL SELECT S=N+1FROM T WHERE SUBSTRING(@L,N,1)=@D)

--Start,Length of each value in list.
--Use LEAD() to get next row start, then subtract current row start and adjust for size of delimiters
,L AS(SELECT S,L=LEAD(S,1,DATALENGTH(@L)+2)OVER(ORDER BY S)-S-1FROM S)

--do final select here based on start,length
SELECT ItemNumber=ROW_NUMBER()OVER(ORDER BY S),Item=SUBSTRING(@L,S,L)FROM L
;


Also attached is the latest test results showing that for the larger tests the BetterSplit is faster, but for some of the smaller tests, the DelimitedSplit8K is faster.

RowNum NumberOfRows NumberOfElements MinElementLength MaxElementLength DelimitedSplit8K BetterSplit MinLength AvgLength MaxLength %dif
6 1000 1 1 10 0.01 0.006 1 5 10 40.00%
13 1000 2 1 10 0.016 0.013 3 12 21 18.75%
20 1000 4 1 10 0.023 0.036 9 24 42 -56.52%
27 1000 8 1 10 0.046 0.056 26 51 78 -21.74%
34 1000 16 1 10 0.076 0.11 68 102 141 -44.74%
41 1000 32 1 10 0.153 0.19 158 206 269 -24.18%
48 1000 64 1 10 0.266 0.38 339 415 484 -42.86%
55 1000 128 1 10 0.536 0.723 716 832 928 -34.89%
62 1000 256 1 10 0.52 0.303 1619 1619 1619 41.73%
69 1000 512 1 10 0.916 0.61 3300 3300 3300 33.41%
76 1000 1150 1 10 2.096 1.37 7365 7365 7365 34.64%
83 1000 1 10 20 0.006 0.01 10 15 20 -66.67%
90 1000 2 10 20 0.016 0.016 21 31 41 0.00%
97 1000 4 10 20 0.04 0.043 46 62 81 -7.50%
104 1000 8 10 20 0.07 0.076 102 126 150 -8.57%
111 1000 16 10 20 0.133 0.143 220 254 300 -7.52%
118 1000 32 10 20 0.263 0.293 450 510 571 -11.41%
125 1000 64 10 20 0.526 0.56 935 1023 1116 -6.46%
132 1000 128 10 20 1.043 1.156 1943 2046 2156 -10.83%
139 1000 256 10 20 0.593 0.343 4107 4107 4107 42.16%
146 1000 480 10 20 1.053 0.593 7674 7674 7674 43.68%
153 1000 1 20 30 0.01 0.01 20 25 30 0.00%
160 1000 2 20 30 0.023 0.02 41 51 61 13.04%
167 1000 4 20 30 0.053 0.053 86 102 120 0.00%
174 1000 8 20 30 0.1 0.103 179 207 237 -3.00%
181 1000 16 20 30 0.196 0.19 378 414 453 3.06%
188 1000 32 20 30 0.39 0.383 773 830 880 1.79%
195 1000 64 20 30 0.766 0.743 1586 1662 1738 3.00%
202 1000 128 20 30 1.533 1.533 3234 3327 3432 0.00%
209 1000 256 20 30 0.673 0.326 6650 6650 6650 51.56%
216 1000 290 20 30 0.723 0.36 7490 7490 7490 50.21%
223 1000 1 30 40 0.01 0.01 30 35 40 0.00%
230 1000 2 30 40 0.023 0.023 61 71 81 0.00%
237 1000 4 30 40 0.073 0.063 124 142 161 13.70%
244 1000 8 30 40 0.13 0.12 259 286 312 7.69%
251 1000 16 30 40 0.256 0.24 525 575 621 6.25%
258 1000 32 30 40 0.513 0.486 1095 1150 1216 5.26%
265 1000 64 30 40 1.02 0.926 2224 2302 2385 9.22%
272 1000 128 30 40 2.036 1.863 4490 4608 4733 8.50%
279 1000 210 30 40 0.63 0.283 7553 7553 7553 55.08%
286 1000 1 40 50 0.01 0.01 40 45 50 0.00%
293 1000 2 40 50 0.023 0.02 81 91 101 13.04%
300 1000 4 40 50 0.096 0.086 166 182 200 10.42%
307 1000 8 40 50 0.166 0.15 342 366 392 9.64%
314 1000 16 40 50 0.323 0.293 696 735 775 9.29%
321 1000 32 40 50 0.63 0.553 1410 1470 1526 12.22%
328 1000 64 40 50 1.286 1.096 2859 2943 3015 14.77%
335 1000 128 40 50 2.573 2.153 5780 5886 5994 16.32%
342 1000 165 40 50 3.143 2.77 7465 7589 7743 11.87%
25.809 21.896 15.16%



Post #1561718
Posted Monday, April 14, 2014 9:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 35,827, Visits: 32,499
Thanks. I'll check it out.

BTW... you know you can attach spreadsheets to a post, right?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561724
Posted Monday, April 14, 2014 9:12 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 9,928, Visits: 11,208
At the risk of making a predictable contribution at this point, based on Miller's stated requirements for long Unicode strings and multi-character delimiters:

CREATE ASSEMBLY [MultiSplit]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300E3DEE24F0000000000000000E00002210B010800000E00000006000000000000FE2C0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000A42C000057000000004000009003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000040D000000200000000E000000020000000000000000000000000000200000602E7273726300000090030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000E02C000000000000480000000200050054220000500A00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D2026F1000000A2D1A036F1000000A2D12026F1100000A036F1100000A73050000062A168D15000001168D1500000173050000062A000000133002002A000000010000110274030000020A03067B01000004281200000A810400000104067B02000004281300000A81050000012A1E02281400000A2A5602281400000A02037D0100000402047D020000042A03300300530000000000000002281400000A02037D0600000402038E697D0800000402047D0700000402048E69D27D0900000402027B0900000417FE017D0A00000402167D0500000402167D0300000402027B09000004155A7D040000042AAA027B05000004027B06000004027B03000004027B04000004027B0300000459731500000A73040000062A000013300300C90000000200001102257B0500000417587D05000004027B04000004027B080000043202162A02027B04000004027B09000004587D03000004027B030000040A2B65170B027B0A0000042C16027B060000040693027B0700000416932E39160B2B35160C2B28060858027B080000042F14027B0600000406085893027B0700000408932E04160B2B0E081758D20C08027B0900000432CF072C0902067D04000004172A0617580A06027B08000004329202027B04000004027B09000004587D0300000402027B080000047D04000004172A5A02167D0300000402027B09000004155A7D040000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000002C030000237E000098030000A803000023537472696E6773000000004007000008000000235553004807000010000000234755494400000058070000F802000023426C6F6200000000000000020000015717A2010902000000FA2533001600000100000016000000040000000A000000080000000900000001000000150000000D0000000200000001000000010000000100000001000000020000000200000000000A0001000000000006005C0055000600760063000A00A3008E000A00BE008E000A00C7008E000A00840169010600C301A4010600FA01E80106001102E80106002E02E80106004D02E80106006602E80106007F02E80106009A02E8010600B502E8010600CE02A4010600E202E80106001B03FB0206003B03FB020A006403690106008E03550006009F03550000000000010000000000010001000100100019000000050001000100030110002E000000050001000400020110003B0000000500030005002300E50022002300EE00250001000E0122000100160122000100E500220021001E013E00210028013E0021003201220021003C0142002100490145005020000000009600AC000A0001008820000000009600D10013000300BE20000000008618DF001E000600C620000000008618DF0028000600DC20000000008618DF002E0008003B2100000000E609F30036000A00682100000000E601FF003A000A003D2200000000E60108011E000A0000000100630100000200960100000100A00102000200E50002000300EE0000000100D00100000200D90100000100DE01000002009601040009003100DF001E003900DF001E004100DF00B0004900DF00B0005100DF00B0005900DF00B0006100DF00B0006900DF00B0007100DF00B0007900DF00B0008100DF00B5008900DF00B0009100DF00BA009900DF001E00A100DF001E00190079033A00190084036302210093036802290093036E020900DF001E00B100DF00790220007B00BF0024000B004C002E0033009D022E001B0087022E00230097022E002B0097022E004B0097022E007300D6022E004300AC022E003B0087022E005B0097022E006B00CD0244000B007E00740281020400010000005B0148000200060003000480000001000000CB11D9980000000000005903000002000000000000000000000001004C0000000000020000000000000000000000010082000000000003000200040002000000003C4D6F64756C653E004D756C746953706C69742E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F72640053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C496E7433320053716C537472696E670046696C6C526F775F4D756C7469002E63746F720073657175656E6365006974656D006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E7400496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053657175656E6365004974656D00546865537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D756C746953706C69740053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172006F705F496D706C6963697400537472696E670000000003200000000000A3AEF14706753B429B464E1C5D2862700008B77A5C561934E0890800021209120D120D0A0003011C1011111011150320000102060802060E05200201080E072002011D031D030320001C0320000203061D030206050206020328001C31010003005408074D617853697A65FFFFFFFF54020D497346697865644C656E6774680054020A49734E756C6C61626C650031010003005408074D617853697A65FF00000054020D497346697865644C656E6774680054020A49734E756C6C61626C6500042001010E0420010102042001010881A201000600540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C74695455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E0F5461626C65446566696E6974696F6E274974656D4E756D62657220696E74656765722C204974656D206E766172636861722834303030290420001D0305000111110805000111150E040701120C072003011D0308080507030802050F01000A4D756C746953706C697400000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313200000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000CC2C00000000000000000000EE2C0000002000000000000000000000000000000000000000000000E02C00000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100D998CB1100000100D998CB113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F0066007400000040000B000100460069006C0065004400650073006300720069007000740069006F006E00000000004D0075006C0074006900530070006C00690074000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003500350035002E00330039003100320039000000000040000F00010049006E007400650072006E0061006C004E0061006D00650000004D0075006C0074006900530070006C00690074002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310032000000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004D0075006C0074006900530070006C00690074002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D006500000000004D0075006C0074006900530070006C00690074000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003500350035002E00330039003100320039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003500350035002E00330039003100320039000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000003D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[SplitString_Multi]
(
@Input nvarchar(max),
@Delimiter nvarchar(255)
)
RETURNS TABLE
(
[ItemNumber] integer NULL,
[Item] nvarchar(4000) NULL
)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [MultiSplit].[UserDefinedFunctions].[SplitString_Multi];
GO
SELECT
Split.ItemNumber,
Split.Item
FROM dbo.SplitString_Multi(N'SQLxxCLRxxISxxCOOL', N'xx') AS Split;

Output:
ItemNumber	Item
1 SQL
2 CLR
3 IS
4 COOL


Source code:
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[SqlFunction
(
FillRowMethodName = "FillRow_Multi",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true,
TableDefinition = "ItemNumber integer, Item nvarchar(4000)"
)
]
public static IEnumerator SplitString_Multi
(
[SqlFacet(MaxSize = -1, IsFixedLength = false, IsNullable = false)]
SqlChars Input,
[SqlFacet(MaxSize = 255, IsFixedLength = false, IsNullable = false)]
SqlChars Delimiter
)
{
return
(
(Input.IsNull || Delimiter.IsNull) ?
new SplitStringMulti(new char[0], new char[0]) :
new SplitStringMulti(Input.Value, Delimiter.Value));
}

private sealed class OutputRecord
{
internal readonly int sequence;
internal readonly string item;

public OutputRecord(int Sequence, string Item)
{
this.sequence = Sequence;
this.item = Item;
}
}

public static void FillRow_Multi(object obj, out SqlInt32 sequence, out SqlString item)
{
OutputRecord r = (OutputRecord)obj;
sequence = r.sequence;
item = r.item;
}

public sealed class SplitStringMulti : IEnumerator
{
public SplitStringMulti(char[] TheString, char[] Delimiter)
{
theString = TheString;
stringLen = TheString.Length;
delimiter = Delimiter;
delimiterLen = (byte)(Delimiter.Length);
isSingleCharDelim = (delimiterLen == 1);

sequence = 0;
lastPos = 0;
nextPos = delimiterLen * -1;
}

#region IEnumerator Members

public object Current
{
get
{
return new OutputRecord(sequence, new string(theString, lastPos, nextPos - lastPos));
}
}

public bool MoveNext()
{
sequence++;

if (nextPos >= stringLen)
return false;
else
{
lastPos = nextPos + delimiterLen;

for (int i = lastPos; i < stringLen; i++)
{
bool matches = true;

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)
{
nextPos = i;
return true;
}
}

lastPos = nextPos + delimiterLen;
nextPos = stringLen;

return true;
}
}

public void Reset()
{
lastPos = 0;
nextPos = delimiterLen * -1;
}

#endregion

private int lastPos;
private int nextPos;
private int sequence;

private readonly char[] theString;
private readonly char[] delimiter;
private readonly int stringLen;
private readonly byte delimiterLen;
private readonly bool isSingleCharDelim;
}
};

Based on http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1561727
Posted Monday, April 14, 2014 10:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 35,827, Visits: 32,499
I agree. A CLR would be the best way and you made it easy enough for everyone to use. Thanks. The "Split" function in the original article was much faster than any of the T-SQL methods.

The only thing I don't like about the one that Adam posted is that it ignores leading/trailing and consecutive delimiters. For the kind of work I do, those are important especially for "ragged right" splits.

The link to Adam's article is broken. Here it is again...
http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561739
Posted Monday, April 14, 2014 11:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 9,928, Visits: 11,208
Jeff Moden (4/14/2014)
I agree. A CLR would be the best way and you made it easy enough for everyone to use. Thanks. The "Split" function in the original article was much faster than any of the T-SQL methods.

I don't know for sure that the "max" version with multi-character delimiters I posted will beat Miller's method, but it's there if (s)he wants to try it.

The only thing I don't like about the one that Adam posted is that it ignores leading/trailing and consecutive delimiters. For the kind of work I do, those are important especially for "ragged right" splits.

I think we had this conversation a long time ago, which lead me to revise it. The version I presented works as you would wish, I think.


Works for me, and I haven't edited it? No worries either way.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1561746
Posted Monday, April 14, 2014 11:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 2,550, Visits: 7,167
FYI: Ran the function through the test script with the same result aggregation as I used for the article.


SplitterName                 TOTAL_D      AVG_D
---------------------------- ------------ -----------
DelimitedSplit8K_T1_LEAD_V2 18302.00000 373.510204
DelimitedSplit8K_T1_LEAD 18587.00000 379.326530
BetterSplit 20307.00000 414.428571
Post #1561748
Posted Tuesday, April 15, 2014 4:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:42 AM
Points: 337, Visits: 2,283
Paul White (4/14/2014)
At the risk of making a predictable contribution at this point, based on Miller's stated requirements for long Unicode strings and multi-character delimiters:

.
.
.

Based on http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx


This version uses .net to supply the sub-strings, an approach so far I seen all splitters take.

A while back I wrote one that just delivers locations of the sub-strings back to SQL. The idea being that the .NET to SQL interface is leaner and would allow SQL Server to be smarter in its memory usage by for example utilizing partial strings internally, instead of fresh copies. That would hardly consume additional memory. For sure the resulting datatype and collation would be in-line with the original input, something which no other version i seen does. It also means no conversions from the a static Unicode result that .NET delivers are needed when parsing varchar input (saving both memory and time).

I never got to run benchmark tests for it as it is part of a large more complete lib which I haven't even fully designed and finished. Too bad I ran out of time and had to focus on other things, but it will be done at one point. I think the standard way of splitting has room to be improved upon, efficiency wise.

Post #1561800
Posted Tuesday, April 15, 2014 8:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 22, 2014 11:13 AM
Points: 67, Visits: 68
It is unfortunate that this that there are some seemingly inexplicable limitations in what you can do with T-SQL.
For example, I wondered what would happen if we were able to removed all usage of SUBSTRING and simply convert the delimited string into scripted data with a single execution of the REPLACE function, then execute the dynamic sql to generate the resultant table. This works great and is incredibly fast, but for several reasons, this will not work as a function, only as a stored procedure.
Below is a procedure that does the split with no tally and no substring, no charindex.

CREATE procedure [dbo].[TallyNo]
(@L NVARCHAR(MAX),@D NVARCHAR(100))AS

DECLARE @E VARCHAR(MAX)
SET @E='SELECT ItemNumber=ROW_NUMBER()OVER(ORDER BY(SELECT 1)),Item FROM(values('''+REPLACE(@L,@D,'''),(''')+'''))V(Item)'
EXEC(@E);



Post #1562096
Posted Saturday, April 26, 2014 12:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 35,827, Visits: 32,499
Paul White (4/14/2014)
Jeff Moden (4/14/2014)
I agree. A CLR would be the best way and you made it easy enough for everyone to use. Thanks. The "Split" function in the original article was much faster than any of the T-SQL methods.

I don't know for sure that the "max" version with multi-character delimiters I posted will beat Miller's method, but it's there if (s)he wants to try it.

The only thing I don't like about the one that Adam posted is that it ignores leading/trailing and consecutive delimiters. For the kind of work I do, those are important especially for "ragged right" splits.

I think we had this conversation a long time ago, which lead me to revise it. The version I presented works as you would wish, I think.


Works for me, and I haven't edited it? No worries either way.


Apologies for the late reply.

We did indeed and thank you very much for not-only tweaking the CLR, Paul, but for offering it up to begin with and for making it easy to instantiate. I don't even know how to spell C# and couldn't have done it on my own. You really came through on that and made an essential comparison possible. Thank you very much, again!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1565352
« Prev Topic | Next Topic »

Add to briefcase «««5758596061»»»

Permissions Expand / Collapse