April 27, 2009 at 6:52 am
Hi all,
I have posted a more scalable SQLCLR string split method here:
Thanks to Paul White for pointing me to this thread. Please consider testing all of these methods UNDER LOAD, using either SQLQueryStress, as I did, or another load tool. The results will be quite interesting, I promise. 😀
--
Adam Machanic
whoisactive
April 27, 2009 at 6:56 am
Hi Adam!
You're done testing Grouped String Concatenation Challenge?
N 56°04'39.16"
E 12°55'05.25"
April 27, 2009 at 6:59 am
I knew I was going to hear something from you soon.
Next on my list, I promise!
--
Adam Machanic
whoisactive
April 27, 2009 at 7:18 am
Great!
I know I have a good solution for original AdventureWorks, but I have no idea how well it scales.
Sorry for the interruption people...
N 56°04'39.16"
E 12°55'05.25"
April 27, 2009 at 7:33 am
Adam,
Just so we can be sure to test exactly the same code, can you please post the Creation script ?
It looks to me as if you've got a fix for the second-order polynomial effect.
I'm dying to test it alongside the others on Moby Dick.
Am I right in thinking that it is expecting a single-character delimiter? If so, it is not strictly comparable, as Flo's original problem involved a two-character delimiter. Persumably, it would be an easy change to make.
Best wishes,
Phil Factor
April 27, 2009 at 7:39 am
Dang but Adam posts fast!
Here's the link to his blog entry that I was going to post:
Jeff is going to love this 😉
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 27, 2009 at 8:04 am
Phil Factor (4/27/2009)
Am I right in thinking that it is expecting a single-character delimiter? If so, it is not strictly comparable, as Flo's original problem involved a two-character delimiter. Persumably, it would be an easy change to make.
Yes and yes, in that order Phil.
As far as I can see, one would have to change the SqlFacet's MaxSize, the line that adds one to nextPos to get lastPos (change to delimiter.Length) and change delimiter from a char to a char[] or string. I think that's it, just in case Adam doesn't come back on your question.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 27, 2009 at 8:28 am
Phil Factor (4/27/2009)
Just so we can be sure to test exactly the same code, can you please post the Creation script ?
Actually I used autodeploy 🙂
One of the key things is making sure that the output is NVARCHAR(N), not NVARCHAR(MAX)--as it turns out, returning NVARCHAR(MAX) from a SQLCLR TVF is quite a bit slower (something to do with how the data is allocated internally, and I don't pretend to fully understand it).
Phil Factor (4/27/2009)
Am I right in thinking that it is expecting a single-character delimiter? If so, it is not strictly comparable, as Flo's original problem involved a two-character delimiter. Persumably, it would be an easy change to make.
Yes, that is correct. That change is easy to make. Here you go:
(Update, a bit later: There was an off-by-one bug here with multiple character delimiters--fixed.)
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillIt", TableDefinition = "output nvarchar(4000)")]
public static IEnumerator faster_split
(
SqlChars instr,
SqlString delimiter
)
{
return (
(instr.IsNull || delimiter.IsNull) ?
new splitIt("", ",") :
new splitIt(instr.ToSqlString().Value, delimiter.Value));
}
public static void FillIt(object obj, out SqlString output)
{
output = (new SqlString((string)obj));
}
public class splitIt : IEnumerator
{
public splitIt(string theString, string delimiter)
{
this.theString = theString;
this.delimiter = delimiter;
this.lastPos = 0;
this.nextPos = 0;
}
#region IEnumerator Members
public object Current
{
get { return theString.Substring(lastPos, nextPos - lastPos).Trim(); }
}
public bool MoveNext()
{
if (nextPos >= theString.Length)
return false;
else
{
if (nextPos > 0)
{
lastPos = nextPos + delimiter.Length;
}
if (lastPos == theString.Length)
return false;
nextPos = theString.IndexOf(delimiter, lastPos);
if (nextPos == -1)
nextPos = theString.Length;
return true;
}
}
public void Reset()
{
this.lastPos = 0;
this.nextPos = 0;
}
#endregion
private int lastPos;
private int nextPos;
private string theString;
private string delimiter;
}
};
--
Adam Machanic
whoisactive
April 27, 2009 at 8:36 am
Paul White (4/26/2009)
@FloRegex is pretty awesome for long strings eh?
Hey Paul
Yes, the performance of the RegEx amazed me too! The RegEx is quiet simple "\r|\r" (no backward or forward references) and compiled.
Anyway, my other tests with non-compiled RegEx show also great results!
Greets
Flo
April 27, 2009 at 8:53 am
Paul White (4/27/2009)
@Phil, Flo:Probably the easiest way would be to compare your respective .sqlplan actual execution plans for the queries in question, together with the usual CPU and IO information...?
I will post my execution plans this evening.
P.S. One obvious disadvantage of the CLR solutions which I forgot before is that T-SQL is more easily re-used. Copying a procedure or function definition in T-SQL is easier and faster than creating an assembly and a T-SQL stub. This is the main reason I still use a ROW_NUMBER() generated numbers table for quick one-off string splits.
???
Database -> Programmability -> Assemblies -> Right click -> Script
Functions -> Right Click -> Script
... seems quiet simple in my opinion. 😉
Greets
Flo
April 27, 2009 at 9:09 am
Adam Machanic (4/27/2009)
Hi all,I have posted a more scalable SQLCLR string split method here:
Thanks to Paul White for pointing me to this thread. Please consider testing all of these methods UNDER LOAD, using either SQLQueryStress, as I did, or another load tool. The results will be quite interesting, I promise. 😀
Hi Adam
😀
Exactly same approach as my apprentice told me last week. A custom enumerator. You are correct, it should scale better than a complete split in one step! I will include it into my tests.
Load-tests are pending but will be done. Thanks for the hint to SQLQueryStress! I intended to write a small tool for this but if the tool already exists I use it 😉
Greets
Flo
April 27, 2009 at 10:13 am
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... 😀
April 27, 2009 at 10:20 am
OK, I figured out how to do the same iteration on the character array instead of converting to a string (see below). Initial tests on my end are showing this method to be around 15% faster. Let me know what your results show, Florian!
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillIt", TableDefinition = "output nvarchar(4000)")]
public static IEnumerator even_faster
(
SqlChars instr,
[SqlFacet(MaxSize=255)]
SqlString delimiter
)
{
return (
(instr.IsNull || delimiter.IsNull) ?
new splitChars(new char[0], new char[0]) :
new splitChars(instr.Value, delimiter.Value.ToCharArray()));
}
public class splitChars : IEnumerator
{
public splitChars(char[] theString, char[] delimiter)
{
this.theString = theString;
this.delimiter = delimiter;
this.lastPos = 0;
this.nextPos = 0;
this.stringLen = theString.Length;
this.delimiterLen = (byte)delimiter.Length;
}
#region IEnumerator Members
public object Current
{
get { return new String(theString, lastPos, nextPos - lastPos).Trim(); }
}
public bool MoveNext()
{
if (nextPos > 0)
{
nextPos += delimiterLen;
lastPos = nextPos;
}
if (delimiterLen > 0)
{
while (nextPos < stringLen)
{
//start of a delimiter?
if (theString[nextPos] == delimiter[0])
{
//optimize for single-character delimiters
if (delimiterLen == 1)
{
return true;
}
else
{
//deal with multi-character delimiters
//bool to remember whether we have an actual delimiter or just a partial
bool isDelimiter = true;
byte delimiterIndex = 1;
while (delimiterIndex < delimiterLen)
{
if (delimiter[delimiterIndex] != theString[nextPos + delimiterIndex])
{
isDelimiter = false;
break;
}
delimiterIndex++;
}
if (isDelimiter)
{
return true;
}
else
{
//Increment based on however far we got into the false delimiter
nextPos += delimiterIndex;
}
}
}
nextPos++;
}
if (nextPos <= stringLen)
return true;
else
return false;
}
else if (nextPos < stringLen)
{
nextPos = stringLen;
return true;
}
else
return false;
}
public void Reset()
{
this.lastPos = 0;
this.nextPos = 0;
}
#endregion
private int lastPos;
private int nextPos;
private readonly char[] theString;
private readonly char[] delimiter;
private readonly int stringLen;
private readonly byte delimiterLen;
}
};
--
Adam Machanic
whoisactive
April 27, 2009 at 10:43 am
Hi Adam!
Thanks for the new version! I also supposed that a char array should perform better than the string. The current test results show the opposite though...
I'm out for now and will try again at home. Also with more different data.
Test results:
ItemLength ItemCount ItemRows ItemType ItemDescription Ranking Module Duration(ms) CpuTime
----------- ----------- ----------- --------------- ----------------- -------- ------------------------- ------------ -------
10 10 10 VARCHAR(111) Fixed Length 1 Traditional Tally TOP(0) 00:00:00.010 15
10 10 10 VARCHAR(111) Fixed Length 2 CLR Simple 00:00:00.017 16
10 10 10 VARCHAR(111) Fixed Length 3 CLR String Enumerator 00:00:00.020 15
10 10 10 VARCHAR(111) Fixed Length 4 Cursor 00:00:00.023 16
10 10 10 VARCHAR(111) Fixed Length 5 CLR RegEx 00:00:00.083 63
10 10 10 VARCHAR(111) Fixed Length 6 CLR char loop 00:00:00.103 79
10 10 10 VARCHAR(111) Fixed Length 7 Traditional Tally 00:00:00.123 16
10 10 10 VARCHAR(111) Fixed Length 8 CLR XML 00:00:00.253 156
10 10 1000 VARCHAR(111) Fixed Length 1 Traditional Tally TOP(0) 00:00:00.140 141
10 10 1000 VARCHAR(111) Fixed Length 2 CLR String Enumerator 00:00:00.463 453
10 10 1000 VARCHAR(111) Fixed Length 3 CLR RegEx 00:00:00.467 469
10 10 1000 VARCHAR(111) Fixed Length 4 CLR char loop 00:00:00.493 468
10 10 1000 VARCHAR(111) Fixed Length 5 CLR Simple 00:00:00.547 531
10 10 1000 VARCHAR(111) Fixed Length 6 Traditional Tally 00:00:00.557 156
10 10 1000 VARCHAR(111) Fixed Length 7 Cursor 00:00:01.020 938
10 10 1000 VARCHAR(111) Fixed Length 8 CLR XML 00:00:01.053 984
100 10 10 VARCHAR(1011) Fixed Length 1 CLR Simple 00:00:00.010 15
100 10 10 VARCHAR(1011) Fixed Length 2 CLR RegEx 00:00:00.013 16
100 10 10 VARCHAR(1011) Fixed Length 3 Traditional Tally TOP(0) 00:00:00.013 16
100 10 10 VARCHAR(1011) Fixed Length 4 CLR String Enumerator 00:00:00.017 16
100 10 10 VARCHAR(1011) Fixed Length 5 CLR char loop 00:00:00.050 16
100 10 10 VARCHAR(1011) Fixed Length 6 Cursor 00:00:00.053 15
100 10 10 VARCHAR(1011) Fixed Length 7 CLR XML 00:00:00.057 31
100 10 10 VARCHAR(1011) Fixed Length 8 Traditional Tally 00:00:00.177 16
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 RegEx 00:00:00.077 62
100 10 100 VARCHAR(1011) Fixed Length 3 CLR char loop 00:00:00.090 78
100 10 100 VARCHAR(1011) Fixed Length 4 Traditional Tally TOP(0) 00:00:00.100 94
100 10 100 VARCHAR(1011) Fixed Length 5 CLR Simple 00:00:00.103 62
100 10 100 VARCHAR(1011) Fixed Length 6 Cursor 00:00:00.210 156
100 10 100 VARCHAR(1011) Fixed Length 7 CLR XML 00:00:00.233 125
100 10 100 VARCHAR(1011) Fixed Length 8 Traditional Tally 00:00:00.477 93
100 10 100 NVARCHAR(1011) Fixed Length 1 CLR String Enumerator 00:00:00.060 62
100 10 100 NVARCHAR(1011) Fixed Length 2 CLR Simple 00:00:00.063 47
100 10 100 NVARCHAR(1011) Fixed Length 3 CLR RegEx 00:00:00.063 62
100 10 100 NVARCHAR(1011) Fixed Length 4 CLR char loop 00:00:00.090 62
100 10 100 NVARCHAR(1011) Fixed Length 5 Traditional Tally TOP(0) 00:00:00.120 125
100 10 100 NVARCHAR(1011) Fixed Length 6 CLR XML 00:00:00.147 141
100 10 100 NVARCHAR(1011) Fixed Length 7 Cursor 00:00:00.167 125
100 10 100 NVARCHAR(1011) Fixed Length 8 Traditional Tally 00:00:00.530 109
100 10 1000 NVARCHAR(1011) Fixed Length 1 CLR Simple 00:00:00.657 579
100 10 1000 NVARCHAR(1011) Fixed Length 2 CLR RegEx 00:00:00.657 578
100 10 1000 NVARCHAR(1011) Fixed Length 3 CLR String Enumerator 00:00:00.687 625
100 10 1000 NVARCHAR(1011) Fixed Length 4 CLR char loop 00:00:00.713 625
100 10 1000 NVARCHAR(1011) Fixed Length 5 Traditional Tally TOP(0) 00:00:01.083 1063
100 10 1000 NVARCHAR(1011) Fixed Length 6 Cursor 00:00:01.293 1219
100 10 1000 NVARCHAR(1011) Fixed Length 7 CLR XML 00:00:01.390 1187
100 10 1000 NVARCHAR(1011) Fixed Length 8 Traditional Tally 00:00:01.713 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 Simple 00:00:00.007 0
10 10 10 VARCHAR(111) Variable Length 3 CLR RegEx 00:00:00.007 16
10 10 10 VARCHAR(111) Variable Length 4 CLR String Enumerator 00:00:00.007 15
10 10 10 VARCHAR(111) Variable Length 5 CLR char loop 00:00:00.027 0
10 10 10 VARCHAR(111) Variable Length 6 Cursor 00:00:00.040 0
10 10 10 VARCHAR(111) Variable Length 7 CLR XML 00:00:00.050 31
10 10 10 VARCHAR(111) Variable Length 8 Traditional Tally 00:00:00.333 16
10 10 1000 VARCHAR(111) Variable Length 1 Traditional Tally TOP(0) 00:00:00.100 94
10 10 1000 VARCHAR(111) Variable Length 2 Traditional Tally 00:00:00.360 140
10 10 1000 VARCHAR(111) Variable Length 3 CLR Simple 00:00:00.483 484
10 10 1000 VARCHAR(111) Variable Length 4 CLR RegEx 00:00:00.503 500
10 10 1000 VARCHAR(111) Variable Length 5 CLR String Enumerator 00:00:00.507 453
10 10 1000 VARCHAR(111) Variable Length 6 CLR char loop 00:00:00.533 500
10 10 1000 VARCHAR(111) Variable Length 7 Cursor 00:00:00.910 828
10 10 1000 VARCHAR(111) Variable Length 8 CLR XML 00:00:01.040 953
500 10 10 VARCHAR(5011) Variable Length 1 CLR RegEx 00:00:00.013 15
500 10 10 VARCHAR(5011) Variable Length 2 CLR String Enumerator 00:00:00.013 16
500 10 10 VARCHAR(5011) Variable Length 3 CLR Simple 00:00:00.017 16
500 10 10 VARCHAR(5011) Variable Length 4 CLR char loop 00:00:00.017 15
500 10 10 VARCHAR(5011) Variable Length 5 Traditional Tally TOP(0) 00:00:00.023 16
500 10 10 VARCHAR(5011) Variable Length 6 Cursor 00:00:00.040 15
500 10 10 VARCHAR(5011) Variable Length 7 CLR XML 00:00:00.077 16
500 10 10 VARCHAR(5011) Variable Length 8 Traditional Tally 00:00:00.453 16
500 10 100 VARCHAR(5011) Variable Length 1 CLR String Enumerator 00:00:00.083 78
500 10 100 VARCHAR(5011) Variable Length 2 CLR Simple 00:00:00.097 78
500 10 100 VARCHAR(5011) Variable Length 3 CLR RegEx 00:00:00.097 94
500 10 100 VARCHAR(5011) Variable Length 4 CLR char loop 00:00:00.100 63
500 10 100 VARCHAR(5011) Variable Length 5 CLR XML 00:00:00.173 141
500 10 100 VARCHAR(5011) Variable Length 6 Cursor 00:00:00.193 172
500 10 100 VARCHAR(5011) Variable Length 7 Traditional Tally TOP(0) 00:00:00.257 234
500 10 100 VARCHAR(5011) Variable Length 8 Traditional Tally 00:00:00.543 203
See/write/read you later 😉
Flo
April 27, 2009 at 10:51 am
Florian Reischl (4/27/2009)
Thanks for the new version! I also supposed that a char array should perform better than the string. The current test results show the opposite though...
Did you try my new version in your tests? Or are you referring to the char array solution already posted here? I'm assuming the latter since the test results you just posted appear to be unchanged from your previous post... Please give the new version a try. In my tests against my previous split method my new one is performing consistently 12-15% faster, both under load and in solitary tests on an empty server.
--
Adam Machanic
whoisactive
Viewing 15 posts - 241 through 255 (of 522 total)
You must be logged in to reply to this topic. Login to reply