The Effect of Data Access Code On SQL Server Performance

Bob Beaucheamin, who works at SQLSkills, has an excellent new article in MSDN Magazine that explains how different methods of writing data access code in ADO.NET can have a huge effect on performance and security.

Here is the executive summary of the article:

1. Concatenating an ad-hoc SQL statement and submitting it without specifying the CommandType is extremely bad from a performance and security standpoint.

2. Parameterizing an ad-hoc SQL statement and using Parameters.AddWithValue is not as evil, but still bad. Parameters.AddWithValue does implicit type conversion of the parameter.

3. Using a parameterized stored procedure is best. It is more secure, easier to maintain, and will not bloat the procedure cache with single-use plans.

4. It usually makes sense to have multiple stored procedures instead of large, generic stored procedures.

Technorati Tags:

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s