Re: Store procedure vs Direct statement ???

This debate has gone on for years. The SQL Server team at MS has done what it can to make ad-hoc queries perform better and yes, they compile to the same execution plans as SPs. Those plans (the compiled code if you will) are cached in the same memory as SP plans. Yes, there are aspects of SPs that make them more efficient as I outline below. However, the real difference is not always in execution performance but in developer productivity. My research has found that the vast majority (over 90%) of the shops out there use SPs over ad-hoc queries. They use them because:
a.. SPs can be changed insitu without recoding, retesting and redeploying an application.
b.. The require discipline within the organization to ensure that changes made to the database and its schema are done in an orderly fashion.
c.. They are more secure as DBAs rarely permit direct access to base tables but do grant limited rights to SPs.
d.. They encourage developers to use Command object Parameter lists that deal with a litany of security and formatting issues.
e.. They find that OUTPUT parameters are far more efficient than rowsets (returned by a SELECT).
f.. They find that now that SPs can be written in VB.NET or C# some very tough jobs can be done on the server instead of on the client. This saves transporting data in bulk to the client for this processing--again improving performance.
I could go on (and I do in my books), but you get the idea.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"BobD" <BobD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:ADD2EEEB-6E58-44C4-8F56-9A002117B664@xxxxxxxxxxxxxxxx
Hi there,

If you get a chance, try some of the things I suggested. I recognize your
point of view, but before you refute the point, please do actually check out
what I said. Try opening up query analyzer and checking out the query plan or
using profiler to capture it that way. There's a ton of information on how to
use these tools to answer the questions you are debating, just take the time
to look into it and I think you'll find the answers you are looking for.

Here's some help on using profiler to that end:
http://www.sql-server-performance.com/rd_query_tuning.asp

Also, here is a quote from sql-server-performance.com: "One of the biggest
advantages of using stored procedures over not using stored procedures is the
ability to significantly reduce network traffic. And the more network traffic
that can be reduced, the better the overall performance of your SQL
Server-based applications.
"
http://www.sql-server-performance.com/stored_procedures.asp

Another quote from the same website: "As you know, one of the biggest
reasons to use stored procedures instead of ad-hoc queries is the performance
gained by using them."

If you work with large databases, you'll begin to see the advantages of
stored procedures. Just my humble opinion, take it for what it's worth.

Cheers mate,
Bob


"Sericinus hunter" wrote:

BobD wrote:
It's true that procedures get cached (true they can go through a recompile
for a large number of reasons as well, which is a performance hit).

The same is true for ad-hoc queries.

Probably the best help here is to take a look at the execution plan for
dynamic sql versus the execution plan for a stored procedure. A stored
procedure generates an actual execution plan, but dynamic sql doesn't (at
least last time I checked it didn't --- checking from within query analyzer)
or at least it is very basic.

I don't understand this. Can you elaborate? Any query needs an execution
plan in order to be executed. Why would this plan be different for ad-hoc
query?

Stored procedures are simply faster than ad-hoc queries.

Why? Both are just chunks of SQL code.

For example, how do you cache a query plan that is dynamic?

What is dynamic query plan? Query gets compiled, execution plan gets
cached, then executed. I see no problems with this.

Which index is it going to use? Would
it be better to look at the heap instead (in other words, run a table scan).
Remember, things like the order of the columns in a query, how the order by
clause is constructed and what criteria you are using all effect performance
of a query.

This is all true, but not relevant. If you type in a similar query which only
differs by, say, ORDER BY clause, it is a different query and it will cause a new
plan be generated and cached. But if you only change parameter values, the SQL
Server will reuse already existing plan.
The only thing I can imagine in favor of stored procedures is that when the
SQL Server is looking for a cached plan, it uses the procedure name, while
for ad-hoc query it needs to parse the query and calculate some sort of a key
to use for available plan lookup. This might be faster, but I don't know
how the engine works in this respect.

It's usually better to have a sql guy develop the sql code and
have programmers develop the ui, in this way the sql code can be tuned
appropriately. This is a big advantage for the SQL Server model.

I am not arguing that. In fact, I am for stored procedures with both hands.
However, this argument about ad-hoc queries not being cached became so popular,
that I just wanted to clear this up.

Relevant Pages

  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SP runs slower than same query in Query analyzer. Why??
    ... Andrew your reply was useful but I'm quite new to sql server and I'm ... query gives me the results in 3-4 seconds. ... > Keep in mind that the server has to compile a complete execution plan ... > when compiling a plan. ...
    (microsoft.public.sqlserver.clients)
  • Re: Execute SQL UD Function call timeout, 1-3 seconds in query ana
    ... We did build a little ASP.NET page yesterday that lets us put a given sql ... query into a text box and then return the execution plan (using Showplan_Text ... Query Analyzer. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Help with Stored Procedure
    ... I did mean stuff like system stored procedures (even ... build the query, compile it, and optimize it, then, then this is less ... very not easy using dynamic sql. ...
    (microsoft.public.sqlserver.programming)
  • Composite index and data distribution
    ... SQL> create table foobar ... COMPOSITE INDEX (BEST I CAN THINK OF FOR THE QUERY TO BE EXECUTED): ... Execution Plan ... 395 bytes received via SQL*Net from client ...
    (comp.databases.oracle.server)