Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Performance issue with tally solution Expand / Collapse
Author
Message
Posted Monday, April 13, 2009 1:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695583
Posted Monday, April 13, 2009 2:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:03 PM
Points: 11,168, Visits: 10,934
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[i]);
}
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:

SELECT	S.row_id, TVF.row_id, TVF.result
FROM #source AS S
CROSS
APPLY dbo.tfn_Split(S.definition, CHAR(13) + CHAR(10)) AS TVF
ORDER BY
S.row_id, TVF.row_id;

Cheers,

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #695592
Posted Monday, April 13, 2009 4:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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[i=1, 1000] (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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695619
Posted Monday, April 13, 2009 4:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695620
Posted Monday, April 13, 2009 4:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
[code="csharp"]
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[i];

sw.WriteElementString("Item", line);
}

sw.WriteEndElement();
sw.WriteEndDocument();
sw.Flush();

strm.Position = 0;

SqlXml sqlXml = new SqlXml(strm);

// Put your code here
return sqlXml;
}
};
[/code]


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


@Jeff
The day to activate CLR on your servers is close

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695621
Posted Monday, April 13, 2009 4:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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" in a text editor until internet gives me a minute or two...



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695632
Posted Monday, April 13, 2009 5:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:03 PM
Points: 11,168, Visits: 10,934
Flo,

More great stuff from you again...!

I hope your internet connection is fixed soon.

Off to bed now, but thanks for the XML CLR solution - neat (though I am naturally XML-sceptic).

I disliked writing the CLR TVF intensely so it's good to see an alternative (and by all measures apparent to me) better solution.

The documentation regarding IEnumerable and CLR TVFs in general is not fantastic in BOL - there must be something better out there on the interweb...


Cheers,

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #695640
Posted Monday, April 13, 2009 5:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Paul White (4/13/2009)
Flo,

More great stuff from you again...!

I hope your internet connection is fixed soon.

Off to bed now, but thanks for the XML CLR solution - neat (though I am naturally XML-sceptic).

I disliked writing the CLR TVF intensely so it's good to see an alternative (and by all measures apparent to me) better solution.

The documentation regarding IEnumerable and CLR TVFs in general is not fantastic in BOL - there must be something better out there on the interweb...


Cheers,

Paul


Currently I'm online

I already saw some other solutions working with XML in SQL Server. It seems MS really optimized it for performance! Since now there is no fantastic documentation about CLR in BOL for anything...

Thanks again for all your help!

Good night
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695643
Posted Monday, April 13, 2009 6:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:06 PM
Points: 35,978, Visits: 30,269
Florian Reischl (4/13/2009)
I know Jeff will shoot me for this...
@Jeff
The day to activate CLR on your servers is close


Heh... you may be right. Might also have to install C# and learn it, too. I've been waiting for someone to do these types of tests. Thanks, Flo.


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #695684
Posted Monday, April 13, 2009 7:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:20 PM
Points: 194, Visits: 411
Say it ain't so. This is the first I've heard a set based solution losing to RBAR.

I don't have the desire to check right now, but what about using a CTE instead for the tally table?

aktikt
Post #695761
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse