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

[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

[Morgan]

Take a look at the Data Access block, included with the Enterprise Library.
http://www.gotdotnet.com/codegallery...2-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
"Alex" <no****@hotmail.com> wrote in message
news:43*********************@news.zen.co.uk...

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

[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.
"Alex" <no****@hotmail.com> wrote in message
news:43*********************@news.zen.co.uk...

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

[Alex]

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

[Stephany Young]

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.
"Alex" <no****@hotmail.com> wrote in message
news:43*********************@news.zen.co.uk...

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

[kids_pro]

If you don't want to handle parameter declaration code why not try
Microsoft Data Access Block.

Stephany Young wrote:

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.
"Alex" <no****@hotmail.com> wrote in message
news:43*********************@news.zen.co.uk...

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

[John Murray]

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


Alex wrote:

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