Executing stored procs via send_rpc()

Executing stored procs via send_rpc()

Post by Steven K. Shapi » Tue, 09 Apr 1996 04:00:00



Howdy!

Our client C++ applications currently send requests to execute stored
procedures via the language handler. We are considering converting to use
send_rpc() instead.

The reasoning is based on the following:

When we send the statement to the language handler, it has to parse and
convert the string before executing the stored procedure. If we were to
use send_rpc() then we do the parsing and converting on the client side and
the server simply executes the stored procedure.

However, it seems to me that we have to perform the parsing and converting in
either case. Whether we do it on the client side or whether it is done
implicitly on the server side it still has to be done.

What I would like to know is if any of you have implemented something like
this and if so, which implementation provides better performance.

In other words, is it worth the time and effort to change our code or should
we just leave it the way it is?

Thanks.

Regards,
Steve.


// All Opinions May Or May Not Be My Own

// WWW HomePage > http://www.universe.digex.net/~skscci/

 
 
 

Executing stored procs via send_rpc()

Post by john » Thu, 11 Apr 1996 04:00:00



> Howdy!

> Our client C++ applications currently send requests to execute stored
> procedures via the language handler. We are considering converting to use
> send_rpc() instead.

> The reasoning is based on the following:

> When we send the statement to the language handler, it has to parse and
> convert the string before executing the stored procedure. If we were to
> use send_rpc() then we do the parsing and converting on the client side and
> the server simply executes the stored procedure.

> However, it seems to me that we have to perform the parsing and converting in
> either case. Whether we do it on the client side or whether it is done
> implicitly on the server side it still has to be done.

> What I would like to know is if any of you have implemented something like
> this and if so, which implementation provides better performance.

> In other words, is it worth the time and effort to change our code or should
> we just leave it the way it is?

> Thanks.

> Regards,
> Steve.

We did something like this a couple of years ago.  To sum it up we had a
series of procs that constituted a transaction.  As these procs were
dynamically called (a transaction could have multiple calls to the same
proc for a transaction) a transaction varied from 30-100 proc calls.  

We found that we were spending more time executing each proc at a time by
doing the send_rpc and then checking the status for each proc.

What we ended up doing was creating one HUGE transaction string and
sending that transaction string to SQL Server via db_cmd - or whatever
the equivalent of ct-libs execute this SQL was (I can't  remeber now).  
In this manner we eliminated making 30-100 trips across the network.  We
simply made 1 trip with a big ol' string.  The difference for us was
large - our typical transaction previously was about 5-6 seconds.  When
we implimented the change it dropped to .3 - .4 seconds, and was
extremely scalable.  150 procs calls would be executed in about .4
seconds versus 12 seconds the old way.

I would say try it and see if it makes a difference to you.  If you work
with 1-5 procs at a time - it might be a waste of time.

Of course, now you would have to parse through the raiserror meassges,
but we were already doing that to start out with.

Later,

JB

 
 
 

1. Executing Stored Procs via dba_object query

Does anyone know how to execute a stored procedure as a result from a
query to dba_object?  I would like to use a packaged procedure that
could take package_name, object_name inputs to a cursor querying the
dba_object or all_arguments table and then loop through and execute the
results.

Thanks in advance!

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

2. "Set Field" versus "Insert Calculated Result"

3. Executing procs via e-mail

4. Checkpoint not complete.

5. Stored procs in an Execute SQL Task

6. store text file in oracle

7. Grant Execute, stored procs, and variables.....

8. sqlplus /sqlnet question

9. How to execute stored procs from A97 client

10. Can't get stored procs to execute from VB5

11. Execute rights on Stored Procs

12. Executing stored procs

13. DB-Library Error, SqlSrv process dye when executing extended-stored-procs