May 23, 2015 at 2:05 am
mburbea (5/22/2015)
Sadly, you're a victim of an optimization bug that makes your test data much less random than it should be. Sql server 2012+ can treat newid as a runtime constant value when you use it through a view unless you use the undocumented traceflag 8690.
The randomness of the test set is of course not absolute but most certainly good enough for the purpose. It is however somewhat irrelevant to this discussion.
😎
DelimitedSplit8k still wins, but now its no longer an absolute bloodbath like the results original results suggested.
splitter duration
fn_split 4.848461
xmlsplit 23.662873
delimitedsplit8kb 13.29136
Whilst admiring the positive attitude, I cannot but disagree here as the results are an absolute slaughter. Here are i.e. the results from another test system where DelimitedSplit8K is more than 6x faster:
METHOD DURATION
------------------ -----------
DelimitedSplit8K 18570063
xmlsplit 115415601
May 23, 2015 at 9:36 am
I bring up the lack of randomness as it works in the favor of delimitedsplit8k, as the optimizer can basically get to reuse its row plans. (Running the test script I posted on github and removing the the traceflag will cause delimitedsplit to start doing very well against the clr splitter). The fact that you aren't clearing the buffercache also isn't help things. But as I said, I still recommend delimitedsplit8k, if you can't use CLR (and are processing <8k strings obviously).
As for the optimism, I'm just happy to have learned something new. This lag/lead trick can have potential uses in places where you need to generate a lob result in an ITF or View but can't come up with a good way to avoid recomputing the result over and over again. If you remove the lead inner table trick, the million row query will run for an insanely long amount of time.
May 23, 2015 at 11:51 am
mburbea (5/23/2015)
I bring up the lack of randomness as it works in the favor of delimitedsplit8k, as the optimizer can basically get to reuse its row plans. (Running the test script I posted on github and removing the the traceflag will cause delimitedsplit to start doing very well against the clr splitter). The fact that you aren't clearing the buffercache also isn't help things. But as I said, I still recommend delimitedsplit8k, if you can't use CLR (and are processing <8k strings obviously).As for the optimism, I'm just happy to have learned something new. This lag/lead trick can have potential uses in places where you need to generate a lob result in an ITF or View but can't come up with a good way to avoid recomputing the result over and over again. If you remove the lead inner table trick, the million row query will run for an insanely long amount of time.
So true, there is always a lesson to be learnd;-)
On Sql Server 2014, the lead will actually slow things down although it is faster than any 2012 T-SQL code.
😎
May 23, 2015 at 1:31 pm
mburbea (5/23/2015)
I bring up the lack of randomness as it works in the favor of delimitedsplit8k, as the optimizer can basically get to reuse its row plans.
I've found the exact opposite to be true especially if "grooved" data (delimiters in the same spot whether the data is different or not) or repeated data because it's WHILE loops that will try to reuse execution plans for every iteration. Such bad test data is what sometimes makes it appear that a WHILE loops or XML is faster than the methods used in DelimitedSplit8K and similar functionality.
That, notwithstanding, do you have a test data generator for this the you'd like us to use? If so, please post it.
--Jeff Moden
May 23, 2015 at 3:01 pm
mburbea (5/23/2015)
I bring up the lack of randomness as it works in the favor of delimitedsplit8k, as the optimizer can basically get to reuse its row plans.
Viewing the text statistics, I fail to agree to that, could you elaborate a little further on this? Below are the approx. 1275 different patterns although this analysis omits the length of each individual "word". Must say I struggle to understand how those patterns would favour one method over the other.
😎
TEXT_LENGTH NUM_WORDS INSTANCE_COUNT
----------- ----------- --------------
NULL NULL 1
0 0 5
1 0 23
2 0 153
2 1 25
3 0 128
3 1 54
3 2 7
4 0 95
4 1 79
4 2 7
4 3 1
5 0 84
5 1 69
5 2 10
5 3 4
6 0 89
6 1 67
6 2 20
6 3 5
6 4 2
7 0 58
7 1 74
7 2 42
7 3 8
7 4 4
8 0 48
8 1 86
8 2 40
8 3 6
8 4 3
9 0 45
9 1 66
9 2 46
9 3 22
9 4 11
9 5 1
10 0 33
10 1 61
10 2 56
10 3 35
10 4 7
10 5 2
10 7 1
11 0 23
11 1 77
11 2 55
11 3 26
11 4 10
11 5 3
12 0 31
12 1 57
12 2 42
12 3 30
12 4 18
12 5 5
13 0 21
13 1 46
13 2 59
13 3 29
13 4 22
13 5 5
13 6 1
14 0 13
14 1 43
14 2 58
14 3 44
14 4 13
14 5 2
14 6 3
15 0 13
15 1 38
15 2 50
15 3 42
15 4 33
15 5 11
15 6 3
16 0 14
16 1 42
16 2 46
16 3 54
16 4 23
16 5 5
16 6 1
16 7 1
17 0 11
17 1 41
17 2 45
17 3 61
17 4 32
17 5 10
17 6 5
18 0 8
18 1 30
18 2 49
18 3 39
18 4 34
18 5 13
18 6 8
18 7 4
19 0 6
19 1 31
19 2 58
19 3 59
19 4 36
19 5 29
19 6 6
19 7 4
19 8 1
20 0 6
20 1 31
20 2 35
20 3 52
20 4 33
20 5 15
20 6 13
20 7 7
20 9 1
21 0 4
21 1 16
21 2 38
21 3 46
21 4 26
21 5 23
21 6 9
21 7 3
21 8 1
21 10 1
22 0 4
22 1 24
22 2 33
22 3 53
22 4 39
22 5 22
22 6 10
22 7 7
22 8 3
23 0 4
23 1 11
23 2 30
23 3 37
23 4 35
23 5 39
23 6 15
23 7 9
23 8 3
23 9 2
23 10 1
24 0 4
24 1 14
24 2 33
24 3 52
24 4 32
24 5 26
24 6 19
24 7 12
24 8 1
24 9 1
25 0 3
25 1 25
25 2 15
25 3 39
25 4 44
25 5 37
25 6 19
25 7 10
25 8 3
26 0 1
26 1 11
26 2 29
26 3 38
26 4 42
26 5 36
26 6 13
26 7 8
26 8 6
26 9 2
26 10 1
26 11 1
27 0 2
27 1 8
27 2 18
27 3 36
27 4 32
27 5 30
27 6 19
27 7 17
27 8 6
27 9 1
27 10 2
28 1 11
28 2 17
28 3 44
28 4 28
28 5 34
28 6 20
28 7 11
28 8 5
28 9 4
29 0 1
29 1 1
29 2 22
29 3 37
29 4 39
29 5 35
29 6 33
29 7 14
29 8 14
29 9 1
29 10 2
29 11 1
30 1 5
30 2 20
30 3 25
30 4 31
30 5 33
30 6 33
30 7 18
30 8 11
30 9 3
30 10 1
30 11 2
31 1 3
31 2 14
31 3 25
31 4 25
31 5 37
31 6 31
31 7 27
31 8 17
31 9 5
31 10 5
31 12 3
32 1 4
32 2 15
32 3 18
32 4 24
32 5 28
32 6 38
32 7 27
32 8 8
32 9 5
32 10 5
32 11 3
33 1 3
33 2 8
33 3 12
33 4 22
33 5 37
33 6 31
33 7 23
33 8 10
33 9 7
33 10 3
33 11 2
34 1 1
34 2 9
34 3 21
34 4 27
34 5 45
34 6 34
34 7 30
34 8 17
34 9 7
34 10 6
34 12 1
35 1 1
35 2 10
35 3 16
35 4 33
35 5 38
35 6 43
35 7 30
35 8 20
35 9 11
35 10 7
35 11 1
36 1 2
36 2 9
36 3 22
36 4 28
36 5 24
36 6 39
36 7 34
36 8 17
36 9 8
36 10 6
36 11 2
36 12 1
37 1 1
37 2 9
37 3 16
37 4 27
37 5 37
37 6 35
37 7 38
37 8 14
37 9 10
37 10 8
37 11 4
38 1 1
38 2 6
38 3 13
38 4 24
38 5 24
38 6 35
38 7 24
38 8 26
38 9 22
38 10 6
38 11 3
39 1 1
39 2 8
39 3 13
39 4 17
39 5 33
39 6 42
39 7 32
39 8 33
39 9 17
39 10 7
39 11 3
39 12 4
39 13 1
39 14 1
40 1 2
40 2 3
40 3 7
40 4 18
40 5 33
40 6 44
40 7 30
40 8 31
40 9 15
40 10 7
40 11 2
40 12 4
41 1 2
41 2 7
41 3 10
41 4 15
41 5 24
41 6 38
41 7 40
41 8 23
41 9 19
41 10 9
41 11 10
41 12 1
41 13 1
42 2 3
42 3 9
42 4 12
42 5 27
42 6 28
42 7 22
42 8 36
42 9 25
42 10 12
42 11 5
42 12 4
42 13 2
42 14 2
43 2 4
43 3 12
43 4 12
43 5 23
43 6 24
43 7 29
43 8 17
43 9 27
43 10 15
43 11 9
43 12 6
43 13 1
43 14 1
44 1 2
44 2 6
44 3 4
44 4 12
44 5 17
44 6 19
44 7 27
44 8 25
44 9 24
44 10 17
44 11 6
44 12 6
44 13 3
44 19 1
45 2 4
45 3 2
45 4 13
45 5 22
45 6 28
45 7 29
45 8 30
45 9 23
45 10 17
45 11 11
45 12 7
45 13 3
45 16 1
46 3 8
46 4 12
46 5 22
46 6 25
46 7 30
46 8 35
46 9 24
46 10 26
46 11 11
46 12 8
46 13 1
46 14 3
47 2 3
47 3 1
47 4 10
47 5 19
47 6 30
47 7 31
47 8 31
47 9 23
47 10 15
47 11 15
47 12 9
47 13 5
47 15 1
48 2 3
48 3 3
48 4 7
48 5 11
48 6 22
48 7 36
48 8 41
48 9 22
48 10 20
48 11 10
48 12 6
48 13 2
48 14 1
49 2 3
49 3 6
49 4 12
49 5 18
49 6 32
49 7 28
49 8 28
49 9 39
49 10 19
49 11 15
49 12 7
49 13 4
49 14 1
49 15 2
49 17 1
49 18 1
50 1 1
50 2 3
50 3 12
50 4 12
50 5 27
50 6 46
50 7 46
50 8 52
50 9 48
50 10 39
50 11 20
50 12 16
50 13 11
50 14 4
50 15 4
50 17 1
51 3 3
51 4 4
51 5 12
51 6 20
51 7 16
51 8 37
51 9 21
51 10 16
51 11 16
51 12 13
51 13 6
51 14 2
51 15 2
52 3 3
52 4 11
52 5 10
52 6 22
52 7 31
52 8 25
52 9 26
52 10 29
52 11 11
52 12 11
52 13 9
52 14 5
52 16 1
52 17 1
53 1 1
53 2 1
53 3 1
53 4 14
53 5 15
53 6 24
53 7 19
53 8 24
53 9 28
53 10 23
53 11 27
53 12 13
53 13 13
53 14 6
53 17 1
54 2 1
54 3 1
54 4 4
54 5 10
54 6 13
54 7 21
54 8 13
54 9 35
54 10 29
54 11 15
54 12 11
54 13 8
54 14 3
54 15 1
54 16 2
54 17 1
54 18 1
55 2 1
55 3 5
55 4 4
55 5 12
55 6 12
55 7 32
55 8 24
55 9 30
55 10 31
55 11 28
55 12 17
55 13 8
55 14 4
55 15 5
55 16 1
56 2 1
56 3 4
56 4 6
56 5 8
56 6 13
56 7 23
56 8 31
56 9 26
56 10 31
56 11 23
56 12 18
56 13 5
56 14 4
56 15 4
56 16 3
57 2 1
57 3 4
57 4 6
57 5 5
57 6 11
57 7 19
57 8 19
57 9 29
57 10 20
57 11 24
57 12 16
57 13 12
57 14 3
57 15 7
57 16 3
57 17 1
58 2 3
58 3 1
58 4 5
58 5 11
58 6 10
58 7 23
58 8 20
58 9 31
58 10 27
58 11 36
58 12 15
58 13 13
58 14 9
58 15 5
58 16 2
58 17 1
59 3 3
59 4 6
59 5 7
59 6 12
59 7 19
59 8 17
59 9 29
59 10 19
59 11 16
59 12 16
59 13 10
59 14 5
59 15 4
60 3 1
60 4 3
60 5 12
60 6 6
60 7 18
60 8 33
60 9 36
60 10 20
60 11 25
60 12 23
60 13 12
60 14 8
60 15 5
60 16 5
60 17 2
60 18 2
60 19 1
61 3 1
61 4 3
61 5 5
61 6 14
61 7 22
61 8 19
61 9 22
61 10 18
61 11 29
61 12 17
61 13 11
61 14 9
61 15 8
61 16 3
61 19 2
61 20 1
62 2 1
62 3 1
62 4 2
62 5 4
62 6 12
62 7 11
62 8 15
62 9 26
62 10 24
62 11 22
62 12 21
62 13 20
62 14 8
62 15 4
62 16 3
62 17 4
63 4 3
63 5 8
63 6 7
63 7 11
63 8 23
63 9 29
63 10 22
63 11 30
63 12 20
63 13 20
63 14 26
63 15 7
63 16 2
63 17 3
63 18 3
64 5 4
64 6 10
64 7 15
64 8 16
64 9 22
64 10 28
64 11 22
64 12 23
64 13 13
64 14 10
64 15 8
64 16 4
64 17 4
64 19 1
65 2 1
65 4 1
65 5 3
65 6 4
65 7 7
65 8 18
65 9 23
65 10 24
65 11 18
65 12 18
65 13 15
65 14 14
65 15 7
65 16 6
65 17 5
66 4 2
66 5 2
66 6 8
66 7 14
66 8 14
66 9 14
66 10 24
66 11 21
66 12 20
66 13 22
66 14 17
66 15 14
66 16 9
66 17 3
66 18 2
67 5 1
67 6 2
67 7 14
67 8 17
67 9 26
67 10 28
67 11 35
67 12 23
67 13 23
67 14 14
67 15 11
67 16 9
67 17 3
67 21 1
68 5 2
68 6 14
68 7 13
68 8 13
68 9 16
68 10 26
68 11 26
68 12 29
68 13 18
68 14 14
68 15 12
68 16 6
68 17 2
68 18 2
68 19 1
69 5 5
69 6 6
69 7 9
69 8 19
69 9 16
69 10 13
69 11 29
69 12 26
69 13 18
69 14 14
69 15 9
69 16 11
69 17 7
69 19 1
69 20 1
69 21 1
70 6 7
70 7 10
70 8 12
70 9 17
70 10 21
70 11 21
70 12 19
70 13 17
70 14 20
70 15 12
70 16 12
70 17 1
70 18 4
70 20 1
70 21 1
71 5 1
71 6 5
71 7 10
71 8 15
71 9 29
71 10 16
71 11 28
71 12 21
71 13 24
71 14 25
71 15 14
71 16 10
71 17 7
71 18 2
71 20 1
71 24 2
72 4 1
72 5 1
72 6 4
72 7 9
72 8 10
72 9 12
72 10 28
72 11 22
72 12 29
72 13 26
72 14 17
72 15 19
72 16 5
72 17 6
72 18 5
72 19 2
72 20 2
72 22 1
73 5 2
73 6 1
73 7 6
73 8 10
73 9 23
73 10 20
73 11 16
73 12 21
73 13 25
73 14 21
73 15 17
73 16 9
73 17 3
73 18 2
73 19 2
73 20 1
73 21 2
74 4 1
74 5 2
74 6 1
74 7 7
74 8 14
74 9 19
74 10 22
74 11 24
74 12 18
74 13 27
74 14 16
74 15 12
74 16 17
74 17 2
74 18 2
74 19 1
74 20 2
74 21 1
75 6 3
75 7 4
75 8 12
75 9 11
75 10 27
75 11 19
75 12 23
75 13 19
75 14 21
75 15 14
75 16 8
75 17 6
75 18 7
75 19 1
75 20 2
76 5 3
76 6 3
76 7 6
76 8 6
76 9 11
76 10 18
76 11 25
76 12 31
76 13 30
76 14 20
76 15 14
76 16 12
76 17 8
76 18 5
76 19 2
76 20 1
76 21 1
77 6 3
77 7 9
77 8 15
77 9 16
77 10 11
77 11 28
77 12 17
77 13 16
77 14 18
77 15 19
77 16 17
77 17 7
77 18 13
77 19 6
77 20 3
77 21 1
77 22 1
78 3 1
78 5 2
78 6 1
78 7 4
78 8 5
78 9 16
78 10 20
78 11 13
78 12 23
78 13 27
78 14 20
78 15 21
78 16 15
78 17 5
78 18 7
78 19 7
78 20 2
78 21 2
79 5 2
79 6 2
79 7 3
79 8 7
79 9 15
79 10 14
79 11 17
79 12 27
79 13 15
79 14 13
79 15 28
79 16 15
79 17 13
79 18 7
79 19 6
79 20 1
80 6 1
80 7 9
80 8 8
80 9 9
80 10 13
80 11 18
80 12 28
80 13 23
80 14 20
80 15 11
80 16 12
80 17 14
80 18 5
80 19 1
80 20 4
80 21 2
80 23 1
80 24 3
81 6 1
81 7 8
81 8 7
81 9 10
81 10 18
81 11 25
81 12 23
81 13 24
81 14 18
81 15 16
81 16 20
81 17 9
81 18 6
81 19 4
81 20 4
81 21 1
81 22 1
81 23 1
82 6 2
82 7 4
82 8 4
82 9 10
82 10 13
82 11 17
82 12 22
82 13 25
82 14 20
82 15 18
82 16 26
82 17 6
82 18 7
82 19 8
82 20 2
82 21 3
82 22 1
82 24 1
83 6 1
83 7 2
83 8 8
83 9 9
83 10 17
83 11 24
83 12 18
83 13 21
83 14 19
83 15 25
83 16 15
83 17 10
83 18 9
83 19 5
83 20 3
83 21 4
83 28 1
84 7 3
84 8 6
84 9 8
84 10 9
84 11 13
84 12 20
84 13 25
84 14 27
84 15 17
84 16 13
84 17 16
84 18 8
84 19 7
84 20 2
84 21 5
84 24 1
84 26 1
85 6 1
85 7 1
85 8 7
85 9 8
85 10 8
85 11 19
85 12 26
85 13 23
85 14 21
85 15 23
85 16 15
85 17 10
85 18 8
85 19 7
85 20 3
85 21 3
85 22 1
85 23 1
85 26 1
86 5 1
86 6 1
86 7 2
86 8 5
86 9 7
86 10 13
86 11 13
86 12 13
86 13 22
86 14 23
86 15 22
86 16 19
86 17 14
86 18 8
86 19 9
86 20 4
86 21 3
86 22 1
86 25 1
87 5 1
87 6 1
87 8 4
87 9 4
87 10 12
87 11 16
87 12 13
87 13 28
87 14 27
87 15 24
87 16 14
87 17 14
87 18 11
87 19 5
87 20 3
87 21 4
87 22 2
87 24 1
88 6 1
88 7 2
88 8 1
88 9 9
88 10 8
88 11 14
88 12 15
88 13 30
88 14 23
88 15 19
88 16 16
88 17 13
88 18 14
88 19 16
88 20 10
88 21 3
88 22 1
88 23 2
88 24 1
88 25 1
89 7 1
89 8 4
89 9 4
89 10 10
89 11 13
89 12 19
89 13 18
89 14 27
89 15 28
89 16 16
89 17 16
89 18 12
89 19 6
89 20 5
89 22 2
89 23 1
90 8 2
90 9 11
90 10 12
90 11 10
90 12 12
90 13 37
90 14 24
90 15 16
90 16 25
90 17 13
90 18 28
90 19 5
90 20 3
90 21 4
90 22 3
90 23 2
90 24 1
90 29 1
91 6 1
91 8 1
91 9 6
91 10 6
91 11 15
91 12 9
91 13 19
91 14 24
91 15 22
91 16 19
91 17 23
91 18 18
91 19 11
91 20 5
91 21 6
91 22 7
91 23 1
91 24 2
91 25 2
92 7 3
92 8 4
92 9 4
92 10 11
92 11 9
92 12 9
92 13 24
92 14 21
92 15 21
92 16 23
92 17 21
92 18 11
92 19 8
92 20 9
92 22 5
92 23 2
92 24 1
92 26 1
92 27 1
93 7 2
93 8 6
93 9 3
93 10 7
93 11 8
93 12 18
93 13 18
93 14 22
93 15 21
93 16 24
93 17 23
93 18 9
93 19 23
93 20 8
93 21 6
93 22 2
93 23 2
93 24 1
93 25 2
94 7 2
94 8 5
94 9 3
94 10 11
94 11 5
94 12 15
94 13 17
94 14 13
94 15 20
94 16 16
94 17 17
94 18 16
94 19 7
94 20 3
94 21 7
94 22 6
94 23 2
94 24 1
94 25 1
94 28 1
95 7 1
95 8 3
95 9 5
95 10 3
95 11 9
95 12 17
95 13 8
95 14 11
95 15 18
95 16 20
95 17 24
95 18 21
95 19 15
95 20 12
95 21 9
95 22 6
95 23 2
95 25 1
95 29 1
96 7 1
96 8 3
96 9 3
96 10 2
96 11 10
96 12 13
96 13 16
96 14 22
96 15 24
96 16 22
96 17 19
96 18 15
96 19 25
96 20 16
96 21 11
96 22 5
96 23 3
96 24 2
96 27 1
97 7 2
97 8 4
97 9 2
97 10 3
97 11 2
97 12 10
97 13 23
97 14 14
97 15 17
97 16 18
97 17 16
97 18 18
97 19 17
97 20 7
97 21 3
97 22 3
97 23 7
97 24 2
98 9 1
98 10 2
98 11 7
98 12 7
98 13 9
98 14 9
98 15 11
98 16 12
98 17 12
98 18 15
98 19 9
98 20 10
98 21 6
98 22 4
98 23 2
98 24 2
98 27 1
(1275 row(s) affected)
May 23, 2015 at 5:06 pm
I think Erikur's "red herring" is pretty accurate.
I ran the data generator several times with trace flag 8690 on and several times with trace flag 8690 off.
For TBL_SAMPLE_STRING the number of distinct string values was about 0.99 (range 0.9887 to 9893) times the number of non-null values, whatever the flag setting, with the flag off number being anything from 99.97% to 100% of the flag on number.
For TBL_SAMPLE_TRANSACTION the number of distinct customer_detail values was about 0.24 times the number of non-null values; the number with the flag off was about 99.96% of the number with the flag on. (Whether 4 transactions per customer is a reasonable average may be arguable either way, but that's irrelevant to this discussion.)
It seems quite clear that the number of duplicates is influenced too little by the flag setting to be distinguished from fluctuation natutrally caused by used of a PRNG in the generation process, at least on the amount of experimentation I am willing to do.
The strange performance effect seem by mburbea may be something to do with the weird performance characteristics of streaming TVFs in some cases, as reported by Adam Machanic in this connect item, which still isn't fixed - - I seem to remember someone making a connection between that and trace flag 8690 a couple of years back, but I can't remember where or find any trace of the statement now so I may be imagining it.
Tom
May 25, 2015 at 10:26 am
The flag just turns off table spool, so that would probably have a very noticeable effect on the function.
When I get some time I might try some of the techniques Adam listed to see if they affect performance. sTVFs are pretty cool, but because there's no way to change the cardinality, you're stuck hoping the actual result set is close. (CLR 1000 rows, xml 200 rows).
As for the xml splitter being bust, I'll concede that point. As I said, my interest was this lag/lead trick to cache a result that normally cannot be cached (e.g. lob values).
May 25, 2015 at 12:02 pm
Inn the meantime, can you post your test data generator that you're using? You said the Eirikur got beat up by a flaw in SQL Server so I'd like to see your workaround.
--Jeff Moden
May 25, 2015 at 10:37 pm
I modified his test script. And posted it in that same post where I criticized the point. When I didn't modify it my times were much much worse on sql server 2014.
It's maybe 10 posts up from here. I didn't change his function I just changed where it was being called to add the traceflag. This increases the randomness of the result as was noted.
I wouldn't really call it a flaw in sql server, you're not supposed to be able to call non-deterministic functions from a inline-table value function, but we currently exploit a trick by using a view. Sql server optimizes the result and considers it a runtime constant. 2008R2 and below don't do this.
June 29, 2015 at 6:28 am
October 28, 2015 at 9:57 am
So, question about these splitter functions (which are pretty much the most used tools in my arsenal, btw!), are they faster/more efficient being run from the I.B.G. CTE tally table like you're doing or with a physical Numbers table? Anyone done that analysis?
October 28, 2015 at 10:09 am
allnelsons (10/28/2015)
So, question about these splitter functions (which are pretty much the most used tools in my arsenal, btw!), are they faster/more efficient being run from the I.B.G. CTE tally table like you're doing or with a physical Numbers table? Anyone done that analysis?
It depends on the situation. I've seen smaller sets (< 8K rows) run faster with a physical tally table than with the tally function.
October 28, 2015 at 10:25 am
Back when I wrote my Splitter Function in 2003 I must have read something somewhere about tally tables and mine has 'disallowrowlocks' and 'disallowpagelocks' set. I presume, at the time, that that conveyed some performance advantage?
Perhaps that, or setting a physical Tally Table to "Read Only" in some way, offers a performance benefit?
October 28, 2015 at 10:35 am
Ed Wagner (10/28/2015)
allnelsons (10/28/2015)
So, question about these splitter functions (which are pretty much the most used tools in my arsenal, btw!), are they faster/more efficient being run from the I.B.G. CTE tally table like you're doing or with a physical Numbers table? Anyone done that analysis?It depends on the situation. I've seen smaller sets (< 8K rows) run faster with a physical tally table than with the tally function.
It does also depend on the construct of the inline talky table, the numbers of rows produced and few other factors, best results so far are using memory resident tally table.In some cases, normal tally patterns like 2^3^3 or 2^2^2^2 tend to be up to 50% slower than 465^3 or 15^2^3.
😎
October 28, 2015 at 1:15 pm
allnelsons (10/28/2015)
So, question about these splitter functions (which are pretty much the most used tools in my arsenal, btw!), are they faster/more efficient being run from the I.B.G. CTE tally table like you're doing or with a physical Numbers table? Anyone done that analysis?
I have tested this pretty extensively and have found that the CTE tally is faster than a physical tally table. This even when I add a unique nonclustered index. If I'm not mistaken, somewhere in this thread Paul White created an in-memory tally table which had better results than the CTE tally. I did not get better results when I tried the in memory tally table but I would not be surprised if I did something wrong (a subject I plan to re-visit one of these days).
The one place I have found the physical tally table to be superior to the CTE tally table is, on a couple occasions, I wanted to use a tally table in an indexed view. You can't do a CTE tally table in an indexed view.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 766 through 780 (of 981 total)
You must be logged in to reply to this topic. Login to reply