Up Prev Next
From: c22309 at j1xsfs90 dot is dot nwa dot com (Mark Conty)
Subject: RE: Executing stored procedures with parameters
Date: Mar 20 1996 10:41PM
Doug Fairclough writes:
> given how short the statements are, how long can this take ? i cant
> believe its going to be long enough to impact the overall execution time
> of a query that is of any complexity.
> i dont see how using RPC will impact network traffic. the query is still
> going to bring back the same amount of data.
For small SQL queries, I suspect you are correct. As the SQL command stream
grows, though, it will take an increasing amount of CPU time to check the syntax
and table references.
In addition, the network traffic from the client to the server is [typically]
greater when sending a stream of SQL statements than when using the RPC
interface. (You're right about the data coming back -- it should all be the
same, unless you want to tweak it to do any necessary conversion of the
returning data on the client instead of on the server or vice-versa.
I've been sitting in on an informal CT-Lib class being given on-site this week,
and it just so happens that yesterday's session dealt with stored procedures.
The following statement appears in the CT-Lib manual (from Sybase), on a page
entitled "Benefits of RPCs":
* Scalability: a SQL server accepting mostly RPC requests will deliver
services to more clients than a server accepting language requests
[i.e., SQL command streams] that do the same amount of work.
So they're saying that, over the long haul, you'll get more throughput from a
given server if the requesting clients use RPC instead of regular SQL stmts (as
long they're doing the same thing).
Now, in the case of the original question, we're dealing with a finite task --
calling a stored procedure -- so it's not likely that the generated command will
change much. But there _is_ a savings, however miniscule, when using RPC. And
if we magnify that scenario by 100 or 1000, we'd be looking at some decent CPU
savings, wouldn't you agree?
For quick-n-dirty stuff, it's probably fine, but for real production code, I've
been changing things over to use RPC instead. (I came in kinda late in the
game.) This forces you to be more disciplined about ensuring that you've got
the names of the stored procedure's parameters correct in the client
application. By the same token, though, a chance in the name of a procedure
parameter will require a corresponding change in any client calling that
procedure via RPC, while a client that specifies the parameters positionally
might not have to be changed.
(It just goes to show you it's always something. :-)