Parameterizing SQL Calls

Damian Barrow is having some problems with parameterizing SQL calls and performance.  He is effectively parameterizing the following SQL statement:

exec getCustomers 'ALFKI'

Notice that this is a call to a stored procedure for SQL Server, providing a value for the proc.  This is contrasted to Damian's original post I commented on, which used an inlined SELECT statement.  For the original example, you might see some performance improvement based on the complexity of the SQL (explained below).  The real goal is to minimize SQL injection attacks for simple statements.  For the stored proc example, you are unlikely to see much improvement, but still prevent against SQL injection attacks.

To execute a stored procedure using the "exec procname" convention, you need to specify the SqlCommand.CommandType as "Text".  This means that you are issuing a text statement that should be parsed.  An alternative is to use the CommandType of StoredProcedure, which indicates the provider should resolve the named proc with the underlying catalog.  Likely, you will not see a noticeable difference between the two conventions because the "magic" happens at the provider and the data store level, not within ADO.NET itself.

The real performance difference occurs back on the server, where the procedure is parsed and cached.  Here is an OLD article that explains the concept by looking at ADO 2.1 with SQL Server 7 and Oracle 8.  But, the same concepts still apply. 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.

The real benefit for parameterized queries comes not for stored procedures, but for inlined SQL calls, such as "SELECT CompanyName from Customers WHERE CustomerID='ALFKI'".  Replace the ALFKI with a question mark, and you parameterize the inlined SQL call.  If you notice in my article, Minimize Cache Thrash, you will see that I point out that simple SELECT statements are still cached as a single entry, and their WHERE clause values replaced by a variable.  So, even simple inlined SQL queries will not benefit greatly from parameterizing queries on subsequent evaluations. 

Again, you are unlikely to see a very noticeable performance increase on the client.  You might, however, detect a very small performance increase on the database server in terms of a few less cycles performed.