Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

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.  :-)