How to call multiple stored procedures in one db trip?

  • Thread starter Thread starter Author #1
  • Start date Start date
A

Author #1

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.
 
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.
Click to expand...

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.
 
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!
|
*********************************************
 
Back
Top