Stored proc question

[John F]

Hi all,

This may be slightly off-topic but it's from within asp.net I'm experiencing
this so here goes:

What is the most efficient way for me to call a SQL stored procedure
multiple times?

I know this may seem easy to some but it's got me good. I thought of
"preparing" the command object (since this is what it was meant for in the
previous ADO object model) but when I run the code, it complains about the
associated connection object being in an "open, fetching state". Please
don't suggest I should close/open the connection object because that just
seems to be defeating the whole command.prepare objective.

Any help here greatly appreciated.

Regards
John.

[Scott Allen]

Hi John:

The best optimization you could make is to avoid multiple round trips and
batch up the multiple calls into a single command (if you know how many times
you'll call the proc before starting). Delimit the command text with semicolons
for the SQL Server provider.

You can get multiple resultsets back with one trip. With a SqlDataAdapter
you'll have a DataTable for each resultset, with a SqlDataReader you'll need
to call NextResult().

Prepare won't help when sprocs are involved.

HTH,

--
Scott
http://www.OdeToCode.com/blogs/scott/

Hi all,

This may be slightly off-topic but it's from within asp.net I'm
experiencing this so here goes:

What is the most efficient way for me to call a SQL stored procedure
multiple times?

I know this may seem easy to some but it's got me good. I thought of
"preparing" the command object (since this is what it was meant for in
the previous ADO object model) but when I run the code, it complains
about the associated connection object being in an "open, fetching
state". Please don't suggest I should close/open the connection object
because that just seems to be defeating the whole command.prepare
objective.

Any help here greatly appreciated.

Regards
John.

[David Browne]

"John F" <[e-mail]> wrote in message
news:%2******************@TK2MSFTNGP14.phx.gbl...

Hi all,

This may be slightly off-topic but it's from within asp.net I'm
experiencing this so here goes:

What is the most efficient way for me to call a SQL stored procedure
multiple times?

I know this may seem easy to some but it's got me good. I thought of
"preparing" the command object (since this is what it was meant for in the
previous ADO object model) but when I run the code, it complains about the
associated connection object being in an "open, fetching state". Please
don't suggest I should close/open the connection object because that just
seems to be defeating the whole command.prepare objective.

SqlServer stored procedures cannot be prepared. In SqlServer a prepared
command only improves the text commands, not stored procedure invocations.
In fact preparing a command simply turns it into a stored procedure call!

So preparing won't help. Batching a number of stored procedure calls into
one big TSQL batch can improve things, but it can also degrade them, since
the command batch must be transmitted, parsed and compiled. You'll need to
test. Plus it's a hassle.

In general there's no very good way to invoke a command repeatedly in
ADO.NET/SQL Server, or to push large amounts of data to SQL Server.

David

[John F]

Scott & Dave,

Thanks a lot to both of you guys. Not exactly what I was expecting (perhaps
I was just expecting some special keyword which did everything my 50 lines
of code is doing <joke>) but a definitive answer I can now deal with . . .

Regards
John.
"David Browne" <davidbaxterbrowne no potted me**@hotmail.com> wrote in
message news:%2********************@tk2msftngp13.phx.gbl.. .

"John F" <[e-mail]> wrote in message
news:%2******************@TK2MSFTNGP14.phx.gbl...

Hi all,

This may be slightly off-topic but it's from within asp.net I'm
experiencing this so here goes:

What is the most efficient way for me to call a SQL stored procedure
multiple times?

I know this may seem easy to some but it's got me good. I thought of
"preparing" the command object (since this is what it was meant for in
the previous ADO object model) but when I run the code, it complains
about the associated connection object being in an "open, fetching
state". Please don't suggest I should close/open the connection object
because that just seems to be defeating the whole command.prepare
objective.

SqlServer stored procedures cannot be prepared. In SqlServer a prepared
command only improves the text commands, not stored procedure invocations.
In fact preparing a command simply turns it into a stored procedure call!

So preparing won't help. Batching a number of stored procedure calls into
one big TSQL batch can improve things, but it can also degrade them, since
the command batch must be transmitted, parsed and compiled. You'll need
to test. Plus it's a hassle.

In general there's no very good way to invoke a command repeatedly in
ADO.NET/SQL Server, or to push large amounts of data to SQL Server.

David