Calling stored procedure with large number of parameters - performance concerns...

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Hi...

I have a stored procedure that takes in a large number of parameters (around
30) and returns (as output parameters) another 10 or so.

At the moment, each parameter is declared, defined and added in my C# code
as follows:

SqlParameter prmCustAcctID = cmd.CreateParameter();
prmCustAcctID.ParameterName = "@CustAcctID";
prmCustAcctID.SqlDbType = SqlDbType.NVarChar;
prmCustAcctID.Direction = ParameterDirection.Input;
prmCustAcctID.Size = 30;
prmCustAcctID.Value = strCustAcctID;
cmd.Parameters.Add(prmCustAcctID);

Multiply this by around 40, and you get a very long block of code!!!

Is there a better/faster/more improved way of doing something like this for
large numbers of parameters?

Thanks!
Alex
 
Take a look at the Data Access block, included with the Enterprise Library.
http://www.gotdotnet.com/codegallery/codegallery.aspx?id=295a464a-6072-4e25-94e2-91be63527327
Depending on the version (1.x or 2.x), it will vary, but the concept remains
the same

It reduces database calls to this pseudo code
Database db = Database.CreateFactory("");
DBCommand cmd = db.GetStoredProcCommandWrapper("StoredProcName");
cmd.AddInParameter("InboundParam1", DbType.String, ParamValue);
cmd.AddOutParameter("OutParam1", DbType.Int32, 4);
cmd.AddOutParameter("OutParam2", DbType.Int32, 4);
db.ExecuteNonQuery(cmd);
cmd.GetParameterValue("OutParam1"); //get value of outbound param
cmd.GetParameterValue("OutParam2"); //get value of outbound param

2 things I really like about it is
1. No need for @; the prefix is defined by the DB type in the setup
2. In theory, you can change the connection string to another DB type with
no changes to the data tier.

HTH,

Morgan
 
And to answer you question about performance... the # of parameters should
be a non-issue. The params are passed all at once when the command action is
called. I would be more concerned about what the proc's are doing once they
get the params.
 
Morgan,

Thanks for your quick response. The stored procedure itself is quite fast -
it just performs some validation on the parameters then uses them to insert
rows into a number of tables - this is much faster than calling multiple
inserts directly from C#, as some parameters are used more than once in the
various inserts (already did some performance testing on that which is why I
ended up with this scenario).

My main concern was finding the fastest way of building and passing the
parameters from C# to SQL... I'll have a look at the Data Access Block too,
thanks.

Regards,
Alexis
 
First of all, because ParameterDirection.Input is the default, for all your
inpur parameters you can reduce the number of lines of code by using:

cmd.Parameters.Add("@CustAcctID", SqlDbType.NVarChar, 30).Value =
strCustAcctID;

Before you get hung up on a 'very long block of code!!!', have you
determined how long it takes to execute the resulting block of code. I think
you might be surprised at how little time it actually takes and that your
'problem' is a non-event.
 
If you don't want to handle parameter declaration code why not try
Microsoft Data Access Block.
 
You may want to look at the SqlCommandBuilder class. This will allow
you to populate the parameters from the sql stored procedure. It will
cause actual execution time to be slower (cause it has to round-trip the
server to get the information) (although probably not noticably) but
will reduce the amount of code you have to write.

John
 
Back
Top