How to call multiple stored procedures in one db trip?

Discussion in 'ASP .Net' started by Author #1, Apr 24, 2009.

  1. Author #1

    Author #1 Guest

    I quite often need to call a bunch of stored procedures to get data
    for a few server controls in a single aspx page.

    I think it gives better performance if I call these stored procedures
    in one db trip instead of one db trip for each of these stored
    procedure.

    Note: I don't want to write a wrapper stored procedure to wrap up
    these stored procedures, and then call this wrapper stored procedure
    in the front end.

    Can I use TransactionScope? If I wrap up my sproc calls in a
    TransactionScope, does it make sure that all stored procedures are
    called in one db trip? See below:

    using (System.Transactions.TransactionScope xScope = new
    System.Transactions.TransactionScope())
    {
    int customerId = Convert.ToInt32(Request["cid"]);
    DataSet ds1 = GetCustomerDetails(customerId);
    DataSet ds2 = GetOrderHistory(customerId);
    int productId = Convert.ToInt32(Request["pid"]);
    DataSet ds3 = GetProductInfo(productId);
    }

    1) Does this TransactionScope block ensure sproc calls in one db
    trip?

    2) All I do in this block of code is SELECT, no INSERT, UPDATE,
    DELETE, does it make sense to wrap them up in a TransactionScope?

    Thank you for your hint.
     
    Author #1, Apr 24, 2009
    #1
    1. Advertising

  2. On Apr 24, 10:18 pm, "Author #1" <> wrote:
    > I quite often need to call a bunch of stored procedures to get data
    > for a few server controls in a single aspx page.
    >
    > I think it gives better performance if I call these stored procedures
    > in one db trip instead of one db trip for each of these stored
    > procedure.
    >
    > Note: I don't want to write a wrapper stored procedure to wrap up
    > these stored procedures, and then call this wrapper stored procedure
    > in the front end.
    >
    > Can I use TransactionScope?  If I wrap up my sproc calls in a
    > TransactionScope, does it make sure that all stored procedures are
    > called in one db trip?  See below:
    >
    > using (System.Transactions.TransactionScope xScope = new
    > System.Transactions.TransactionScope())
    > {
    >        int customerId = Convert.ToInt32(Request["cid"]);
    >        DataSet ds1 = GetCustomerDetails(customerId);
    >        DataSet ds2 = GetOrderHistory(customerId);
    >        int productId = Convert.ToInt32(Request["pid"]);
    >        DataSet ds3 = GetProductInfo(productId);
    >
    > }
    >
    > 1) Does this TransactionScope block ensure sproc calls in one db
    > trip?
    >
    > 2) All I do in this block of code is SELECT, no INSERT, UPDATE,
    > DELETE, does it make sense to wrap them up in a TransactionScope?
    >
    > Thank you for your hint.


    No. TransactionScope assumes that you will use a single connection for
    all of the database calls that occur within the transaction. This
    means, one connection, multiply round-trips. To avoid round-trips when
    returning multiple resultsets, use a single database request. The
    SqlDataReader object has a method called NextResult. Instead of doing
    single SELECT, INSERT... make one dynamic sql SELECT...;INSERT....;
    and use NextResult. Or use one stored procedure.
     
    Alexey Smirnov, Apr 24, 2009
    #2
    1. Advertising

  3. Transaction scope merely means everything passes or everything fails. It
    does not group commands.

    There are a couple of things you can do.

    1. Create a "master" stored procedure that calls the others and send all of
    the info necessary to call all to that stored procedure. you can handle as
    many "recordsets" as necessary in the return.

    2. Call the sprocs via linked exec command strings. This is potentially a
    sql inject issue, if you do it wrong, so be careful.

    Anything else you do will still multi- trip.

    --
    Gregory A. Beamer
    MCP: +I, SE, SD, DBA

    *********************************************
    | Think outside the box!
    |
    *********************************************
    "Author #1" <> wrote in message
    news:...
    >I quite often need to call a bunch of stored procedures to get data
    > for a few server controls in a single aspx page.
    >
    > I think it gives better performance if I call these stored procedures
    > in one db trip instead of one db trip for each of these stored
    > procedure.
    >
    > Note: I don't want to write a wrapper stored procedure to wrap up
    > these stored procedures, and then call this wrapper stored procedure
    > in the front end.
    >
    > Can I use TransactionScope? If I wrap up my sproc calls in a
    > TransactionScope, does it make sure that all stored procedures are
    > called in one db trip? See below:
    >
    > using (System.Transactions.TransactionScope xScope = new
    > System.Transactions.TransactionScope())
    > {
    > int customerId = Convert.ToInt32(Request["cid"]);
    > DataSet ds1 = GetCustomerDetails(customerId);
    > DataSet ds2 = GetOrderHistory(customerId);
    > int productId = Convert.ToInt32(Request["pid"]);
    > DataSet ds3 = GetProductInfo(productId);
    > }
    >
    > 1) Does this TransactionScope block ensure sproc calls in one db
    > trip?
    >
    > 2) All I do in this block of code is SELECT, no INSERT, UPDATE,
    > DELETE, does it make sense to wrap them up in a TransactionScope?
    >
    > Thank you for your hint.
     
    Cowboy \(Gregory A. Beamer\), Apr 25, 2009
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Pedro Vera
    Replies:
    5
    Views:
    442
    Juan T. Llibre
    Mar 30, 2007
  2. Elmo Watson
    Replies:
    4
    Views:
    403
    Elmo Watson
    Jul 24, 2007
  3. Paul Hale
    Replies:
    3
    Views:
    374
    Paul Hale
    Jun 13, 2008
  4. pbd22
    Replies:
    0
    Views:
    89
    pbd22
    May 21, 2007
  5. pbd22
    Replies:
    4
    Views:
    86
    pbd22
    May 22, 2007
Loading...

Share This Page