How much faster are stored procedures really? Are ad-hoc queries a better engineered method?
Posted on 2004-10-13
My question relates to stored procedures on SQL Server 2000
Hi, I am studying for an MCAD qualification and have been experimenting with SQL server access from ASP.NET. The books I am studying and stuff I have read elsewhere all say that I should use stored procedures rather than ad-hoc queries in almost all cases as the performance is better.
Does anyone know of any benchmark data that measures how significant this performance increase is? Reading the books, they suggest that the difference is because the T-SQL statements can be precompiled. Compiling a few lines of SQL doesn't sound to me to be a hugel processing task anyway. I am wondering if the performance increase from stored procedures is something likely to make a noticeable difference to an app, or is just one of thse performance tweaks where the difference is pretty immeasurable.
Just looking at it as a newby, it seems to me that there is a disadvantage to stored procedures from an engineering complexity point of view as it means splitting the business logic between ASP.NET components and the database. I guess it also means rewriting big chunks of an application if we ever changed brands of database.
If the performance increase from using stored procedures is only marginal, shouldn't we use adhoc queries instead.
Anybody agree / disagree , thoughts?