InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 12:38:58 GMT20http://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxCan't believe it.Turn out the SQL2000 solution is not that straight forward.Or, should I say, is very limited.If we change the function a little bit:[code="sql"]CREATE FUNCTION dbo.TimesN (@SomeINT int, @Multiplier int)RETURNS TABLE WITH SCHEMABINDING ASRETURN SELECT Doubled = @SomeINT * @MultiplierGO[/code]this still works fine: [code="sql"]SELECT * FROM dbo.TimesN(10, 2)[/code]Returns 20 as expected.But if you try to use it against a table :[code="sql"]DECLARE @Result INT--===== Begin measuring duration using GETDATEDECLARE @StartTime DATETIME;SELECT @StartTime = GETDATE()--===== Test the code using the "throw-away" variableSELECT @Result = (SELECT Doubled FROM dbo.TimesN(SomeNumber, 2)) FROM #TestTable--===== "Stop the timer" and report the duration PRINT DATEDIFF(ms,@StartTime,GETDATE())[/code]You are getting this:Msg 155, Level 15, State 1, Line 7'SomeNumber' is not a recognized OPTIMIZER LOCK HINTS option.Does not like 2 or more parameters, and that's it.Same code works perfectly in SQL2008.Tue, 22 Jan 2013 19:56:54 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxAh, Sergiy... my ol' friend. Long time no see. Thank you for stopping by and thanks for the great explanations. Yeah... I agree... most folks using front end code aren't going to notice any difference between using a Scalar UDF or an iTVF. Chances are, many won't even be able to measure the differences because of the extremely low rowcounts especially since I showed them why not to use SET STATISTICS to measure Scalar UDF performance.I do try to avoid Scalar UDFs, though, because most of the stuff I do is on staging tables where the cumulative times of multiple functions across many columns does start to make a difference especially when loading hundreds of large files in such a manner. 5% performance differences can really start to stack up in batch code.For GUI code, I write it as if it were large volume batch code because so many people will "leverage" any code they can find in a schedule pinch for their own batch code. If the iTVFs turn out to be a bit more complicated than some will understand, a well placed comment here and there in the code takes care of the problem. If not, then they probably shouldn't be trying to modify the code to begin with. :-)Of course, it's still possible to write crap code in an iTVF. iTVFs aren't a panancea.Thu, 17 Jan 2013 23:04:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Jeff Moden (8/5/2012)[/b][hr][quote][b]mmilodragovich (8/1/2012)[/b][hr]Thanks for the detailed and METHODICAL analysis. I once was troubleshooting a performance problem in a large, complicated application and it came down to a "set quoted identifer on" statement in a scalar function. The statement was not needed, was probably generated by a developer who used a right-click-on-object to generate the "create" statement. The function was being called massive number of times so even the slightest performance difference was hugely magnified. I've been wary ever since but know that knowledge is power, so thanks for the article.[/quote]You're welcome and thanks for the feedback. I've had similar not-easy-to-explain things happen in this wonderful world of T-SQL. [/quote]Actually, it's not so hard to explain.We all forget time to time that SQL is an interpreter, not a compiler.Therefore any piece if code is at least re-evaluated (worst case scenario - recompiled) every time it's invoked.Scalar function is applied in the final recordset. One time for an each entry a returned row.If you try to trace a query with a scalar functuion in it you'll see this:[code="sql"]SP:Starting SELECT @Result = dbo.TimesTwo(SomeNumber) FROM #TestTable ;SP:StmtStarting RETURN @SomeINT * 2SP:StmtCompleted RETURN @SomeINT * 2SP:Completed SELECT @Result = dbo.TimesTwo(SomeNumber) FROM #TestTable ;[/code]repeated as many times as number of rows returned by the query.At the same time, table function (parametrised view) in JOIN statement is invoked ONCE - so reavaluated (recompiled) once.That's where the performance difference comes from.SQL Server is just being busy creatind-recreating the scalar function in memory, that's all.In real life it should not make much difference, unless your system (querying) design is really stupid.There is no way a user wants to see more than a hundred rows on UI. Therefore you should never have million rows scenario in real-life application.And if you apply a WHERE clause to all those queries leaving under 1000 rows in the final recordsets you'll see that that difference is really minor, less than 5%.Not worth any attention, really.Same case as explicitly dropping #tables in a SP code - if you do a test run on only create/drop operations in a SP and execute it 10000 than overheade be really noticable. But in a real life procedures the impact will be totally unnoticable. Well, there will a negative impact on performance when you explicitely drop a # table at the end of a SP, but it's totally forgivable for sake of one's style (not my style! :-)) In real life you should not have millin-row SELECT statements, so overhead of a scalar function can be ignored, if it's more comfortable to code.Unless you're doing some kind of Data Transformation task, but even then - it's one-off, and even a big overhead can be tolarated.Views used for reporting - yes, that's where you should be concerned.And not using UDF's in SELECT part of the queries must be a mandatory rule.Thu, 17 Jan 2013 21:47:53 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]RBarryYoung (9/25/2012)[/b][hr][quote][b]SQL Kiwi (9/23/2012)[/b][hr]Removed. (I quoted Jeff partially quoting Barry without reading all the way back to see that Barry had already referenced the document I wanted to mention).[/quote]Hey! I got something [strike]write[/strike] right! :-D[/quote](Doggone it! :angry:)Tue, 25 Sep 2012 21:55:35 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]SQL Kiwi (9/23/2012)[/b][hr]Removed. (I quoted Jeff partially quoting Barry without reading all the way back to see that Barry had already referenced the document I wanted to mention).[/quote]Hey! I got something write! :-DTue, 25 Sep 2012 21:53:33 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]rragno (9/23/2012)[/b][hr]Unlike the C# code, this always takes 0 ms to execute. It appears to simply analyze the code and conclude that only the last iteration of the loop will matter. If you change the Mult function to accumulate instead, it no longer can do that. Still, it then takes only 6 ms to execute, which is much faster than the C# version.)[/quote]So the Java optimiser is somewhat better than the C# optimiser. Of course I would have expected pre-compiled code in either of these languages to run in 0 ms for the version with the accumulation as well, so these numbers suggest that optimisation techniques (for procedural languages like these) have deteriorated badly in the last four decades, or everything is doing recompilation all the time, or people have decide that some of the possible optimisations are rather pointless. And it seems pretty clear that the C# optimisation [b]is[/b] really bad.Sun, 23 Sep 2012 19:16:54 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxYes, I was indeed talking about two different things. One is the CLR-in-SQL function, and the other is a pure C# application.I think the C# application was fairly well protected against being optimized out. I didn't realize I never attached it; I can see if I have it around. I inspected the generated IL and the runtime execution traces. I also observed that it scaled linearly with the number of iterations. Just to be certain, I also accumulate a value in the loop body, and print out that accumulated value at the end.In my experience, the CLR JIT optimizer rarely optimizes out entire loop bodies. It just does not go through that work to do the analysis, likely under the assumption that it has little benefit for real world (non-benchmark) cases. (The Oracle JVM actually does do such eliminations fairly often. In fact, I find the Oracle JVM to perform stronger optimizations across the board. However, with C# and the CLR, you have pointers and non-heap types and similar things, and thus have more ability to optimize what you find necessary. Basically, as a rule of thumb, performance almost always follows: simple C# < java < optimized C#.)It seems like there are further limits on really making the SQL CLR functionality match the performance of pure CLR code for numerical computation. For example, when passing over a large set of primitive values, it is not fast to make any call at all for each and every entry. It would commonly be faster to first shuttle the values into a primitive array and then execute on that, for reasons of memory locality and call overhead. Further, SQL Server could in theory persist such copies at will, as long as it treated them as a cache and invalidated them. For data warehousing scenarios this would work well, since updates are rare, and that is where high-performance numerical computation is common. Unfortunately, this kind of usage has not been a priority. I suppose there just isn't much customer demand for a unified system... As it is, I would be cautious about overdoing any complex computations that are not core SQL Server scenarios. For many such cases of numerical computation, the optimal design (often by orders of magnitude) will be: dump the data from SQL Server, compute, spew data into SQL Server.Now, optimizing large-scale extraction and import is a different topic, and is also far from ideal performance with standard mechanisms...I wish we didn't have to consider all of this complexity. There is no reason why we should have to worry about making CLR versions of TSQL functions that could be created by a template transform, to get high performance. We also shouldn't have to worry that the best we can do for some large-scale computation on a data warehouse might be 1000x slower than what we could do if we dump the data, even when highly-optimized. This really reduces the flexibility and simplicity of systems built around SQL Server for a wide variety of cases. Of course, again, these are not the core customer cases, so...I assume the standalone C# code I was measuring was something like (although more JIT warming, etc. would be desirable):[code="other"]using System;using System.Diagnostics;namespace Test{ class Program { static void Main(string[] args) { int[] data = new int[10000000]; for (int i = 0; i < data.Length; i++) { data[i] = i; } int acc = 0; acc += Mult(data); acc = 0; Stopwatch time = Stopwatch.StartNew(); acc += Mult(data); time.Stop(); Console.WriteLine("Time: " + time.ElapsedMilliseconds + " ms [" + acc + "]"); acc = 0; time.Restart(); acc += Mult(data); time.Stop(); Console.WriteLine("Time: " + time.ElapsedMilliseconds + " ms [" + acc + "]"); acc = 0; time.Restart(); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); time.Stop(); Console.WriteLine("Time: " + time.ElapsedMilliseconds + " ms [" + acc + "]"); } public static int Mult(int[] data) { int res = 0; foreach (int d in data) { //res += Mult(d); res = Mult(d); } return res; } public static int Mult(int d) { return d * 2; } }}[/code](Incidentally, it is out of scope for this forum, but to back up my aside above... Here is the rough code in Java:[code="other"] public class Test { public static void main(String[] args) { int[] data = new int[10000000]; for (int i = 0; i < data.length; i++) { data[i] = i; } int acc = 0; acc += Mult(data); acc = 0; long time = System.nanoTime(); acc += Mult(data); time = System.nanoTime() - time; System.out.println("Time: " + (time / 1000000) + " ms [" + acc + "]"); acc = 0; time = System.nanoTime(); acc += Mult(data); time = System.nanoTime() - time; System.out.println("Time: " + (time / 1000000) + " ms [" + acc + "]"); acc = 0; time = System.nanoTime(); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); acc += Mult(data); time = System.nanoTime() - time; System.out.println("Time: " + (time / 1000000) + " ms [" + acc + "]"); } public static int Mult(int[] data) { int res = 0; for (int d : data) { //res += Mult(d); res = Mult(d); } return res; } public static int Mult(int d) { return d * 2; } }[/code]Unlike the C# code, this always takes 0 ms to execute. It appears to simply analyze the code and conclude that only the last iteration of the loop will matter. If you change the Mult function to accumulate instead, it no longer can do that. Still, it then takes only 6 ms to execute, which is much faster than the C# version.)Sun, 23 Sep 2012 16:15:07 GMTrragnohttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]SQL Kiwi (9/23/2012)[/b][hr]Right. Working back through the comments completed :-)Thank you Jeff for so many mentions in your fine article![/quote]The articles you wrote on CROSS APPLY are the best on the net IMHO. Thank you for writing them. They give the rest of us a good spot to refer folks to on the subject.Sun, 23 Sep 2012 15:07:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Jeff Moden (9/23/2012)[/b][hr]Thanks for the explanation, Paul. It's been a long time (6502 days) since I've even gone near machine language.[/quote]Me too! My first ever computer was a Commodore VIC-20 (1MHz 8-bit 6502 CPU) I bought with paper-round money.Sun, 23 Sep 2012 15:01:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]SQL Kiwi (9/23/2012)[/b][hr][quote][b]Jeff Moden (9/23/2012)[/b][hr][quote][b]SQL Kiwi (9/23/2012)[/b][hr]The C# code will indeed be compiled down to just one machine-code instruction (which may take less than one CPU cycle to execute)...[/quote]Are you sure, Paul? IIRC, even a simple JMP instruction takes at least 2 clock cycles.[/quote]I was referring to the fact that modern processors employ tricks like prefetch, pipelining, branch prediction, speculative execution, out-of-order execution...and so on...meaning that multiple instructions can be retired per clock. An simple instruction that multiplies by two (which may well be optimized to a left shift as Tom mentions) may be timed at 1 clock but might well take less than that. e.g. [url]http://stackoverflow.com/questions/692718/how-to-find-cpu-cycle-for-an-assembly-instruction[/url][/quote]Thanks for the explanation, Paul. It's been a long time (6502 days) since I've even gone near machine language. The processors certainly have come a long way at that level.Sun, 23 Sep 2012 14:48:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxRight. Working back through the comments completed :-)Thank you Jeff for so many mentions in your fine article!Sun, 23 Sep 2012 12:47:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]L' Eomot Inversé (9/23/2012)[/b][hr]I think you misunderstood me Paul - my fault for not expressing myself more clearly.rragnmo seemed to me to be talking about two distinct thigs - a chunk of pure C# measured on its own (his first paragraph) and a CLR function embedded into SQL (his second paragraph). I was concerned only with his second paragraph. When an attempt is made to do the whole thing in c# (or Fortran, or any other procedural language) there's a risk that the optimiser notices that the first one million minus one function calls are irrelevant so it leaves them out. This doesn't happen with SQL, currently (but it might in the furture) so generally there';s an attempt to prevent the optimiser from doing this to avoid meaningless comparisons. My "one multiplication" was for the whole process, which needed only one multiplication (probably actually a left shift, depending on the target machine, as it's a multiplication by 2) instead of a million multiplications. If that sort of optimisation was happening, we would be comparing a forest with a single tree.[/quote]Oh I see! Yes, sorry I did misread your question. You're right that this sort of optimization doesn't happen now, and I doubt it ever will - unless SQL Server's interpreted T-SQL language is completed replaced by .NET (unlikely, perhaps sadly).Sun, 23 Sep 2012 12:45:56 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Jeff Moden (9/23/2012)[/b][hr][quote][b]SQL Kiwi (9/23/2012)[/b][hr]The C# code will indeed be compiled down to just one machine-code instruction (which may take less than one CPU cycle to execute)...[/quote]Are you sure, Paul? IIRC, even a simple JMP instruction takes at least 2 clock cycles.[/quote]I was referring to the fact that modern processors employ tricks like prefetch, pipelining, branch prediction, speculative execution, out-of-order execution...and so on...meaning that multiple instructions can be retired per clock. An simple instruction that multiplies by two (which may well be optimized to a left shift as Tom mentions) may be timed at 1 clock but might well take less than that. e.g. [url]http://stackoverflow.com/questions/692718/how-to-find-cpu-cycle-for-an-assembly-instruction[/url]Sun, 23 Sep 2012 12:38:52 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]milldyce (8/1/2012)[/b][hr]A bit of a tangent, but I remember a number of queries for which response times exploded when trying to view the Execution Plan.[/quote]Requesting an actual execution plan causes SQL Server to insert extra (invisible) operators into the executable to measure the things like actual row counts, rebinds, rewinds...and so on. The output from all this data collection is converted into XML at the end of execution and returned to the client. Understandably, all these extra operators and activity generate significant overhead.Sun, 23 Sep 2012 12:34:04 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]RichB (8/1/2012)[/b][hr]Have you tried looking at these with a server side trace? I'd be very curious to know whether that can have a similar problem.[/quote]Yes a server-side trace has very similar effects compared with STATISTICS TIME for similar reasons. Your question reminded me of a real issue here on SSC from several years ago: [url]http://www.sqlservercentral.com/Forums/FindPost886822.aspx[/url]Sun, 23 Sep 2012 12:31:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]G Bryant McClellan (8/1/2012)[/b][hr]While every testing method negatively affects the performance of the thing being tested by making the tester part of the experiment, some are obviously worse than others. I'd be interested to hear from some of the SQL Internals authors if they have some insight into how SET STATISTICS TIME ON works and how its use may or may not affect other situations where it is used. Paul? Kalen? Are you thinking about this yet?[/quote]Having STATISTICS TIME ON causes SQL Server to measure things every time a T-SQL frame is entered and exited. The way T-SQL scalar functions are implemented now (with BEGIN...END syntax) an (expensive!) new T-SQL frame is created on every call.[url]http://blogs.msdn.com/b/sqlclrperf/archive/2007/03/08/scalar-functions.aspx[/url]Sun, 23 Sep 2012 12:29:17 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]SQL Kiwi (9/23/2012)[/b][hr]The C# code will indeed be compiled down to just one machine-code instruction (which may take less than one CPU cycle to execute)...[/quote]Are you sure, Paul? IIRC, even a simple JMP instruction takes at least 2 clock cycles.Sun, 23 Sep 2012 12:27:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]SQL Kiwi (9/23/2012)[/b][hr][quote][b]L' Eomot Inversé (9/23/2012)[/b][hr]It would be interesting to see the C# so that we could see what it is that's being optimised. Clearly either the C# optimiser is really awful, or the C# is deliberately written in such a way as to avoid the most insane optimisations. For example, it would be nice to know how many multiplications the optimised code does, it would be nice to know how many values it reads from an array, and so on. Decades ago I saw a Fortran system which would have run this, if naively written so that the optimiser could be really clever, in about 10 microseconds. Of course it would have done exactly one multiplication.[/quote]Please be assured that the .NET compiler and optimizer is state-of-the-art. The overhead is caused by CLR being in hosted in SQL Server. The C# code will indeed be compiled down to just one machine-code instruction (which may take less than one CPU cycle to execute) but it takes time to connect things up and switch between SQL Server and the hosted environment. [/quote]I think you misunderstood me Paul - my fault for not expressing myself more clearly.rragnmo seemed to me to be talking about two distinct thigs - a chunk of pure C# measured on its own (his first paragraph) and a CLR function embedded into SQL (his second paragraph). I was concerned only with his second paragraph. When an attempt is made to do the whole thing in c# (or Fortran, or any other procedural language) there's a risk that the optimiser notices that the first one million minus one function calls are irrelevant so it leaves them out. This doesn't happen with SQL, currently (but it might in the furture) so generally there';s an attempt to prevent the optimiser from doing this to avoid meaningless comparisons. My "one multiplication" was for the whole process, which needed only one multiplication (probably actually a left shift, depending on the target machine, as it's a multiplication by 2) instead of a million multiplications. If that sort of optimisation was happening, we would be comparing a forest with a single tree.Having seen people make utterly wrong decisions based on run times of tasks which could be optimied out just about completely, I just wondered how well protected rragnmo's measurements were aginst that sort of thing. One of the things he said in his comment seemed to indicate that he did have had it in mind, but was concerned that he might not have done enough to avoid it.Sun, 23 Sep 2012 12:19:57 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxRemoved. (I quoted Jeff partially quoting Barry without reading all the way back to see that Barry had already referenced the document I wanted to mention).Sun, 23 Sep 2012 12:17:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]L' Eomot Inversé (9/23/2012)[/b][hr]Great article. I will have to remember the content, because it could make a big difference in many things.[/quote]Thanks for the feedback, Tom. I appreciate it.[quote]But you worried me a little in one of your comments, because some people may interpret as saying that performnce is the most important thing (which I'm sure you didn't mean at all:[quote][b]Jeff Moden (8/5/2012)[/b][hr]Shifting gears, I'm one of the first to say that performance is always a prime factor ....[/quote]For me, correctness comes before performance; [/quote]Oh, for me to. That's why I said performance is only "a" prime factor. Perhaps it's wrong to do so but I take it for granted that everyone knows the answer has to be correct before any other factor comes into play. My personal mantra on the subject of code development is "Make it work, make it fast, make it pretty and it ain't done 'til it's pretty." :-)Sun, 23 Sep 2012 12:10:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]rragno (8/5/2012)[/b][hr]That is the C# code! The cruft needed to deploy might be difficult, but they made some things elegant. That method I posted is the whole method. I can send over a little package... Once you compile that into a DLL, it is a one-liner to add the assembly into your DB, and then another to declare a function that calls this method. Otherwise, the testing is identical to the TSQL function.[/quote]This is a test I have performed many times myself. T-SQL scalar user-defined functions are implemented so poorly that even returning an unmodified value is faster in a CLR scalar function that a (non-inline) T-SQL one. Another highlight is that CLR scalar functions can use parallelism whereas the T-SQL equivalent forces the whole execution plan to run serially. Nastily, a query that references a table that incorporates a T-SQL scalar function will always run serially even if the column that uses the T-SQL function is not referenced anywhere in the query!I don't have a multiply-by-two assembly and function to hand, but I did post a CLR scalar function to sum the digits of an integer a couple of days ago that beats even in-line T-SQL (no functions at all):[url]http://www.sqlservercentral.com/Forums/FindPost1362532.aspx[/url]T-SQL is an interpreted language, and intrinsic functions are generally slow. It doesn't take much at all to make a CLR scalar function perform faster (though in many cases it is not worth the effort). If and when SQL Server includes the ability to in-line CLR code within T-SQL, that might change. I think this feature is part of the draft SQL standard.Sun, 23 Sep 2012 12:10:02 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]L' Eomot Inversé (9/23/2012)[/b][hr]It would be interesting to see the C# so that we could see what it is that's being optimised. Clearly either the C# optimiser is really awful, or the C# is deliberately written in such a way as to avoid the most insane optimisations. For example, it would be nice to know how many multiplications the optimised code does, it would be nice to know how many values it reads from an array, and so on. Decades ago I saw a Fortran system which would have run this, if naively written so that the optimiser could be really clever, in about 10 microseconds. Of course it would have done exactly one multiplication.[/quote]Please be assured that the .NET compiler and optimizer is state-of-the-art. The overhead is caused by CLR being in hosted in SQL Server. The C# code will indeed be compiled down to just one machine-code instruction (which may take less than one CPU cycle to execute) but it takes time to connect things up and switch between SQL Server and the hosted environment. Nevertheless, [url=http://msdn.microsoft.com/en-us/library/ms131075.aspx]as Books Online says[/url], [i]"CLR functions benefit from a quicker invocation path than that of Transact-SQL user-defined functions. Additionally, managed code has a decisive performance advantage over Transact-SQL in terms of procedural code, computation, and string manipulation.[/i]".Sun, 23 Sep 2012 12:00:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]GSquared (8/1/2012)[/b][hr]Post-script: Name-case coding will be wrong much of the time. Bill O'Malley will likely end up as Bill O'malley, or Bob's will end up as Bob'S, unless you code some seriously smart algorithms into it. Any code that handles MacDonald correctly will probably mangle Macy or Macey, and vice-versa. The more "correct" you make it, the more complex it will be, and the slower it will run, and (likely) the buggier it will get.[/quote]It's worse than that. Often the "correct" capitalisation is different from one person to the next; with surnames like MacinTosh/Macintosh/MacIntosh, MaclEan/MacLean, Mackay/MacKay, and Macintyre/MacinTyre/MacIntyre there's no imaginable way you can determine the "correct" capitalisation in any sort of scaler function, you just have to hope it was entered right in the first place. So the task is generally impossible for surnames of Gaelic (Scottish, Manx, or Irish) origin (the capitalisation in the Gaelic forms is of course determinate but that doesn't help with the English forms - for example in Macintyre only the m and the S are capitalised, which doesn't carry over very well into the English transliteration since the S isn't there. Names with other non-English origins display the same sort of problem.Sun, 23 Sep 2012 11:22:56 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]rragno (8/1/2012)[/b][hr]It is irritating how big the gap is between the performance here and high-performance code, though. Why should we have to be impressed that multiplying a million numbers takes 800 ms? A simplistic implementation of the same process, applying a function call, in C# clocks in at 0.8 ms on my old laptop. That is 1000x faster. Obviously, there are reasons for some slowdown, but this magnitude is hard to accept.[/quote]It would be interesting to see the C# so that we could see what it is that's being optimised. Clearly either the C# optimiser is really awful, or the C# is deliberately written in such a way as to avoid the most insane optimisations. For example, it would be nice to know how many multiplications the optimised code does, it would be nice to know how many values it reads from an array, and so on. Decades ago I saw a Fortran system which would have run this, if naively written so that the optimiser could be really clever, in about 10 microseconds. Of course it would have done exactly one multiplication.Sun, 23 Sep 2012 11:19:47 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxGreat article. I will have to remember the content, because it could make a big difference in many things.But you worried me a little in one of your comments, because some people may interpret as saying that performnce is the most important thing (which I'm sure you didn't mean at all:[quote][b]Jeff Moden (8/5/2012)[/b][hr]Shifting gears, I'm one of the first to say that performance is always a prime factor ....[/quote]For me, correctness comes before performance; I don't care how fast I can produce the wrong answer; yes, performance is important - but it is only important once I can produce the right answer. And next after that might be reusability (or adaptability), for the cases where I'm going to have to do the same thing (or pretty much the same thing) in dozens of places, or it might be flexibility (ease of changing - for when I know the requirements are a heap of wet shifting quicksand invented by sales consultants who haven't a clue what is needed), or (rarely, when the requirement is rock solid and the code is only to be used in one context) performance. So performance is never first and rarely second.Sun, 23 Sep 2012 11:10:28 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxJeff -I read this article last week and it was definitely an eye-opener. Great stuff!It is always exceedingly important to know your tools and most especially their limitations.Sorry I didn't get around to saying this until now. Thought I had but was busy last week and it must have slipped my mind.Wed, 08 Aug 2012 22:10:11 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Jeff Moden (8/7/2012)[/b][hr]Seriously, though. You did a really nice job. Thanks for joining this discussion.[/quote]The pleasure is always mine :cool: It is a great honor to receive such a generous compliment from you :Wow:Tue, 07 Aug 2012 23:48:42 GMTUsman Butthttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Usman Butt (8/7/2012)[/b][hr][quote][b]Jeff Moden (8/6/2012)[/b][hr][quote][b]Usman Butt (8/6/2012)[/b][hr]Can I safely assume that you have approved my Anti-RBAR alliance membership? ;-)[/quote]Heh... make no "Butts" about it. :-) I know, I know... you've had "Mo-den" enough of those kinds of jokes. :w00t:[/quote]:laugh: Since living in this "Mo-den" era we had no other option but to enjoy such creativity ;-) Keep them coming. I wonder haven't you got any request to write a super joke book yet :hehe:[/quote]Seriously, though. You did a really nice job. Thanks for joining this discussion.Tue, 07 Aug 2012 06:18:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]GSquared (8/6/2012)[/b][hr]And the key to the article was the bit about inline 1X1 UDFs as "scalar" functions with better performance, and the point about Set Stats. Regardless of ICaps functionality, those are both very good points.[/quote]Can't agree more. The prime focus should be on these very informative key points. Tue, 07 Aug 2012 04:15:14 GMTUsman Butthttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Jeff Moden (8/6/2012)[/b][hr][quote][b]Usman Butt (8/6/2012)[/b][hr]Can I safely assume that you have approved my Anti-RBAR alliance membership? ;-)[/quote]Heh... make no "Butts" about it. :-) I know, I know... you've had "Mo-den" enough of those kinds of jokes. :w00t:[/quote]:laugh: Since living in this "Mo-den" era we had no other option but to enjoy such creativity ;-) Keep them coming. I wonder haven't you got any request to write a super joke book yet :hehe:Tue, 07 Aug 2012 03:59:11 GMTUsman Butthttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Jeff Moden (8/6/2012)[/b][hr][quote][b]GSquared (8/6/2012)[/b][hr]Of course, if you really want to see how complex this kind of thing can get, try to make it deal with things like "USA" (or worse, "USA's"), or names like "deGas", "MacDonald" (make sure it doesn't blow up on "Macy" when you do that one), and so on.There's a lot to this kind of thing.[/quote]Heh... since I'm getting older, "deGas" has a special meaning to me, lately. :-DI agree... Initial Caps is a whole lot easier than Proper or Title casing. And as soon as you make a rule to properly case "MacDonald", someone will want a lower case version. Even AI can't handle such exceptions. You need human intervention or for humans to do it right the first time.[/quote]I was just wondering if anyone would draw a connection between McDonald's and deGas, honestly. :w00t:Anyway, it's a good ICaps function. Slow on longer strings, but that's going to be a very rare real-world need.And the key to the article was the bit about inline 1X1 UDFs as "scalar" functions with better performance, and the point about Set Stats. Regardless of ICaps functionality, those are both very good points.Mon, 06 Aug 2012 12:05:03 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]GSquared (8/6/2012)[/b][hr]Of course, if you really want to see how complex this kind of thing can get, try to make it deal with things like "USA" (or worse, "USA's"), or names like "deGas", "MacDonald" (make sure it doesn't blow up on "Macy" when you do that one), and so on.There's a lot to this kind of thing.[/quote]Heh... since I'm getting older, "deGas" has a special meaning to me, lately. :-DI agree... Initial Caps is a whole lot easier than Proper or Title casing. And as soon as you make a rule to properly case "MacDonald", someone will want a lower case version. Even AI can't handle such exceptions. You need human intervention or for humans to do it right the first time.Mon, 06 Aug 2012 11:50:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Usman Butt (8/6/2012)[/b][hr]Can I safely assume that you have approved my Anti-RBAR alliance membership? ;-)[/quote]Heh... make no "Butts" about it. :-) I know, I know... you've had "Mo-den" enough of those kinds of jokes. :w00t:Mon, 06 Aug 2012 11:46:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxOf course, if you really want to see how complex this kind of thing can get, try to make it deal with things like "USA" (or worse, "USA's"), or names like "deGas", "MacDonald" (make sure it doesn't blow up on "Macy" when you do that one), and so on.There's a lot to this kind of thing.Mon, 06 Aug 2012 06:20:19 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Jeff Moden (8/5/2012)[/b][hr][quote][b]mmilodragovich (8/1/2012)[/b][hr]Thanks for the detailed and METHODICAL analysis. I once was troubleshooting a performance problem in a large, complicated application and it came down to a "set quoted identifer on" statement in a scalar function. The statement was not needed, was probably generated by a developer who used a right-click-on-object to generate the "create" statement. The function was being called massive number of times so even the slightest performance difference was hugely magnified. I've been wary ever since but know that knowledge is power, so thanks for the article.[/quote]You're welcome and thanks for the feedback. I've had similar not-easy-to-explain things happen in this wonderful world of T-SQL. For example, I discover quite by accident about a week ago that the [b]current rendition of the DelimitedSplit8K and 4K functions from the "Tally OH!" article actually run faster if you select both of its outputs (ItemNumber and Item) rather than just the Item[/b].[/quote]Aghhh...I guess this is another debatable topic. I have observed it oppositely for most part of my testing (I hope you remember I did some working on this). At that time I asked the question to myself that is the ItemNumber really needed since the sequence of the strings were not relevant in my case? So I did omit the ItemNumber (Even only while executing the query as the QO was smart enough to remove the ItemNumber-ing ) and hence saw performance gain as some cpu cycles were saved. But on the other hand with larger strings especially while testing the VARCHAR(MAX) version, I intentionally converted the ItemNumber from data-type BIGINT to NUMERIC(38) etc to give more memory to the query execution. This way I wanted to avoid any tempdb spilling etc.So as always "it depends" ;-) I remember, I did find some other interesting things but my bad I did not document them (which is a must for people like me with such short memory :( ).Mon, 06 Aug 2012 01:03:46 GMTUsman Butthttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Jeff Moden (8/5/2012)[/b][hr]I can't find the faulty experiment I did but it does't matter now. Usman's fine bit of T-SQL prestidigitation will do.[/quote]Can I safely assume that you have approved my Anti-RBAR alliance membership? ;-)[quote]That being said, I believe this is one of those places where a CLR and some REGEX may be faster.Of course, this isn't proper "Title" case... words like "of" and "the" shouldn't be capitalized unless they're the first word but hat's off on making a fast T-SQL version of "Initial Caps".[/quote]Totally agreed. "Intitial Cap"-ping needs prior English grammar knowledge etc embedded in the system and TSQL is definitely not the best place to handle it.Mon, 06 Aug 2012 00:43:20 GMTUsman Butthttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxI can't find the faulty experiment I did but it does't matter now. Usman's fine bit of T-SQL prestidigitation will do.I made a couple of minor changes as noted in the revision history. I haven't tested it for all possibilities but is does seem to handle apostropied names, possessives, and some oddities such as the "Roaring 20's" with only a minor decrease in overall performance. I also tweaked the code with some of the other suggestions made and some of my own.[code="sql"]DROP FUNCTION [dbo].[InitialCap]GO CREATE FUNCTION dbo.InitialCap/******************************************************************************* Purpose: Capitalize the first letter of the given string and any letter that follows a non-letter character except for those letters which follow a single quote. All other letters will be changed to lower case. Usage notes: 1. This is an "Inline Scalar Function" or "iSF" for short. In reality, it's an iTVF (Inline Table Valued Function) that returns a single value. It must be used either in a FROM clause or a CROSS APPLY because it cannot be called like a normal Scalar User Defined Function. 2. The key to understanding this function is that t.N is always 1 less than the character being concatenated. 3. This code will correctly handle apostrophied and possessive names like O'Hare's, up to 2 letter contractions, and "odd" things like the "Roaring 20's". Revision History: Rev 00 - 02 Aug 2012 - Usman Butt - Original Code Rev 01 - 05 Aug 2012 - Jeff Moden - Reformatted and additional comments added. - Made a couple of minor optimizations including handling some of the slower, non-default collations. - Altered the code to handle things found in "User Note #3) above.*******************************************************************************/--===== Declare the IO of the function (@String VARCHAR(8000))RETURNS TABLE WITH SCHEMABINDING AS RETURN--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover VARCHAR(8000). WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of -- rows right up front for both a performance gain and -- prevention of accidental "overruns". SELECT TOP (ISNULL(DATALENGTH(@String),0)) --Keeps trailing spaces ROW_NUMBER() OVER (ORDER BY [N]) FROM E4 )--===== Force the first character of the string to upper case. -- Obviously, non-letter values will not be changed by UPPER. SELECT InitialCapString = UPPER(LEFT(@String,1)) + ( --=== If the current character in the given string isn't a letter then -- concatenate the next character as an UPPER case character. -- Otherwise, make it lower case character. -- The COLLATE clause speeds up non-default collations. SELECT CASE WHEN SUBSTRING(@String, t.N , 1) COLLATE Latin1_General_BIN LIKE '[^A-Za-z'']' COLLATE Latin1_General_BIN OR SUBSTRING(@String, t.N , 4) COLLATE Latin1_General_BIN LIKE '[^A-Za-z][A-Za-z][A-Za-z][A-Za-z]' COLLATE Latin1_General_BIN THEN UPPER(SUBSTRING(@String, t.N+1, 1)) ELSE LOWER(SUBSTRING(@String, t.N+1, 1)) END FROM cteTally t --No WHERE clause needed because of TOP above ORDER BY t.N FOR XML PATH(''), TYPE ).value('text()[1]', 'varchar(8000)');GO[/code]The following code...[code="sql"] SELECT * FROM dbo.InitialCap('DON''T YOU THINK THEY''RE O''HARE''S VERSION OF THE ROARING 20''S') SELECT * FROM dbo.InitialCap('don''t you think they''re o''hare''s version of the roaring 20''s')[/code]... returns the following output.[code="plain"]InitialCapString------------------------------------------------------------Don't You Think They're O'Hare's Version Of The Roaring 20's(1 row(s) affected)InitialCapString------------------------------------------------------------Don't You Think They're O'Hare's Version Of The Roaring 20's(1 row(s) affected)[/code]That being said, I believe this is one of those places where a CLR and some REGEX may be faster.Of course, this isn't proper "Title" case... words like "of" and "the" shouldn't be capitalized unless they're the first word but hat's off on making a fast T-SQL version of "Initial Caps".Sun, 05 Aug 2012 22:00:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspxThat is the C# code! The cruft needed to deploy might be difficult, but they made some things elegant. That method I posted is the whole method.I can send over a little package... Once you compile that into a DLL, it is a one-liner to add the assembly into your DB, and then another to declare a function that calls this method. Otherwise, the testing is identical to the TSQL function.The statistics might not record much for CLR functions, but the wall clock time is also consistent - it seriously stomps on the TSQL time.The standalone C# code I threw together, to make it 1000x faster without using the database at all, I can dig up; it isn't that special, of course.Sun, 05 Aug 2012 20:23:09 GMTrragnohttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]rragno (8/1/2012)[/b][hr]It is irritating how big the gap is between the performance here and high-performance code, though. Why should we have to be impressed that multiplying a million numbers takes 800 ms? A simplistic implementation of the same process, applying a function call, in C# clocks in at 0.8 ms on my old laptop. That is 1000x faster. Obviously, there are reasons for some slowdown, but this magnitude is hard to accept.Also skipped here is simply using a CLR-implemented function. Something like:[code="other"][Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]public static SqlInt32 Mult2(SqlInt32 n){ return n * 2;}[/code]This gives much better performance.My measurements on a modern machine, running SQL Server 2012:[code="other"]Baseline (questionable due to optimization, but...): 414 ms. 150 ms - w/o SET STATISTICSTSQL Function: 2403 ms. 1653 ms - w/o SET STATISTICSiSF Function: 263 ms. 203 ms - w/o SET STATISTICSCLR Function: 349 ms. 346 ms - w/o SET STATISTICS[/code]While the iSF beats the CLR function, the CLR version can be used just like the normal TSQL one.The optimizer could be ruining all of these measurements, of course.And as I started with, all of this is orders of magnitude away from simple code for the same task.[/quote]Any chance of seeing the C# code you used for the multiplication test from your first paragraph above? Also, thank you very much for posting your timing results. It really does show the impact that SET STATISTICS makes even on inline code.IIRC, SET STATISTICS doesn't measure resources consumed by the CLR itself but don't take my word for it. I just can't find the URL where I saw someone prove it.Sun, 05 Aug 2012 17:08:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx[quote][b]Usman Butt (8/2/2012)[/b][hr]As always great article. But I am sure there must be something up your sleeve ;-) After following you so much I am not believing that you would be using the scalar function at all ([b]Unless the String parameter is of small length[/b]). [/quote]I wish I could say the it was something so elegant but it's nothing like that. Somewhere along the line, I apparently made a serious mistake in testing. :blush: Thanks to you folks, I'll go back (It was more than a year ago) and see what the heck I did wrong. I'll be sure to correct the article, as well.Sun, 05 Aug 2012 16:35:12 GMTJeff Moden