Pete's comments on .NET, politics, humor, and everything else wrong in the world.

11/12/2004

ADO.NET and SQL Server Optimization

Recently a compatriot and I got into this diatribe over the use of CommandType.Text and "EXEC spName param, param, ..." to make SQL Server stored procedure calls.I had chimed in on his answer to a poster who was having difficulty with a sproc call, and he suggested "you don't need to use the Command Object" and proceeded to show an illustration like the above.

Even after I had provided some pretty good background material on why this is not a good idea, he continued to hammer on little pieces of the subject, in effect missing the big picture. There have been many times I've been corrected. As I've grown older (and hopefully wiser) I've learned to put my ego in my back pocket and thank the person for the education. And you? I guess what I am trying to say is this; you have two MVPs who didn't know about this. The only reason I found out about it is because I was lucky enough to attend Tech-Ed 2004 and sit in Gert Drapers' presentation. So if two C# MVP's weren't aware of it, how many other people do you suppose are writing whole applications using Language Event processing instead of RPC Event processing as they should?

The bottom line is, you should never use CommandType.Text and concatenated "EXEC SPNAME PARM PARM" convention. In fact, even if you are not using stored procedures at all - you should parameterize your text queries - because the SQL Server engine is smart enough to "Template-ize" them - and cache the compiled execution path for re-use. What I posted was Gert Drapers' PowerPoint Presentation to illustrate. Gert Drapers, for those who don't know, is a Software Architect in the SQL Server Development group at Microsoft. He is involved in the architecture design and performance tuning of the most challenging applications and large-scale database deployments around the world; worked on both SQL Server 7.0 and 2000; first as Software Design Engineer in the SQL Server Storage Engine and later as the Product Unit Manager of the SQL Server Management Tools team and as Architect of SQL Server DTS. So, he certainly has the credentials, IMHO.

Here are some of the notes (slightly edited) that I took of Mr. Drapers' presentation at Tech-Ed 2004:

First, we need to make a decision which command type to use. It is NOT a convenience issue. From a server persepective, there is a significant difference.:

IN SQL Server you have language events, and RPC events, This is a split code path. In other words, If you want to run a stored procedure, you want to be sure that you both start and end inside the RPC Event source code path. You do this by explicitly setting commandType.StoredProcedure in the ADO.NET SQLClient class. You don't want to execute it as a Language Event, because that is not going to trigger the most optimal code path inside SQL Server.

If SQL Profiler shows you a SQL Statement starting event and then you see anything else than INSERT, UPDATE, DELETE, or if you see "EXECUTE " you are probably dealing with a stored procedure. That means that your program is actually choosing the wrong code path.

Parameter re-use: If you send in a "EXEC" - type select concatenated text vs Parameterized Command, the execution plan gets recompiled. But , when you use named parameters in ADO.NET, you are actually creating a Template. This Template is how SQL Server achieves plan re-use and does not have to recompile the execution plan for the query.

You can actually see this plan re-use and optimization by querying select * from master.dbo.syscacheobjects . In SyscacheObjects, there is a Sql column and a usecounts column. Sql holds the sql fragment, and usecounts shows how many times the SQL Engine has been able to re-use the code path without having to recompile, resulting in more optimized code and lower CPU utilization.

So, for example if you run 10,000 stored proc invocations with different (or even the same) parameter values using CommandType.Text and "EXEC SPNAME param1 param2" etc., these will be usually recompiled every single time (depending on whether autoparameterization is used by SQL Server). This burns up CPU cycles. Event though the performance difference may not be readily apparent, you can bet that this is an inefficient way to do it. When you use CommandType.StoreProcedure with SqlParameters, it gets compiled ONCE - and re-used by the Sql Server engine every subsequent time. This ain't rocket science!

And of course, with Stored Procedures and selects / inserts, using fully parameterized execution results in significant performance increases.

Client - side Parameter Caching

You can also use a Client-side caching mechanism for SqlParameters. The MS Data Application Blocks "SqlHelper" class already has a very nice one built in. By caching the parameter set for each stored procedure, I've seen performance increases ; it seems that the creation of ADO.NET SqlParameter objects is expensive, and if we can cache and re-use them , there are substantial economies of throughput to be obtained.

If a multi-insert operation is bracketed within a transaction, the deferred writing to the transaction log creates much enhanced throughput. Drapers illustrated this in real time in his Tech-Ed presentation and I was, well - trust me, its hugely faster! Also client side Parameter Caching (a.k.a. SqlHelper) can increase performance.

What others have to say about the subject:

Kirk Allen Evans (Excerpts):

Databases create a query plan or "internal road map" to determine the quickest and most efficient route to deliver the requested data. The database's cache manager compiles the query plan and stores it in its cache when you execute a query for the first time. If you execute the same query text again, the database retrieves the query plan from the cache and uses it to retrieve the data. But if the query differs by even one character, such as the data in a WHERE clause, the database must compile the query again, generate a new query plan, and store the query and plan in its cache.

If an application searches for a customer by name, many different cache listings will appear in syscacheobjects for essentially the same query.

A more efficient approach than forcing the cache manager to recompile queries each time is to reuse the same query multiple times using parameters. This saves CPU cycles because it avoids recreating cache entries. SQL Server provides an excellent mechanism for doing this through its implementation of stored procedures. Create a stored procedure that accepts a variable and use the variable in the SQL WHERE clause.

The real performance difference occurs with complex SQL statements that are inlined, where the WHERE clause varies only by its values. This causes discrete cache entries into the cache plan, meaning the query is parsed for execution plan each time the query is run.

The Prepare statement caches the statement and indicates to the provider that the same statement will be run many times. The provider may or may not perform optimimizations to support this, which SQL Server does. By executing this each time within the loop, you cause extra processing. Move this out of the loop, because you only have to prepare it once.

Second, you will get better performance by using a parameterized SQL statement by replacing the literal values with question marks and using parameters in place of the literal values. The parameters can then be reused as well, where you simply set the values during each iteration.

Third, you can enhance performance a little more if your provider supports the .Prepared property for the Command object. The .Prepared property pre-compiles the SQL statement and caches it, avoiding parses for invaalid SQL statements.

One more comment: you are not going to see a noticeable time difference between using parameterized SQL queries that simply call a stored proc over using a CommandType of StoredProc. The real difference occurs back on the server, where the procedure is parsed and cached.

Jesse Ezell:

In any case, ALWAYS use parameterized queries. Anything else is a rediculously [sic] lame decision.

Barry Gervin:

In terms of SQLServer and Oracle, there are no implicit performance improvements between a stored proc and a parameterized inline sql statement. Both of them have their execution plans compiled at first execution - and then reused.

In SqlServer - with non-parameterized sql - the syntax will be parsed and auto-parameterized - so 2nd & nth executions on similar sql (different hard coded values) should exhibit performance on scale with parameterized sql (or stored procs).

Sometimes the auto-parming doing by Sql isn't as efficient as a developer could manually parm it. Think of a case when you have a constant in the query that neverchanges: where inactive="n" and state="CA". SqlServer will auto-parm the "n" and the "CA" but you know that "n" will never change. That would lead to not as efficient parameterization.

The Bottom Line!The lesson from all this is that just because something "works" and you have gotten used to doing it doesn't mean you automatically have the right to become a self-proclaimed "guru" and recommend it to everybody else in the face of facts, figures and arithmetic that explain why what you are doing is NOT "best practices code". And if you are obstinate and refuse to accept the facts, digging in to protect your already indefensible position, shame on you! Don't use CommandType.Text to execute stored procedures. If you are lazy and you don't like to type, use the SqlHelper class with (connectionstring,spname,object[] parmvalues) and your SqlParameters will be automatically CACHED for you! Finally, ALWAYS parameterize your text sql queries with parameter placeholders and REAL SqlParameters!

Obstinacy is the result of the will forcing itself into the place of the intellect.

About Me

I am a former Microsoft C# .NET MVP (Most Valuable Professional) for 10 years , author, and UnEducator based in the Orlando Florida area. I write mostly about .NET, formerly for eggheadcafe and currently at my programming blog at peterbromberg.net. I'm an ex-jazz musician and stockbroker who dreams of being a philanthropist and paying less in taxes. I believe in the US Constitution and free-market "Austrian" economic theories. I'm also a "climate realist" and believe in good science, which is never "settled".

As a computer programmer with over 25 years of hands-on experience in designing and developing large-scale enterprise applications, I am an expert at solving business problems.

Some subjects that I am interested in are: Art, Jazz, Classical, Photography, Politics, Science, Psychology, Economics. One of my hobbies is creating digital photocollage with my original photographs and Autodesk Maya software.

NOTE: I occasionally make controversial posts or comments in order to stir debate.My comments are my own, and do not represent in any way those of any employer or client, past or present. I encourage civil debate, whether you agree with me or not, but if you engage in ad-hominem personal insults on one of my posts you will be blocked in a New York second.

Excerpts provided by me are pursuant to the Fair Use Doctrine for educational and discussion purposes per Title 17 U.S.C. Section 107, Copyright Law.

Copyright Disclaimer Under Section 107 of the Copyright Act 1976, allowance is made for "fair use" for purposes such as criticism, comment, news reporting, teaching, scholarship, and research. Fair use is a use permitted by copyright statute that might otherwise be infringing. Non-profit, educational or personal use tips the balance in favor of fair use.