July 13, 2009 at 11:06 am
Hi,
I've been testing CLR performance using the SQL 2005 sample code provided by Microsoft and I find that it is slower that more standard solutions. I'll give 2 examples:
1. String splitting. Using the split C# code is slower than splitting a delimited string using a Tally (i.e. numbers) table
Here is the standard SQL solution:
select substring (path, b.id+1, charindex ('.', path, b.id+1)-b.id-1)
from paths a
cross join Tally b
where substring (path, b.id, 1) = '.'
and b.id < len (path)[/b]
2. concatening a string via the "for xml path ('')" is faster than the Concatenate C# solution in the samples. Here is the xml path solution:
[b]SELECT a.id
, '.'+(select cast (a as varchar (10))+'.'
from T b
where b.id = a.id
for xml path ('')
)
FROM T a
group by id
[/b]
I certainly would have thought that in the 2nd case the standard solution would have been slower because it is a correlated query.
Why is this happening? Is it the cost of context switching? Method invocation should should just be be a stack push.
Is it because of object creation?
Any thoughts would be greatly appreciated.
Regards to all,
David
July 13, 2009 at 12:08 pm
David Brusowankin (7/13/2009)
I've been testing CLR performance using the SQL 2005 sample code provided by Microsoft and I find that it is slower that more standard solutions. I'll give 2 examples:
This is actually quite common, though it is not as bad as I once thought it was.
1. String splitting. Using the split C# code is slower than splitting a delimited string using a Tally (i.e. numbers) table
Here is the standard SQL solution:
select substring (path, b.id+1, charindex ('.', path, b.id+1)-b.id-1)
from paths a
cross join Tally b
where substring (path, b.id, 1) = '.'
and b.id < len (path)
This has been explored extensively, the definitive information is here: http://www.sqlservercentral.com/Forums/Topic695508-338-1.aspx (until Florian writes his article 🙂 ). The short answer is: "it depends on a number of factors (most importantly the length of the string being searched and the length of the separator being searched for), but the best CLR solutions are usually the fastest." The key word in that statement is "best", it makes a huge difference what approach you use. Read the thread, it's long but it has absolutely everything in it, including several high-speed, high-power solutions.
2. concatening a string via the "for xml path ('')" is faster than the Concatenate C# solution in the samples. Here is the xml path solution:
SELECT a.id
, '.'+(select cast (a as varchar (10))+'.'
from T b
where b.id = a.id
for xml path ('')
)
FROM T a
group by id
Although the SQL XML has a number of problems (mostly in characters that it cannot handle) I think that it is the current best solution. IIRC, there are a number of different good CLR approaches but they are all hampered by the inabilty to stream a long solution string back into SQL efficiently.
I certainly would have thought that in the 2nd case the standard solution would have been slower because it is a correlated query.
The putative performance problems of correlated subqueries are an archaic myth. They were not optimized well in SQL Server 2000, but this has been largely fixed in 2005. Now, correlated subqueries usually perform just as well as an equal number of functionally equivalent Joins (the exception is when multiple correlated subqueries could be converted to a single Join, SQL Server still does not optimize this well).
Why is this happening? Is it the cost of context switching? Method invocation should should just be be a stack push.
That wouldn't be safe for cross-environment calls & returns and I do not know any high-level environment-context switching invocation that works that simply. Environment contexts need to protect themselves against each other or it's not really safe (like extended procedures were not really safe).
... (more in a minute) ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 13, 2009 at 1:16 pm
I find that there are two common assumptions that developers make about SQL Server & CLR that lead to first, over-application of SQLCLR on SQL Server and secondly, to confusion and disappointment when performance improvements are not apparent (or performance actually decreases).
The first, which you have stated, is that the invocation overhead is zero or close to zero. Unfortunately, this not the case. Although the invocation overhead is not awful, it is still significant.
In fact Microsoft says that the invocation overhead of say a CLR user-defined function is lower than that of a T-SQL user-defined function. While this may be true, it side-steps that fact that in T-SQL, you always have the option of "in-lining" the function code and eliminating the invocation overhead altogether. Obviously, CLR does not have this same option and thus it is stuck with the call overhead. and no matter how reasonable that overhead may be, when it is multiplied by a million rows, it becomes significant.
Added to this are two particular problem areas: 1) the overhead of passing (always by Copy) and returning LOBs, Large-Object Strings and 2) return Table-Valued results. the theoretically-best techniques for these two cases do not appear to be available where you need them the most in CLR. (Paul or Florian could explain this better than I).
The second, even more common assumption, is that A) .Net compiled code is fast and B) T-SQL code is slow. The problem with this is thatA) typical .Net managed code (C# and VB.net) isn't that fast (I can often beat the pants off the smaller routines with VBA macros(!)) and B) T-SQL is not nearly as slow as most developers assume.
Yes, C#/VB.net is expression-for-expression typically faster than T-SQL, but it's not an order of magnitude faster.
When you put all of this together what you get is: the solution to every T-SQL performance issue is NOT to convert it to SQLCLR. In fact SQLCLR is probably the fastest-performing solution in only a minority of cases (20-30% at best), even when you already start with a CPU-bound performance problem. That said, there are still many areas where SQLCLR is the fastest solution, assuming that the computation-per-invocation and/or per-row or per-byte is significant enough. Or, if CLR offers the ability to use algorithms or techniques that T-SQL just cannot do and thus can save a lot of CPU because of better algorithms.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 13, 2009 at 1:34 pm
Barry,
Thanks for your informative post. I saddens me that 10 years after Informix bought Illustra and merged their solution into that database platform, Microsoft still hasn't caught up. Even in DB2 back then, one still had the ability to choose how one's 3GL code would be implemented.
1. As a DLL that was loaded into the database engine. This was for trusted code so it would be incredibly fast since its invocation was a simple function call on the stack.
2. Loaded into an external process from which the function could be called.
As for managed code, there are plenty of benchmarks that show Java as fast or faster than C++ in a number of cases. I'll assume that the same case can be made for the .NET runtime. If not, then that's pretty bad.
Regards,
David
July 13, 2009 at 2:02 pm
David Brusowankin (7/13/2009)
Barry,Thanks for your informative post. I saddens me that 10 years after Informix bought Illustra and merged their solution into that database platform, Microsoft still hasn't caught up. Even in DB2 back then, one still had the ability to choose how one's 3GL code would be implemented.
1. As a DLL that was loaded into the database engine. This was for trusted code so it would be incredibly fast since its invocation was a simple function call on the stack.
2. Loaded into an external process from which the function could be called.
As for managed code, there are plenty of benchmarks that show Java as fast or faster than C++ in a number of cases. I'll assume that the same case can be made for the .NET runtime. If not, then that's pretty bad.
Regards,
David
I wouldn't lay this at the feet of .NET. Functions in SQL server have never been great performers, so external functions already start with a disadvantage. The thing you may find interesting is that when you compare function to function, CLR functions are often faster than the "straight" T-SQL functions; sadly though, in just about every case, neither function implementation can keep up with "straight" transactSQL. (As a note - you may find interesting gains from CLR stored procs over CLR functions, since CLR stored procs have access to streaming output, where functions do not).
Finally - the C++ to Java metrics don't necessarily port to .NET. Actual performance between the two is actually very close and has very little to do with the actual code (more with the app server serving up the code instead). There are tradeoffs in both directions, so don't drink the KoolAid that one side is clearly better.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 13, 2009 at 2:07 pm
Hi Matt,
I never thought that this was a .NET problem. I was only complaining that Microsoft didn't allow the CLR the option of being integrated into the database engine itself for speed.
Regards,
David
July 13, 2009 at 4:36 pm
David Brusowankin (7/13/2009)
...Thanks for your informative post. I saddens me that 10 years after Informix bought Illustra and merged their solution into that database platform, Microsoft still hasn't caught up.
Umm, first of all, you are taking what I said way stronger than I said it.
Secondly, there is nothing either new or original about this "feature" that you so vaguely describe. It has been around in one form or another for decades before either Illustra or ORM ever existed.
Thirdly, this would NOT be a step forward for Microsoft & SQL Server, but a step backwards into the cesspool of unreliable, unsupportable and untrackable customer modifications. There is a reason why every company that has ever offered this feature is either out of business or has backed off from it.
If you really want this kind of implementation-customization, then I suggest that you try MySQL, at least until SUN withdraws this capability. Or if you really want to do stuff like this in SQL Server, then write your own extended stored procedures, that's how they work. But in any event, and in any of these environments calling it "a simple function call" or "just a push onto the stack" is a gross oversimplification that hand-waves away the raft of extremely detailed and complex issues that have to be dealt with (like "how do I allocate & release memory safely & efficiently?"). In even the simplest of these, the calls are actually implemented as an indirect table of jump-vectors which has about 2x-4x the overhead of a "simple function call".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply