Since my Digital Point – Misunderstood post has proven such a hit, it occurs to me to share a hysterical post I found on SQL.NET. This idiot is writing “Death to TSQL” because of horrible SQL coding skills and because the man doesn’t seem to understand that SQL is not ASP. (Yes, you read that correctly – SQL and ASP Ancient.)
The article concludes that TSQL should never be used for stored procedures, that procs should never (“.EVER.”) use temp tables, and that the CLR (Common Language Runtime – aka .NET) should be used for 1980s style programming. Quoth the raven “In this case, it’s so overwhelmingly better than TSQL that I cannot recommend to anyone coding for SQL 2005 to use TSQL for stored procedures, even CRUD procs.“
CRUD = Create Read Update Delete. This went out of style by 1990. A client application should not be allowed to perform primitive operations like these for any number of reasons. Performance – we want chunky, not chatty interprocess protocols. Simplicity and maintainability – the client application should not have to understand the full implementation in the database. This should be hidden from and for them. (This is half the reason we use sprocs in the first place!!) Data quality and integrity – if the network connection is severed midway through a run, while half the creates have run and some of the updates, the data is left in an indeterminate state.
To stack the deck, Payton Byrd makes some shocking and unforgivable errors
- T-SQL code designed to be as resource intensive and slow as possible. Not the cursor use. The test code comes with this warning “This may not be the most efficient way to do this, but I don’t specialize in TSQL stored procs” which should disqualify the results for any thinking person.
- One reason the CLR code is assumed to perform faster than the TSQL code is because the .NET procedure uses a StringBuilder. In a related article, our anti hero tells us if the test data set were larger, the difference would be more pronounced, because of this. A StringBuilder is a hack, a work around for .NET because the CLR handles strings in a funny way. When your code creates and then alters a string, both copies live on in memory, because (due to threading) strings are immutable. Always creating and repointing references is so tiresome for the host computer that a class was written to give programmers an entirely different way of concatenating strings. SQL does not suffer this limitation. One of the key assumptions holding up the conclusion (aka guess) giving this article reason to be is fundamentally flawed.
- Wild conclusions “The performance results are absolutely stunning, the CLR stored procedure is 14 times faster than the TSQL stored procedure“
- More profound confusion “I’m willing to do more testing, but I think this pretty well proves the point that you can get much better performance from the CLR than from TSQL when you step beyond the simplest of stored procedures.” Apparently the meaning of complex was lost on this writer. Anybody who’s ever done any database work knows that complex != writing loop code for a set based language to put commas between values.
I’m not writing this to make fun of an incompetent developer. I’m writing to point out flaws of logic that seem all too common these days. The internet seems to have created a world where everyone is expected (even if only by themselves) to be an expert in some area. When a supposed expert doesn’t understand something, the rule this day in age seems to be that one should make assumptions and run with them. As Tripy would say, do you programming, then, when there’s no time left, do your analysis.
An even better way to cause yourself grief is to test an edge case and then assume the general world is described by some crazy scheme. If I wrote that there’s no reason ever to drive anywhere because I took a train yesterday, arrived at the station a moment before the vessel departed, then found I saved 5 minutes by not parking, people would rightly assume I had lost my wits.
The Texas Sharp Shooter Fallacy describes exactly this. Payton’s test case addresses a tremendously rare (and poorly coded) situation, one that might never be encountered by a senior database programmer. From this highly specific test he makes sweeping general conclusions. A test was engineered to provide support for a conclusion that had already been drawn. The test tells us nothing, except that we should avoid Payton’s blog if we aim to learn.