Parameterized Queries and Performance

Parameterized queries can help you with injection attacks, but they can also help with performance as most databases will attempt to improve their performance by caching query plans.


If you use string concatentation when forming queries as shown below, the database will have to parse the command, compile it, and then come up with an execution plan for each value of customerID.


 

SqlCommand command = connection.CreateCommand();
command.CommandText
= SELECT * FROM Customers WHERE CustomerID = ‘ + customerID + ;

// Execute the command…


 


You can help the database by using parameterized queries as below.  The database will recognize this command as the same for all values of customerID, and it can avoid the parsing, compiling, etc. by reusing the cached query plan.


 

SqlCommand command = connection.CreateCommand();
command.CommandText
= SELECT * FROM Customers WHERE CustomerID = @CustomerID;

command.Parameters.Add(
new SqlParameter(@CustomerID, SqlDbType.NChar, 5)).Value = customerID;

// Execute the command…


 


 

This entry was posted in ADO.NET. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

5 Responses to Parameterized Queries and Performance

  1. Sam says:

    Perhaps one of youse guys could clarify this for me, but I’ve been working under the assumption that a parameterized query is really just a call to sp_execute_sql. True? False?

    Otherwise it would seem there’s gotta be quite a bit of busy-work/duplication in ADO.NET.

    Of course I’ve also read once-upon-a-time that a Parameter passed without an explicit type, or a n/varchar without an explicit length, is passed as nvarchar(4000). Inline queries wouldn’t have this issue though, but I don’t have books-online handy right now and I can’t remember the exact syntax for sp_execute_sql so I dunno if these two (parameter type/length and sp_execute_sql) jive.

  2. ewise says:

    As long as we’re talking about query performance… is it not also more performant to list your return values instead of using ‘*’. * pulls in some overhead doing a scan.

  3. Jeremy D. Miller says:

    There’s still the small performance gain from the reduction in string concatenation in the middle tier code, not to mention the improved readability of the data access code. Either way, there’s more than enough reasons to use parameterized sql instead of inline sql construction.

  4. dhayden says:

    That’s good information, Paul. Everything I have been reading recently still suggests performance improvements using parameterized queries.

  5. Paul Wilson says:

    This used to be a valid difference, but it hasn’t been in at least MS Sql or Oracle (and probably other DBMSs) for quite some time. Instead, these databases will actually parameterize dynamic sql and then automatically reuse existing and already compiled query plans just as if it had been parameterized to begin with. To take advantage of this you simply have to make sure your dynamic sql “looks” the same each time, except for the actual values of course — and if you’re creating your sql using a common method (or an O/R Mapper) then that is no big deal. Now my goal is not to argue against using parameterized sql since it does help to protect against sql injection, but even then its not very hard to protect yourself even without using parameters — so I’m simply trying to clarify your point. Note that my O/R Mapper provides both options (parameterized queries using OPath and concatenated dynamic sql queries otherwise), so I’m not trying to defend either option.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>