Do you know stored procedures and SQL Test queries have similar performance?!

Last night i was studying ASP.NET 2.0 Website Programming: Problem - Design - Solution by "Marco Bellinaso".

In my opinion this book is one of most valuable books in web development world with ASP.NET.

I found an interesting stuff that I did not know before this.

I put exact statements here:

"If you read a lot, you'll find many sources saying that stored procedures provide better performance than SQL text queries because they are "pre-compiled" (I don't mean compiled to a binary program, but parsed and used to generate an execution plan) and cached in-memory by SQL Server. However, many articles and books miss the fact that this happens for SQL text queries as well (one of the enhancements that was added to SQL Server some years ago), as long as you use parameterized SQL statements (which you should definitely do in all cases, to avoid SQL-injection security attacks). Therefore, the performance of stored procedures and SQL text queries is similar in most cases. However, the name of a stored procedure is shorter and invariant compared to a SQL statement, so it may be easier for the SQL Server engine to find the cached execution plan, but this shouldn't make a significant difference in most cases."

Note that in continue of the book "Marco Bellinaso" has mentioned some advantages of stored procedure against SQL Text queries.

What is your comment?

4 Comments

  • How can you have parameterized SQL statements without a stored procedure?

  • @rrobbins
    Sample :
    SqlConnection con = new SqlConnection("ConnectionString");
    SqlCommand cmd = new SqlCommand("Select * from tableName where ID=@ID", con);
    cmd.Parameters.AddWithValue("@ID", 10);

  • This is true for as long as the sql statement stays in the procedure cache. Once it's deemed to be too old then it may be removed and executing the statement again will involve creating a new plan, so it isn't really as good as it first sounds.
    Read the article in BOL for more info:
    msdn.microsoft.com/.../ms181055.aspx

  • Most of the time yes but when you perform bulk updates or export of data with many tables, SPs destroy inline selects especially ones generated from an ORM implementation.  

Comments have been disabled for this content.