April 12, 2009 at 5:40 pm
All I did was confirm your code is essentially the same on my SQL2005/1Gig ram 2.8 machine.
My first guess was because you were using a @table variable for your Tally table, I've always heard that @table vars were good only when they had fewer rows.
I ran your code, then substituted my permenant tally table...identical results. second pass saved a few milliseconds due to caching I assume.
/*
Start tally solution
Milliseconds: 3733 | Lines: 25987
Start cursor solution
Milliseconds: 2140 | Lines: 26922
second pass:
Start tally solution
Milliseconds: 3703 | Lines: 25987
Start cursor solution
Milliseconds: 1983 | Lines: 26922
*/
I've just got to assume that although a Tally table is awesome, there will be situations where it does not perform as well as other methods.
Lowell
April 12, 2009 at 6:48 pm
Flo: I'm not at my laptop right now, so I can't test your code. I'll do so at the earliest opportunity. Couple of questions though:
Why are you selecting the top (1000)?
WHERE N < len(s.definition)-1 should handle that.
ORDER BY N? Does it make any difference to the output?
Why table variable @tally?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 12, 2009 at 10:42 pm
Hey Flo!
It is true to say that the following features of the script won't help overall performance:
1. Using table variables (no statistics are available and parallelism cannot be used)
2. Using large object types (MAX)
3. Using Unicode
4. @crlf is defined as CHAR rather than NCHAR (implicit conversion)
Nevertheless, all that is rather beside the point here.
The tally or numbers table solution is, to an extent, a brute-force approach. In the case of many long strings where the frequency of the searched-for string is low, a solution based on a loop/CHARINDEX approach should out-perform the tally approach.
Searching a 1000-row table containing strings of 500K characters for example:
The tally method will execute a SUBSTRING 1000 * 500K times, trying to find the search string.
A method based on CHARINDEX will execute Sigma (Ni+1) times, where Ni is the number of occurrences of the search string in row i.
Of course CHARINDEX is a character-by-character search from the starting position, but even without doing the math rigorously, it is apparent that there will always be a point where the tally method will be slower.
In your particular example, it helps if the source table contains an extra column containing the length of the string, but the CHARINDEX method is still faster.
This code shows how including an extra length column helps the optimizer:
DECLARE @crlf NCHAR(2)
SELECT @crlf = CHAR(13) + CHAR(10)
SELECTSUBSTRING(s.definition, N + 2, CHARINDEX(@crlf, s.definition, N + 1) - N - 2)
FROM#source AS S
JOIN#tally AS T
ON (T.N BETWEEN 1 AND S.Length - 2)
WHERESUBSTRING(s.definition, N, 2) = @crlf
ORDERBY N
April 12, 2009 at 11:21 pm
Florian Reischl (4/12/2009)
Hello everybody!I have a performance issue with a set based solution. Please could you advice me what I'm doing wrong?
ORDER BY on unindexed Tally table, for starters. All the other comments about large table variables may apply, as well, although I haven't tested the code, yet.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2009 at 11:32 pm
Sratch the above... I found the index. Still looking.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2009 at 11:33 pm
Jeff Moden (4/12/2009)
ORDER BY on unindexed Tally table, for starters. All the other comments about large table variables may apply, as well, although I haven't tested the code, yet.
Unindexed? :unsure:
Flo's code:
DECLARE @tally TABLE (N INT NOT NULL, PRIMARY KEY CLUSTERED (N))
edit: overlapped with Jeff's latest 🙂
April 13, 2009 at 12:08 am
Jeff Moden (4/12/2009)
Florian Reischl (4/12/2009)
Hello everybody!I have a performance issue with a set based solution. Please could you advice me what I'm doing wrong?
ORDER BY on unindexed Tally table, for starters. All the other comments about large table variables may apply, as well, although I haven't tested the code, yet.
Wow! Nicely done, Flo... I haven't figured it out, yet. Looks like you may have figured out a way to beat the Tally table.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 13, 2009 at 12:43 am
Flo... there's a small error in your temporary stored procedure. It was leaving a leading space in the results. I've repaired it in the following but it still beats the tar out of the Tally table solution. Guess the Tally table pretty much sucks on the big stuff.
--===== Procedure to split line
IF (OBJECT_ID('tempdb..#usp_print_lines') IS NOT NULL)
DROP PROCEDURE #usp_print_lines
GO
CREATE PROCEDURE #usp_print_lines
@text NVARCHAR(MAX)
AS
DECLARE @ret TABLE (id INT IDENTITY, line NVARCHAR(MAX))
DECLARE @pos INT, @next INT, @crlf NCHAR(2)
SELECT @pos = 1, @crlf = CHAR(13) + CHAR(10)
WHILE (1 = 1)
BEGIN
SELECT @next = CHARINDEX(@crlf, @text, @pos)
IF (@next = 0) BREAK --- Nothing more to do
IF (@pos <> @next)
INSERT INTO @ret
SELECT SUBSTRING(@text, @pos + 1, @next - @pos -1)
SELECT @pos = @next + 1
END
SELECT line FROM @ret ORDER BY id --- Return lines
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
April 13, 2009 at 1:47 am
Hi
Thank you for all this feedback!
...hope my internet stays alive since I answered all the questions...
Greets
Flo
April 13, 2009 at 1:55 am
Bob Hovious (4/12/2009)
Flo: I'm not at my laptop right now, so I can't test your code. I'll do so at the earliest opportunity. Couple of questions though:Why are you selecting the top (1000)?
WHERE N < len(s.definition)-1 should handle that.
ORDER BY N? Does it make any difference to the output?
Why table variable @tally?
Hi Bob
Thanks for your help!
Why are you selecting the top (1000)?
The TOP(1000) is just to able to use the ORDER BY to ensure that the returned lines will not be swapped.
ORDER BY N? Does it make any difference to the output?
I'm not sure if a sequential result is ensured if not ordered. It is important that no lines will be swapped.
Why table variable @tally?
The variable @tally was only to ensure everything is available for the sample. I also tried with a usual Tally table, same result.
Greets
Flo
April 13, 2009 at 2:52 am
Hey again Flo,
You might like to also try this with a CLR TVF.
I tried it on your sample and got:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#source'. Scan count 1, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 3803, lob physical reads 0, lob read-ahead reads 2381.
SQL Server Execution Times:
CPU time = 1422 ms, elapsed time = 1940 ms.
A real C# dev will be able to improve my attempt at the code:
(the code tags have broken a couple of things 'AddAdd' and a cast, but hopefully you will get the gist...)
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = "FillRow",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
Name = "tfn_Split",
TableDefinition = "row_id int, result nvarchar(max)")]
public static IEnumerable tfn_Split
(
[SqlFacet(IsNullable = false, MaxSize = -1, IsFixedLength = false)]
SqlString StringToSplit,
[SqlFacet(IsNullable = false, MaxSize = 4000, IsFixedLength = false)]
SqlString Delimiter
)
{
string toSplit = StringToSplit.Value;
string delimeter = Delimiter.Value;
string[] items = toSplit.Split(new string[] { delimeter }, StringSplitOptions.RemoveEmptyEntries);
Dictionary (items.Length);
for (int i = 0; i < items.Length; i++)
{
results.Add(i + 1, items);
}
return results;
}
public static void FillRow(Object obj, out SqlInt32 row_id, out SqlString result)
{
KeyValuePair )obj;
row_id = new SqlInt32(kvp.Key);
result = new SqlString(kvp.Value);
}
};
The CLR version runs fastest on my laptop, has the simplest plan (obviously) and starts to return results immediately, which is nice.
For completeness, this is the SQL:
SELECTS.row_id, TVF.row_id, TVF.result
FROM#source AS S
CROSS
APPLYdbo.tfn_Split(S.definition, CHAR(13) + CHAR(10)) AS TVF
ORDERBY
S.row_id, TVF.row_id;
Cheers,
Paul
April 13, 2009 at 4:00 am
Hey Paul!
1. Using table variables (no statistics are available and parallelism cannot be used)
Was only for the sample to make everything available. I have a real tally table in my database. The performance is better with the real table but worse than the loop.
2. Using large object types (MAX)
3. Using Unicode
Source data are NVARCHAR(MAX) and it is required...
4. @crlf is defined as CHAR rather than NCHAR (implicit conversion)
Absolutely correct, thank you! I just corrected.
Nevertheless, all that is rather beside the point here.
The tally or numbers table solution is, to an extent, a brute-force approach. In the case of many long strings where the frequency of the searched-for string is low, a solution based on a loop/CHARINDEX approach should out-perform the tally approach.
Searching a 1000-row table containing strings of 500K characters for example:
The tally method will execute a SUBSTRING 1000 * 500K times, trying to find the search string.
A method based on CHARINDEX will execute Sigma (Ni+1) times, where Ni is the number of occurrences of the search string in row i.
Same as I think. The tally split function seems really handy and fast for sets of strings wich are not too large. For really large strings a simple character cursor seems to be a better solution. I think a split function is still one of the best approaches for a CLR function 😉
Greets
Flo
April 13, 2009 at 4:00 am
Hi Jeff!
Wow! Nicely done, Flo... I haven't figured it out, yet. Looks like you may have figured out a way to beat the Tally table.
I'm not sure if "nice" should be the correct word for "The known slow approach seems to be the faster solution" 😀
Thanks also for the bug fix in the procedure!
Greets
Flo
April 13, 2009 at 4:01 am
I know Jeff will shoot me for this... 🙂
I did some other tests and finally found a high performant solution with CLR. Due to the fact that CLR functions do not support table valued functions I had to handle with XML data type.
Since now the function is not really optimized but it beats all other solutions!
CLR function
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Xml;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static System.Data.SqlTypes.SqlXml ufn_clr_SplitLines(SqlChars textIn)
{
string text = new string(textIn.Value);
string[] lines = text.Split(new string[] { "\r" }, StringSplitOptions.None);
int capacity = text.Length * 2;
MemoryStream strm = new MemoryStream(capacity);
XmlWriter sw = XmlWriter.Create(strm);
sw.WriteStartDocument();
sw.WriteStartElement("Root");
for (int i = 0; i < lines.Length; i++)
{
string line = lines;
sw.WriteElementString("Item", line);
}
sw.WriteEndElement();
sw.WriteEndDocument();
sw.Flush();
strm.Position = 0;
SqlXml sqlXml = new SqlXml(strm);
// Put your code here
return sqlXml;
}
};
Additional test block in TSQL
DELETE FROM @result -- Clean up
-- //////////////////////////////////////////////////////////
-- -> Clr xml solution
PRINT 'Start clr xml solution'
SELECT @now = GETDATE()
-- Split text into lines
INSERT INTO @result
SELECT l.line
FROM @source s
CROSS APPLY (SELECT
ISNULL(T.C.value('(./text())[1]', 'nvarchar(1000)'), '') line
FROM (SELECT dbo.ufn_clr_SplitLines(s.definition) col1) x
CROSS APPLY x.col1.nodes('/Root/Item') T(C)
) l
-- Results
SELECT @duration = DATEDIFF(MILLISECOND, @now, GETDATE())
SELECT @count = COUNT(*) FROM @result
PRINT 'Milliseconds: ' + CONVERT(VARCHAR(10), @duration) + ' | Lines: ' + CONVERT(VARCHAR(10), @count)
-- <- Clr xml solution
-- //////////////////////////////////////////////////////////
Results
Start clr xml solution
Milliseconds: 783 | Lines: 28545
The day to activate CLR on your servers is close 😀
Greets
Flo
April 13, 2009 at 4:33 am
Hey Paul again!
Sorry! Didn't see your new post...
I did not know that CLR functions can return IEnumerable. Thanks for that!
I tested your function against mine:
Start clr xml solution
Milliseconds: 790 | Lines: 28545
Start clr tvf (PW) solution
Milliseconds: 1083 | Lines: 25661
I don't really understand why the way over XML is faster than a direct TVF. The only idea I have is that SQL Server calls the FillRowMethod over reflection, what would be really ugly implementation! I usually handle those kinds of dynamic method calls over dynamic classes and delegates. It's a little more source code but the performance improvement is up to factor 1000.
Greets
Flo
PS: Sorry also for the late answer. I had to restart my rooter about twenty times... Currently I'm doin' something like "stock posting" :hehe: in a text editor until internet gives me a minute or two...
Viewing 15 posts - 1 through 15 (of 523 total)
You must be logged in to reply to this topic. Login to reply