RE: Performance issue with tally solution

SSC-Dedicated

Points: 37301

April 28, 2009 at 3:33 am

Jeff Moden (4/27/2009)
Florian Reischl (4/27/2009)
Hi Adam
The String Enumerator (I just called it "String Enumerator" for better understanding what it does) performs very good! Especially for more rows. I just tried with some of my test cases:
ItemLength ItemCount ItemRows ItemType ItemDescription Ranking Module Duration(ms) CpuTime
----------- ----------- ----------- -------------- ---------------- -------- -------------------------- ------------ -----------
10 10 10 VARCHAR(111) Fixed Length 1 CLR Simple 00:00:00.010 0
10 10 10 VARCHAR(111) Fixed Length 2 CLR RegEx 00:00:00.010 0
10 10 10 VARCHAR(111) Fixed Length 3 CLR String Enumerator 00:00:00.010 16
10 10 10 VARCHAR(111) Fixed Length 4 Traditional Tally TOP(0) 00:00:00.010 16
10 10 10 VARCHAR(111) Fixed Length 5 Cursor 00:00:00.033 15
10 10 10 VARCHAR(111) Fixed Length 6 CLR char loop 00:00:00.037 0
10 10 10 VARCHAR(111) Fixed Length 7 CLR XML 00:00:00.053 31
10 10 10 VARCHAR(111) Fixed Length 8 Traditional Tally 00:00:00.113 0
10 10 1000 VARCHAR(111) Fixed Length 1 Traditional Tally TOP(0) 00:00:00.203 156
10 10 1000 VARCHAR(111) Fixed Length 2 CLR String Enumerator 00:00:00.443 422
10 10 1000 VARCHAR(111) Fixed Length 3 CLR Simple 00:00:00.467 468
10 10 1000 VARCHAR(111) Fixed Length 4 CLR RegEx 00:00:00.473 468
10 10 1000 VARCHAR(111) Fixed Length 5 CLR char loop 00:00:00.533 516
10 10 1000 VARCHAR(111) Fixed Length 6 Traditional Tally 00:00:00.747 188
10 10 1000 VARCHAR(111) Fixed Length 7 CLR XML 00:00:00.987 954
10 10 1000 VARCHAR(111) Fixed Length 8 Cursor 00:00:01.027 969
100 10 10 VARCHAR(1011) Fixed Length 1 CLR Simple 00:00:00.010 16
100 10 10 VARCHAR(1011) Fixed Length 2 CLR RegEx 00:00:00.010 15
100 10 10 VARCHAR(1011) Fixed Length 3 CLR String Enumerator 00:00:00.013 16
100 10 10 VARCHAR(1011) Fixed Length 4 Traditional Tally TOP(0) 00:00:00.017 16
100 10 10 VARCHAR(1011) Fixed Length 5 CLR char loop 00:00:00.027 0
100 10 10 VARCHAR(1011) Fixed Length 6 Cursor 00:00:00.040 16
100 10 10 VARCHAR(1011) Fixed Length 7 CLR XML 00:00:00.070 15
100 10 10 VARCHAR(1011) Fixed Length 8 Traditional Tally 00:00:00.370 31
100 10 100 VARCHAR(1011) Fixed Length 1 CLR String Enumerator 00:00:00.073 47
100 10 100 VARCHAR(1011) Fixed Length 2 CLR char loop 00:00:00.087 63
100 10 100 VARCHAR(1011) Fixed Length 3 CLR RegEx 00:00:00.087 62
100 10 100 VARCHAR(1011) Fixed Length 4 CLR Simple 00:00:00.090 78
100 10 100 VARCHAR(1011) Fixed Length 5 Traditional Tally TOP(0) 00:00:00.110 94
100 10 100 VARCHAR(1011) Fixed Length 6 CLR XML 00:00:00.150 109
100 10 100 VARCHAR(1011) Fixed Length 7 Cursor 00:00:00.167 140
100 10 100 VARCHAR(1011) Fixed Length 8 Traditional Tally 00:00:00.417 141
100 10 100 NVARCHAR(1011) Fixed Length 1 CLR String Enumerator 00:00:00.057 47
100 10 100 NVARCHAR(1011) Fixed Length 2 CLR Simple 00:00:00.070 63
100 10 100 NVARCHAR(1011) Fixed Length 3 CLR RegEx 00:00:00.070 62
100 10 100 NVARCHAR(1011) Fixed Length 4 Traditional Tally TOP(0) 00:00:00.143 110
100 10 100 NVARCHAR(1011) Fixed Length 5 CLR char loop 00:00:00.147 79
100 10 100 NVARCHAR(1011) Fixed Length 6 CLR XML 00:00:00.150 125
100 10 100 NVARCHAR(1011) Fixed Length 7 Cursor 00:00:00.180 141
100 10 100 NVARCHAR(1011) Fixed Length 8 Traditional Tally 00:00:00.720 109
100 10 1000 NVARCHAR(1011) Fixed Length 1 CLR String Enumerator 00:00:00.567 532
100 10 1000 NVARCHAR(1011) Fixed Length 2 CLR char loop 00:00:00.670 547
100 10 1000 NVARCHAR(1011) Fixed Length 3 CLR Simple 00:00:00.677 609
100 10 1000 NVARCHAR(1011) Fixed Length 4 CLR RegEx 00:00:00.717 641
100 10 1000 NVARCHAR(1011) Fixed Length 5 Traditional Tally TOP(0) 00:00:01.313 1172
100 10 1000 NVARCHAR(1011) Fixed Length 6 Cursor 00:00:01.350 1313
100 10 1000 NVARCHAR(1011) Fixed Length 7 CLR XML 00:00:01.517 1094
100 10 1000 NVARCHAR(1011) Fixed Length 8 Traditional Tally 00:00:01.837 1141
10 10 10 VARCHAR(111) Variable Length 1 Traditional Tally TOP(0) 00:00:00.007 0
10 10 10 VARCHAR(111) Variable Length 2 CLR RegEx 00:00:00.007 16
10 10 10 VARCHAR(111) Variable Length 3 CLR String Enumerator 00:00:00.007 16
10 10 10 VARCHAR(111) Variable Length 4 CLR Simple 00:00:00.010 0
10 10 10 VARCHAR(111) Variable Length 5 CLR char loop 00:00:00.020 0
10 10 10 VARCHAR(111) Variable Length 6 Cursor 00:00:00.030 15
10 10 10 VARCHAR(111) Variable Length 7 CLR XML 00:00:00.047 15
10 10 10 VARCHAR(111) Variable Length 8 Traditional Tally 00:00:00.097 0
10 10 1000 VARCHAR(111) Variable Length 1 Traditional Tally TOP(0) 00:00:00.110 109
10 10 1000 VARCHAR(111) Variable Length 2 CLR String Enumerator 00:00:00.453 453
10 10 1000 VARCHAR(111) Variable Length 3 CLR Simple 00:00:00.457 469
10 10 1000 VARCHAR(111) Variable Length 4 CLR RegEx 00:00:00.483 484
10 10 1000 VARCHAR(111) Variable Length 5 CLR char loop 00:00:00.537 484
10 10 1000 VARCHAR(111) Variable Length 6 Traditional Tally 00:00:00.537 141
10 10 1000 VARCHAR(111) Variable Length 7 Cursor 00:00:00.933 906
10 10 1000 VARCHAR(111) Variable Length 8 CLR XML 00:00:01.120 891
500 10 10 VARCHAR(5011) Variable Length 1 CLR String Enumerator 00:00:00.010 16
500 10 10 VARCHAR(5011) Variable Length 2 CLR Simple 00:00:00.013 15
500 10 10 VARCHAR(5011) Variable Length 3 CLR RegEx 00:00:00.013 0
500 10 10 VARCHAR(5011) Variable Length 4 CLR char loop 00:00:00.020 0
500 10 10 VARCHAR(5011) Variable Length 5 Traditional Tally TOP(0) 00:00:00.027 15
500 10 10 VARCHAR(5011) Variable Length 6 Cursor 00:00:00.043 15
500 10 10 VARCHAR(5011) Variable Length 7 CLR XML 00:00:00.087 16
500 10 10 VARCHAR(5011) Variable Length 8 Traditional Tally 00:00:00.560 32
500 10 100 VARCHAR(5011) Variable Length 1 CLR RegEx 00:00:00.073 62
500 10 100 VARCHAR(5011) Variable Length 2 CLR String Enumerator 00:00:00.073 47
500 10 100 VARCHAR(5011) Variable Length 3 CLR Simple 00:00:00.090 78
500 10 100 VARCHAR(5011) Variable Length 4 CLR char loop 00:00:00.110 78
500 10 100 VARCHAR(5011) Variable Length 5 CLR XML 00:00:00.193 125
500 10 100 VARCHAR(5011) Variable Length 6 Traditional Tally TOP(0) 00:00:00.210 203
500 10 100 VARCHAR(5011) Variable Length 7 Cursor 00:00:00.217 188
500 10 100 VARCHAR(5011) Variable Length 8 Traditional Tally 00:00:00.440 219
Greets
Flo
PS: I just formatted the code more to a C# look in my solution. DBAs writing C#, pew...
Flo, which test generator are you using for this. Just gotta make sure.

Hi Jeff

I use a little extended version of the generic approach I posted. Complete environment (CLR-Project in C#, Database-DDL, Test script) is attached here.

Greets

Flo

Cookies on SQLServerCentral