Tally OH! An Improved SQL 8K “CSV Splitter” Function
-
mtassin (5/3/2011)
Craig Farrell (5/3/2011)
WayneS (5/3/2011)
Here's the results that I have. The "DemilitedSplit8Kpt" is the new DelimitedSplit8K function, with a permanent tally table.10 to 20 Characters per Element:
B'narg! Whaaaa? After a point I'd have expected the Perm table to stabilize more to the memory build.
Wow.
Yah... I'm now going to have to create a view named tally that does it with CTE and then quietly drop the tally table I fought so hard to put into my db.
Oh... be real careful. Most folks that have unit based Tally Tables also has code in the WHERE clause that looks like the following (for example)...
WHERE t.N < LEN(somestring)
If you create a 0 based Tally Table to replace it with, all of that code will need to be converted to (for example)...
WHERE t.N >= 1 AND < LEN(somestring)
--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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
WayneS (5/3/2011)
Why don't you take your functions, integrate them into the test script included in the References section of the article, run the tests, and post the results for all of us to enjoy?All right, but I'm not much with the fancy graphs, so here's the output exported as a csv (I don't know what the forum would do to tabs)
1,SplitRCte,1000,1,1,10,0.01600,1,5,10
2,SplitXML,1000,1,1,10,0.14000,1,5,10
3,DelimitedSplit8K,1000,1,1,10,0.01600,1,5,10
4,DelimitedSplit8KPerm0Based,1000,1,1,10,0.01600,1,5,10
5,DelimitedSplit8KPermUnion,1000,1,1,10,0.00600,1,5,10
6,SplitRCte,1000,2,1,10,0.04300,3,11,21
7,SplitXML,1000,2,1,10,0.18300,3,11,21
8,DelimitedSplit8K,1000,2,1,10,0.02000,3,11,21
9,DelimitedSplit8KPerm0Based,1000,2,1,10,0.01600,3,11,21
10,DelimitedSplit8KPermUnion,1000,2,1,10,0.01600,3,11,21
11,SplitRCte,1000,4,1,10,0.08300,8,24,40
12,SplitXML,1000,4,1,10,0.22000,8,24,40
13,DelimitedSplit8K,1000,4,1,10,0.06600,8,24,40
14,DelimitedSplit8KPerm0Based,1000,4,1,10,0.02000,8,24,40
15,DelimitedSplit8KPermUnion,1000,4,1,10,0.07600,8,24,40
16,SplitRCte,1000,8,1,10,0.14300,22,51,75
17,SplitXML,1000,8,1,10,0.27300,22,51,75
18,DelimitedSplit8K,1000,8,1,10,0.11600,22,51,75
19,DelimitedSplit8KPerm0Based,1000,8,1,10,0.10300,22,51,75
20,DelimitedSplit8KPermUnion,1000,8,1,10,0.12000,22,51,75
21,SplitRCte,1000,16,1,10,0.27000,63,102,138
22,SplitXML,1000,16,1,10,0.37600,63,102,138
23,DelimitedSplit8K,1000,16,1,10,0.07300,63,102,138
24,DelimitedSplit8KPerm0Based,1000,16,1,10,0.07600,63,102,138
25,DelimitedSplit8KPermUnion,1000,16,1,10,0.06600,63,102,138
26,SplitRCte,1000,32,1,10,0.53000,148,207,264
27,SplitXML,1000,32,1,10,0.58000,148,207,264
28,DelimitedSplit8K,1000,32,1,10,0.12300,148,207,264
29,DelimitedSplit8KPerm0Based,1000,32,1,10,0.12300,148,207,264
30,DelimitedSplit8KPermUnion,1000,32,1,10,0.12000,148,207,264
31,SplitRCte,1000,64,1,10,1.03600,348,415,486
32,SplitXML,1000,64,1,10,0.98600,348,415,486
33,DelimitedSplit8K,1000,64,1,10,0.23600,348,415,486
34,DelimitedSplit8KPerm0Based,1000,64,1,10,0.22000,348,415,486
35,DelimitedSplit8KPermUnion,1000,64,1,10,0.22600,348,415,486
36,SplitRCte,1000,128,1,10,2.06000,740,829,918
37,SplitXML,1000,128,1,10,1.79600,740,829,918
38,DelimitedSplit8K,1000,128,1,10,0.46000,740,829,918
39,DelimitedSplit8KPerm0Based,1000,128,1,10,0.41600,740,829,918
40,DelimitedSplit8KPermUnion,1000,128,1,10,0.44600,740,829,918
41,SplitRCte,1000,256,1,10,4.10600,1527,1664,1801
42,SplitXML,1000,256,1,10,3.43300,1527,1664,1801
43,DelimitedSplit8K,1000,256,1,10,0.90300,1527,1664,1801
44,DelimitedSplit8KPerm0Based,1000,256,1,10,0.81000,1527,1664,1801
45,DelimitedSplit8KPermUnion,1000,256,1,10,0.88000,1527,1664,1801
46,SplitRCte,1000,512,1,10,8.19000,3126,3327,3521
47,SplitXML,1000,512,1,10,6.82300,3126,3327,3521
48,DelimitedSplit8K,1000,512,1,10,1.79300,3126,3327,3521
49,DelimitedSplit8KPerm0Based,1000,512,1,10,1.58600,3126,3327,3521
50,DelimitedSplit8KPermUnion,1000,512,1,10,1.74000,3126,3327,3521
51,SplitRCte,1000,1150,1,10,18.46600,7167,7475,7804
52,SplitXML,1000,1150,1,10,15.41300,7167,7475,7804
53,DelimitedSplit8K,1000,1150,1,10,4.02600,7167,7475,7804
54,DelimitedSplit8KPerm0Based,1000,1150,1,10,3.52300,7167,7475,7804
55,DelimitedSplit8KPermUnion,1000,1150,1,10,3.80300,7167,7475,7804
56,SplitRCte,1000,1,10,20,0.02600,10,15,20
57,SplitXML,1000,1,10,20,0.14600,10,15,20
58,DelimitedSplit8K,1000,1,10,20,0.01300,10,15,20
59,DelimitedSplit8KPerm0Based,1000,1,10,20,0.01600,10,15,20
60,DelimitedSplit8KPermUnion,1000,1,10,20,0.01600,10,15,20
61,SplitRCte,1000,2,10,20,0.04600,21,30,41
62,SplitXML,1000,2,10,20,0.19000,21,30,41
63,DelimitedSplit8K,1000,2,10,20,0.02000,21,30,41
64,DelimitedSplit8KPerm0Based,1000,2,10,20,0.01600,21,30,41
65,DelimitedSplit8KPermUnion,1000,2,10,20,0.02300,21,30,41
66,SplitRCte,1000,4,10,20,0.08000,46,62,82
67,SplitXML,1000,4,10,20,0.22300,46,62,82
68,DelimitedSplit8K,1000,4,10,20,0.04000,46,62,82
69,DelimitedSplit8KPerm0Based,1000,4,10,20,0.05000,46,62,82
70,DelimitedSplit8KPermUnion,1000,4,10,20,0.08000,46,62,82
71,SplitRCte,1000,8,10,20,0.14300,99,126,152
72,SplitXML,1000,8,10,20,0.27600,99,126,152
73,DelimitedSplit8K,1000,8,10,20,0.06600,99,126,152
74,DelimitedSplit8KPerm0Based,1000,8,10,20,0.07000,99,126,152
75,DelimitedSplit8KPermUnion,1000,8,10,20,0.06300,99,126,152
76,SplitRCte,1000,16,10,20,0.27300,207,255,298
77,SplitXML,1000,16,10,20,0.38300,207,255,298
78,DelimitedSplit8K,1000,16,10,20,0.12000,207,255,298
79,DelimitedSplit8KPerm0Based,1000,16,10,20,0.12600,207,255,298
80,DelimitedSplit8KPermUnion,1000,16,10,20,0.11000,207,255,298
81,SplitRCte,1000,32,10,20,0.53300,446,511,563
82,SplitXML,1000,32,10,20,0.59300,446,511,563
83,DelimitedSplit8K,1000,32,10,20,0.22000,446,511,563
84,DelimitedSplit8KPerm0Based,1000,32,10,20,0.20600,446,511,563
85,DelimitedSplit8KPermUnion,1000,32,10,20,0.21600,446,511,563
86,SplitRCte,1000,64,10,20,1.05000,940,1024,1110
87,SplitXML,1000,64,10,20,1.02000,940,1024,1110
88,DelimitedSplit8K,1000,64,10,20,0.48600,940,1024,1110
89,DelimitedSplit8KPerm0Based,1000,64,10,20,0.38300,940,1024,1110
90,DelimitedSplit8KPermUnion,1000,64,10,20,0.40000,940,1024,1110
91,SplitRCte,1000,128,10,20,2.10600,1920,2045,2160
92,SplitXML,1000,128,10,20,1.86300,1920,2045,2160
93,DelimitedSplit8K,1000,128,10,20,0.85600,1920,2045,2160
94,DelimitedSplit8KPerm0Based,1000,128,10,20,0.75000,1920,2045,2160
95,DelimitedSplit8KPermUnion,1000,128,10,20,0.77600,1920,2045,2160
96,SplitRCte,1000,256,10,20,4.18000,3934,4098,4240
97,SplitXML,1000,256,10,20,3.63000,3934,4098,4240
98,DelimitedSplit8K,1000,256,10,20,1.68600,3934,4098,4240
99,DelimitedSplit8KPerm0Based,1000,256,10,20,1.47000,3934,4098,4240
100,DelimitedSplit8KPermUnion,1000,256,10,20,1.56600,3934,4098,4240
101,SplitRCte,1000,480,10,20,7.82000,7450,7677,7900
102,SplitXML,1000,480,10,20,6.69000,7450,7677,7900
103,DelimitedSplit8K,1000,480,10,20,3.13600,7450,7677,7900
104,DelimitedSplit8KPerm0Based,1000,480,10,20,2.72300,7450,7677,7900
105,DelimitedSplit8KPermUnion,1000,480,10,20,2.93000,7450,7677,7900
106,SplitRCte,1000,1,20,30,0.03000,20,25,30
107,SplitXML,1000,1,20,30,0.15000,20,25,30
108,DelimitedSplit8K,1000,1,20,30,0.01600,20,25,30
109,DelimitedSplit8KPerm0Based,1000,1,20,30,0.01300,20,25,30
110,DelimitedSplit8KPermUnion,1000,1,20,30,0.01300,20,25,30
111,SplitRCte,1000,2,20,30,0.04600,41,51,61
112,SplitXML,1000,2,20,30,0.18600,41,51,61
113,DelimitedSplit8K,1000,2,20,30,0.02600,41,51,61
114,DelimitedSplit8KPerm0Based,1000,2,20,30,0.02000,41,51,61
115,DelimitedSplit8KPermUnion,1000,2,20,30,0.02300,41,51,61
116,SplitRCte,1000,4,20,30,0.08600,85,102,122
117,SplitXML,1000,4,20,30,0.22000,85,102,122
118,DelimitedSplit8K,1000,4,20,30,0.05600,85,102,122
119,DelimitedSplit8KPerm0Based,1000,4,20,30,0.05600,85,102,122
120,DelimitedSplit8KPermUnion,1000,4,20,30,0.05000,85,102,122
121,SplitRCte,1000,8,20,30,0.14600,178,206,237
122,SplitXML,1000,8,20,30,0.28000,178,206,237
123,DelimitedSplit8K,1000,8,20,30,0.09000,178,206,237
124,DelimitedSplit8KPerm0Based,1000,8,20,30,0.09300,178,206,237
125,DelimitedSplit8KPermUnion,1000,8,20,30,0.08300,178,206,237
126,SplitRCte,1000,16,20,30,0.28000,378,415,452
127,SplitXML,1000,16,20,30,0.39000,378,415,452
128,DelimitedSplit8K,1000,16,20,30,0.17000,378,415,452
129,DelimitedSplit8KPerm0Based,1000,16,20,30,0.15600,378,415,452
130,DelimitedSplit8KPermUnion,1000,16,20,30,0.15300,378,415,452
131,SplitRCte,1000,32,20,30,0.54300,766,830,885
132,SplitXML,1000,32,20,30,0.60600,766,830,885
133,DelimitedSplit8K,1000,32,20,30,0.32300,766,830,885
134,DelimitedSplit8KPerm0Based,1000,32,20,30,0.29000,766,830,885
135,DelimitedSplit8KPermUnion,1000,32,20,30,0.30000,766,830,885
136,SplitRCte,1000,64,20,30,1.07300,1578,1662,1735
137,SplitXML,1000,64,20,30,1.04300,1578,1662,1735
138,DelimitedSplit8K,1000,64,20,30,0.63000,1578,1662,1735
139,DelimitedSplit8KPerm0Based,1000,64,20,30,0.56300,1578,1662,1735
140,DelimitedSplit8KPermUnion,1000,64,20,30,0.57600,1578,1662,1735
141,SplitRCte,1000,128,20,30,2.10300,3214,3326,3452
142,SplitXML,1000,128,20,30,1.92000,3214,3326,3452
143,DelimitedSplit8K,1000,128,20,30,1.25300,3214,3326,3452
144,DelimitedSplit8KPerm0Based,1000,128,20,30,1.08600,3214,3326,3452
145,DelimitedSplit8KPermUnion,1000,128,20,30,1.15600,3214,3326,3452
146,SplitRCte,1000,256,20,30,4.21000,6503,6656,6813
147,SplitXML,1000,256,20,30,3.74600,6503,6656,6813
148,DelimitedSplit8K,1000,256,20,30,2.49600,6503,6656,6813
149,DelimitedSplit8KPerm0Based,1000,256,20,30,2.15300,6503,6656,6813
150,DelimitedSplit8KPermUnion,1000,256,20,30,2.28600,6503,6656,6813
151,SplitRCte,1000,290,20,30,4.77600,7379,7540,7694
152,SplitXML,1000,290,20,30,4.23000,7379,7540,7694
153,DelimitedSplit8K,1000,290,20,30,2.82300,7379,7540,7694
154,DelimitedSplit8KPerm0Based,1000,290,20,30,2.43600,7379,7540,7694
155,DelimitedSplit8KPermUnion,1000,290,20,30,2.58000,7379,7540,7694
156,SplitRCte,1000,1,30,40,0.03000,30,34,40
157,SplitXML,1000,1,30,40,0.15600,30,34,40
158,DelimitedSplit8K,1000,1,30,40,0.01600,30,34,40
159,DelimitedSplit8KPerm0Based,1000,1,30,40,0.01300,30,34,40
160,DelimitedSplit8KPermUnion,1000,1,30,40,0.01600,30,34,40
161,SplitRCte,1000,2,30,40,0.04600,61,70,81
162,SplitXML,1000,2,30,40,0.19000,61,70,81
163,DelimitedSplit8K,1000,2,30,40,0.02600,61,70,81
164,DelimitedSplit8KPerm0Based,1000,2,30,40,0.02300,61,70,81
165,DelimitedSplit8KPermUnion,1000,2,30,40,0.02600,61,70,81
166,SplitRCte,1000,4,30,40,0.08300,125,142,161
167,SplitXML,1000,4,30,40,0.22600,125,142,161
168,DelimitedSplit8K,1000,4,30,40,0.06600,125,142,161
169,DelimitedSplit8KPerm0Based,1000,4,30,40,0.06600,125,142,161
170,DelimitedSplit8KPermUnion,1000,4,30,40,0.06300,125,142,161
171,SplitRCte,1000,8,30,40,0.15000,260,286,313
172,SplitXML,1000,8,30,40,0.28300,260,286,313
173,DelimitedSplit8K,1000,8,30,40,0.11600,260,286,313
174,DelimitedSplit8KPerm0Based,1000,8,30,40,0.11300,260,286,313
175,DelimitedSplit8KPermUnion,1000,8,30,40,0.11000,260,286,313
176,SplitRCte,1000,16,30,40,0.28300,539,575,614
177,SplitXML,1000,16,30,40,0.39300,539,575,614
178,DelimitedSplit8K,1000,16,30,40,0.22300,539,575,614
179,DelimitedSplit8KPerm0Based,1000,16,30,40,0.20300,539,575,614
180,DelimitedSplit8KPermUnion,1000,16,30,40,0.19600,539,575,614
181,SplitRCte,1000,32,30,40,0.55300,1099,1151,1214
182,SplitXML,1000,32,30,40,0.62000,1099,1151,1214
183,DelimitedSplit8K,1000,32,30,40,0.42600,1099,1151,1214
184,DelimitedSplit8KPerm0Based,1000,32,30,40,0.37600,1099,1151,1214
185,DelimitedSplit8KPermUnion,1000,32,30,40,0.38600,1099,1151,1214
186,SplitRCte,1000,64,30,40,1.08000,2225,2303,2379
187,SplitXML,1000,64,30,40,1.07300,2225,2303,2379
188,DelimitedSplit8K,1000,64,30,40,0.83600,2225,2303,2379
189,DelimitedSplit8KPerm0Based,1000,64,30,40,0.73300,2225,2303,2379
190,DelimitedSplit8KPermUnion,1000,64,30,40,0.76300,2225,2303,2379
191,SplitRCte,1000,128,30,40,2.18300,4499,4608,4721
192,SplitXML,1000,128,30,40,1.98600,4499,4608,4721
193,DelimitedSplit8K,1000,128,30,40,1.66300,4499,4608,4721
194,DelimitedSplit8KPerm0Based,1000,128,30,40,1.43300,4499,4608,4721
195,DelimitedSplit8KPermUnion,1000,128,30,40,1.49300,4499,4608,4721
196,SplitRCte,1000,210,30,40,3.54300,7411,7558,7749
197,SplitXML,1000,210,30,40,3.20000,7411,7558,7749
198,DelimitedSplit8K,1000,210,30,40,2.72600,7411,7558,7749
199,DelimitedSplit8KPerm0Based,1000,210,30,40,2.33300,7411,7558,7749
200,DelimitedSplit8KPermUnion,1000,210,30,40,2.46300,7411,7558,7749
201,SplitRCte,1000,1,40,50,0.03000,40,44,50
202,SplitXML,1000,1,40,50,0.15600,40,44,50
203,DelimitedSplit8K,1000,1,40,50,0.01600,40,44,50
204,DelimitedSplit8KPerm0Based,1000,1,40,50,0.01300,40,44,50
205,DelimitedSplit8KPermUnion,1000,1,40,50,0.01600,40,44,50
206,SplitRCte,1000,2,40,50,0.04600,81,91,101
207,SplitXML,1000,2,40,50,0.19300,81,91,101
208,DelimitedSplit8K,1000,2,40,50,0.03000,81,91,101
209,DelimitedSplit8KPerm0Based,1000,2,40,50,0.02300,81,91,101
210,DelimitedSplit8KPermUnion,1000,2,40,50,0.03000,81,91,101
211,SplitRCte,1000,4,40,50,0.08300,165,182,201
212,SplitXML,1000,4,40,50,0.22600,165,182,201
213,DelimitedSplit8K,1000,4,40,50,0.08000,165,182,201
214,DelimitedSplit8KPerm0Based,1000,4,40,50,0.08000,165,182,201
215,DelimitedSplit8KPermUnion,1000,4,40,50,0.08000,165,182,201
216,SplitRCte,1000,8,40,50,0.15000,344,366,394
217,SplitXML,1000,8,40,50,0.28600,344,366,394
218,DelimitedSplit8K,1000,8,40,50,0.14000,344,366,394
219,DelimitedSplit8KPerm0Based,1000,8,40,50,0.13600,344,366,394
220,DelimitedSplit8KPermUnion,1000,8,40,50,0.12600,344,366,394
221,SplitRCte,1000,16,40,50,0.29000,689,735,788
222,SplitXML,1000,16,40,50,0.40000,689,735,788
223,DelimitedSplit8K,1000,16,40,50,0.27300,689,735,788
224,DelimitedSplit8KPerm0Based,1000,16,40,50,0.24300,689,735,788
225,DelimitedSplit8KPermUnion,1000,16,40,50,0.24300,689,735,788
226,SplitRCte,1000,32,40,50,0.55600,1419,1471,1519
227,SplitXML,1000,32,40,50,0.63300,1419,1471,1519
228,DelimitedSplit8K,1000,32,40,50,0.52600,1419,1471,1519
229,DelimitedSplit8KPerm0Based,1000,32,40,50,0.46000,1419,1471,1519
230,DelimitedSplit8KPermUnion,1000,32,40,50,0.48600,1419,1471,1519
231,SplitRCte,1000,64,40,50,1.11000,2871,2943,3019
232,SplitXML,1000,64,40,50,1.09000,2871,2943,3019
233,DelimitedSplit8K,1000,64,40,50,1.04300,2871,2943,3019
234,DelimitedSplit8KPerm0Based,1000,64,40,50,0.90300,2871,2943,3019
235,DelimitedSplit8KPermUnion,1000,64,40,50,0.93600,2871,2943,3019
236,SplitRCte,1000,128,40,50,2.20600,5742,5885,6002
237,SplitXML,1000,128,40,50,2.05000,5742,5885,6002
238,DelimitedSplit8K,1000,128,40,50,2.07000,5742,5885,6002
239,DelimitedSplit8KPerm0Based,1000,128,40,50,1.78300,5742,5885,6002
240,DelimitedSplit8KPermUnion,1000,128,40,50,1.84600,5742,5885,6002
241,SplitRCte,1000,165,40,50,2.81600,7465,7590,7723
242,SplitXML,1000,165,40,50,2.62000,7465,7590,7723
243,DelimitedSplit8K,1000,165,40,50,2.66600,7465,7590,7723
244,DelimitedSplit8KPerm0Based,1000,165,40,50,2.28600,7465,7590,7723
245,DelimitedSplit8KPermUnion,1000,165,40,50,2.38300,7465,7590,7723
Use your splitter of choice to return it to table form!
-
Thanks for the great article Jeff!
Eagerly awaiting the varchar(max) versions so I can incorporate them into my databases. 🙂
Does anyone have a theory as to why the CTE beats the permanent table?
-
The multi CTE temporary tally does not beat a permanent table.
A permanent tally table with a single, simplified CTE loses to the multi CTE temporary tally table.
A permanent temp table with a double CTE performs better than the multi CTE temporary tally table.
Something in the simplification is causing the performance issue; not the change from temporary to permanent. See my post just above.
-
Please post your code for this multi cte temporary tally table that outperforms the others. I am sure several others would like to test it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (5/3/2011)
Please post your code for this multi cte temporary tally table that outperforms the others. I am sure several others would like to test it.The multi cte temporary tally table is Jeff's from the article.
The three permanent tally table versions are already in my posts farther up this thread.
-
Nadrek (5/3/2011)
SQLRNNR (5/3/2011)
Please post your code for this multi cte temporary tally table that outperforms the others. I am sure several others would like to test it.The multi cte temporary tally table is Jeff's from the article.
The three permanent tally table versions are already in my posts farther up this thread.
Intriguing. The multi-cte method that Jeff uses has always outperformed a permanent talley table in all of the tests I have seen done on it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Roy Ernest (5/3/2011)
As usual, great post Jeff. Five star article.. 🙂Thanks, Roy. I appreciate you stopping by. How are you doing after that last car crunch?
--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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Nadrek (5/3/2011)
Craig Farrell (5/3/2011)
B'narg! Whaaaa? After a point I'd have expected the Perm table to stabilize more to the memory build.
Wow.
Try one of the two double-CTE versions I posted above (post 1102460); the single CTE simplification does something very bad.
http://qa.sqlservercentral.com/Forums/FindPost1102460.aspx
These do beat the temporary tally table version by ~13-15%.
Interesting. Would you mind adding your two function to the test code I wrote and posting the results? Thanks.
--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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
JJ B (5/3/2011)
From Jeff Moden: I was considering writing an article about the all-too-"secret" characters 28-31 and a couple of other goodies in "control character land". Whatcha tink? Worthwhile or not?
Yes! I'd be very interested. There's one vote anyway.
JJ, your vote counts plenty with me. I'll start thinking about how to put one together. This will fit right in with what Phil Factor was talking about in one of his editorials..
--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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
WayneS (5/3/2011)
Here's the results that I have. The "DemilitedSplit8Kpt" is the new DelimitedSplit8K function, with a permanent tally table.It's true... a picture IS worth a thousand words. Thanks for the time in putting those up, Wayne.
--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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Nadrek (5/3/2011)
WayneS (5/3/2011)
Why don't you take your functions, integrate them into the test script included in the References section of the article, run the tests, and post the results for all of us to enjoy?All right, but I'm not much with the fancy graphs, so here's the output exported as a csv (I don't know what the forum would do to tabs)
Here's your data converted to "the fancy graphs".
10 to 20 elements
20 to 30 elements
Oh Jeff... looks like the Perm0Based is beating your newest, hottest code! (Man, I love it when the community comes together like this!)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
mtassin (5/3/2011)
WayneS (5/3/2011)
ChrisM@home (5/3/2011)
Jeff, please please please write a book, without changing your style. Give us all an opportunity to repay you for what you've done for us.+10!
Yes, please do write a book...
Oddly enough on the first of every month, I search Amazon for Jeff's name hoping to find a book for my kindle 🙂
You folks are too much. :blush: Thanks for the confidence. I actually started to work on a book, gosh, I guess it was two years ago. I got all balled up at work and also came down with Bronchitis that hung on for about 4 months and a bunch of other things and put writing asided. Tony Davis sent me an email last month asking if I could start again. Guess there's no time like the present. Now, all I have to do is convince Tony Davis the ChrisM@home is right... let me write it in my style... it'll be a whole lot easier for me to be me.:-P
--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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden (5/3/2011)
WayneS (5/3/2011)
Here's the results that I have. The "DemilitedSplit8Kpt" is the new DelimitedSplit8K function, with a permanent tally table.It's true... a picture IS worth a thousand words. Thanks for the time in putting those up, Wayne.
NP Jeff - always glad to help you out!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Jeff Moden (5/3/2011)
mtassin (5/3/2011)
WayneS (5/3/2011)
ChrisM@home (5/3/2011)
Jeff, please please please write a book, without changing your style. Give us all an opportunity to repay you for what you've done for us.+10!
Yes, please do write a book...
Oddly enough on the first of every month, I search Amazon for Jeff's name hoping to find a book for my kindle 🙂
You folks are too much. :blush: Thanks for the confidence. I actually started to work on a book, gosh, I guess it was two years ago. I got all balled up at work and also came down with Bronchitis that hung on for about 4 months and a bunch of other things and put writing asided. Tony Davis sent me an email last month asking if I could start again. Guess there's no time like the present. Now, all I have to do is convince Tony Davis the ChrisM@home is right... let me write it in my style... it'll be a whole lot easier for me to be me.:-P
Just send us Tony's email... we'll all help convince him!
Hmm... with Tony - does that mean it'll be out as one of his free e-books? Wow!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2
Viewing 15 posts - 106 through 120 (of 981 total)
You must be logged in to reply to this topic. Login to reply