Q: Why should you use Stored procedures not ad-hoc queries?
A (happily, he's sure that he knows how to answer this question): They perform better because they are precompiled at creation time, also ...(Some, other reasons that I don't really care about now)...
Is that true?
In fact, this is a common misconception that many users of SQL Server 7.0, 2000, and 2005 suffer, may be because this was the case in SQL Server 6.5. Also because inaccurate and not up to date articles, books ,and learning materials.
I quote this from msdn:
In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure.
This is not the case in for SQL Server 7.0 and higher, again from msdn:
SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement.
Knowing this, it's common that arguments take place from time to another comparing using Stored procedures vs. ad hoc queries.
Personally, I prefer to use stored procedures because I find this more modular.
My advice is, whether you use Stored procedures or ad hoc queries, always use best coding practices when performing database operations to avoid SQL injection attacks. Be them parameterized queries, prepared statements, or whatever depending on the programming language and framework you use.
6 comments:
hmmm I am realy so surprised with this information becuase I face a problem with sql srver 2000 to run spesific report wich aggregate more than Hundred thousand of record and the result did not come after agressive proccesing and to the solve the problem stop service :d or rebuild indxes or use recompile option with stored procedure.
As stated in BOL 2005:
SQL Server automatically recompiles stored procedures and triggers when it is advantageous to do this.
Maybe in your case, great chages occur to data between stored procedures runs. In this case it may be beneficial to create the stored procedure using (WITH RECOMPILE).
This will cause SQL Server not to cache the execution plan and recompile it each time the stored procedure is run.
SQL Server 2005 has a feature that you can specify this clause at the statement level in a stored procedure as a query hint.
Remember to update statistics and make sure indexes are not heavily fragmented.
Hi Hesham,
Thanks a lot for your comment on (WITH RECOMPILE) option.
I have a situation where I need to change the table name used in my query dynamically, and hence I construct queries dynamically in my stored proc and execute them.
SQL Server caches execution plans for each one of those dynamic statements and this leads to very high memory utilization on the SQL sever box.
I hope to use statement level recompile option of SQL Server 2005 to stop SQL Server from caching those execution plans.
Thanks a lot for your tip.
Hi Neel
It's great that you found the tip useful.
For whoever is curious how to specify this query hint, here is an example:
select * from dbo.tablename
where id= 11
option (recompile)
You could use CLR stored procedures, which are .NET pre-compiled:
http://msdn.microsoft.com/en-us/library/ms131052.aspx
Post a Comment