> 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?
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.