Alexander The Great

March 19, 2008

C# and VB.NET Stored Procedures Suck

Filed under: Programming — alexanderthegreatest @ 1:59 pm
Tags: , , , , , , , ,

Somebody found the blog you’re reading by searching Google for evidence that “c# stored procedures suck“. (Probably this post, possibly this one.) This search phrase isn’t exactly true – C# procedures by themselves can be a good thing, under the rarest of circumstances. The pain comes when people use them in all the billions of ways they aren’t suited to. On the other hand, VB sprocs always suck. 🙂

First, you don’t work with them in the same way as normal procedures. At best you can think of them as encrypted procs, and that’s if you don’t have to deploy them. The way to do that is compiling through the command line (CSC.EXE /t:library your_csharp_code_file.cs), then you have to write SQL pointing to a location in the file system! First you run a Create Assembly [SysName] From [File Path To DLL] query and then you create your procedure pointing to the fully qualified name of the method in the code. Don’t forget to make it public accessibility and decorate it with the SqlProcedure attribute. Don’t even think about lieing if a function isn’t deterministic – SQL will believe you!!!

If you could type your C# or VB code into SQL Server Management Studio, hit F5, and have it compiled and deployed to the current database, and then sp_helptext a .NET proc, it wouldn’t hurt maintainability of code. Think of the poor DBA trying to track down the cause of bad performance when code they have no knowledge or view into is involved.

SQL Server has its own memory management, its own threading and scheduling, it’s own locking, and talks to the operating system. It’s a system. You can tap into it with the Common Language Runtime, but the CLR runs in a very different way, and the interop causes sometimes great overhead. The managed heap is there in all cases but God help you if your code uses threads or calls win32 directly.

When .NET Procs Don’t Suck

It seems like the answer would be “when you don’t know T-SQL very well” but that’s how production systems are brought to a limp. The CLR is absolutely not a replacement for T-SQL. It’s an add on for some very specific scenarios.

String Manipulation

As a programmer at a consulting firm I can’t even count how many times I’ve seen transact query language being used to parse out a comma delimited list. This is attrocious. The code will make you vomit, and the server isn’t that much happier, it’s like asking Babe Ruth to throw your dog a ball.

.NET code is a lot more elegant at this kind of task, and it’s optimized to do this sort of thing. Not strings in particular, actually, because they’re immutable to make threading easier. But logic and object manipulation are the domain of coding systems other than structured query language and it’s extensions.


The CSV string should really be XML, whether you use regular SQL or decide to write in Java Sharp #. This way you don’t have to worry about character limits (expecially in unicode!). You don’t have to write a parser.
SQL Server 2000 and 2005 have support for XML, using Microsoft’s XML DOM or MSXML. Purely from a SQL code stand point, there’s an easy way and a cumbersome way to do this. You can grab the values directly with XPath, or you can use OpenXML and maintian a handle to the parsed document. Unless you do the second method, you’re forcing SQL to parse the XML over and over again. But that’s only realistic from within a single batch. The same goes for FOR XML AUTO in SELECT queries – this uses the DOM to create the XML result.
Of course .NET has a different and more granular caching mechanism in general. A developer can parse XML once, store some things for later use, and get to everything in its time. It can make use of the object data before generating XML from it. Any of these things, done correctly, can improve scalability by reducing SQL Server overhead. Especially when it comes to XML reparsing. Let the database server do what it’s good at, but take non RDBMS work somewhere else.

Custom Data Types

I don’t have much to say on this one myself, but Microsoft has an in depth code sample showing how to work with spacial data using SQL CLR sprocs.

In Conclusion

Use SQL CLR procs, be they in C# or VB, very sparingly. But don’t hesitate to use them when they’re the best tool in the kit. Microsoft put more arrows in our quiver, but we should still use them carefully, and have the right one on hand when we need to slay those dragons.


March 13, 2008

SQL Danger

Filed under: Evolution,Programming,Software — alexanderthegreatest @ 3:53 pm
Tags: , , , , , ,

SQL Server supports a cooperative, non-preemptive threading model in which the threads voluntarily yield execution periodically, or when they are waiting on locks or I/O. The CLR supports a preemptive threading model. If user code running inside SQL Server can directly call the operating system threading primitives, then it does not integrate well into the SQL Server task scheduler and can degrade the scalability of the system. The CLR does not distinguish between virtual and physical memory, but SQL Server directly manages physical memory and is required to use physical memory within a configurable limit.


If you’re scratching your head, thank your lucky stars you don’t need to understand this gibberish. I’ve been having to focus on SQL Server and .NET Integration, also known as SQL CLR integration. A lot of people have made fairly bad choices in the very, very recent past. Just because you can write your stored procedures now in Visual Basic doesn’t mean you should. Obviously it cuts both ways, and that doesn’t mean you shouldn’t – the trouble seems to come when people can’t decide whether they should or not.

Having to help guide that decision, I found the 1st paragraph above, which has some frightening guidance on the matter. It seems to suggest never to use .NET at first glance, but that isn’t really the case. What it really says is long running code that might interfere with SQL’s thread scheduling can be bad. Code that spawns new threads really shouldn’t be hosted in SQL. PInvoke calls can hurt.

But XML processing is an example of something that does none of those things. And while SQL has good XML support, for some operations, .NET is better. Also the set based nature of SQL compared to the procedural and object oriented nature of C# mean you have better control over caching in .NET, so by porting this type of sproc, you can parse an XML document in memory once, instead of once for every time you need to access it.

After all, it’s not SyBase in 1997!

March 3, 2008

SQL Server Deployment Checklist

Filed under: Science,Software — alexanderthegreatest @ 4:37 pm
Tags: , , , ,

John Hicks (of MSDN Blogs) has posted a very interesting article on SQL Server deployments that raised my eyebrows. It begins with the text “Every once in a while, a process gets too complex for humans to manage consistently.

The article goes into great detail about the steps that should be taken to performance optimize a new node in a SQL Server cluster, or to prepare a new server that’s being migrated to. This is extremely high scale stuff here, probably boring to mom n pop shops that need to store and instantly access less than perhaps 20 million records. (PHP users will find this article endlessly tiresome, because PHP users turn their nose up at any system meant to serve 25 or more concurrent users. Such is the demand of “enterprise level” evil corporate programming, a need of monsterish slave masters to rob the noble developers of the world of their identities and very souls. I’m not sure where this prejudice comes from, but it’s unescapable at any number of web forums.) The paper describes physical tuning only, completely ignoring the logical domain.

Some highlights

  1. Set your disc data allocation units (pages) to 64 KB to match the size of an extent.
  2. Set network packet size to 8 KB to take better advantage of large and few TCP packets. This is known as jumbo framing.
  3. Create a TEMPDB filegroup for each CPU core on the server.
  4. Set MAXDOP to correspond to your disc subsystem hardware. (I don’t understand why – maybe someone can explain this to me in the comment stream. Shouldn’t these settings be independant from one another? Disc bound transactions should allow the threads to block and be preempted by CPU bound transactions, no?)
  5. Use TraceFlags to control lock escalation – DANGER, WILL ROBERTSON!

Create a free website or blog at