SQL Server Performance

Query Performance: Batch or Procs

Discussion in 'SQL Server 2008 General DBA Questions' started by jmac, Jan 15, 2010.

  1. jmac New Member

    Hello Experts!
    In your experience, as a general rule which has better performance:
    1 x Batch Query
    exec "INSERT INTO tbl (col) VALUES (1); INSERT INTO tbl (col) VALUES (2);INSERT INTO tbl (col) VALUES (3);"
    Or
    n x Stored Procs
    exec s_AddVal 1
    exec s_AddVal 2
    exec s_AddVal 3
    Thanks for your expertise!
  2. preethi Member

    Hi John,
    I believe you are trying to get the performance difference between ad-hock queries and stored procedures in general and not against the particular statements you have mentioned. These are my thoughts:
    In SQL Server 2008, the performance differences are narrowed down between ad-hock queries and stored procedures but there are couple of things which still make stored procedures perform better.
    1. SQL engine will take the parameters correctly and create the plan. There could be a possibility where ad-hock query may not get the parameters correctly. If that happens, each query will go as separate statement and will get compiled for each statement.
    2. Generally ad-hock queries take more network bandwidth then stored procedures.
    I'll opt for stored procedures for a couple of other reasons as well.
    1. Easier to test
    2. Any change could be made at the database level without going trough code. That means DBAs can do the work without waiting on Development team. It also removes the headache of compiling the code.
    3. Identifying the code is easier when tracing (profiler)
    I am sharing what I know. There could be many who know better.
  3. jmac New Member

    Thanks for your reply! That makes a lot of sense, and that's probably why we use procs so much.
    However, when I run SQL profiler on the large enterprise web-apps I work on, I've always been astonished at the plethora of procs we developers typically call to fill a web page with data (over web services). And then again to process the data upon a form submit.

    I thought a more efficient approach might be to package up all queries for that web page in a single batch, and execute it once. Rather than calling 20-30 procs for each little piece of data here and there as the code runs down the list of everything the page needs.

    Same for the submit. Call a batch of all the sql needed to process that page, rather than just going down the code, save this: execute, wait for web services to come back, save that: execute, wait for web services, etc...

    I first started thinking about this when reading some suggestions on strategies for cloud computing since I won't be using Service Oriented Architecture on a new project I'm starting.

    Thought I'd see if anyone else out there has taken a similar approach or had thoughts from their experience.

    Thanks again!
  4. preethi Member

    You are welcome. By the way, Welcome to the forum.
    Calling many procedures in a batch is a good way to reduce network round trips. Please make sure that the stored procedures have SET NOCOUNT ON statement in the beginning of the procedure.

Share This Page