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: "Wechsler, Steven" <Steven dot Wechsler at mtvstaff dot com>
Subject: RE: Is there a performance advantage to using placeholders with Sybase?
Date: Jan 8 2008 3:31PM

Interesting. Anyone know if there is a DBI method of calling a stored
proc as an RPC?

Steve Wechsler |
Sybase/SQL Server/MySQL Database Administrator

MTV Networks

-----Original Message-----
From: []
On Behalf Of Stefan Karlsson
Sent: Tuesday, January 08, 2008 8:07 AM
Subject: Re: Is there a performance advantage to using placeholders with

I do not know the exact capabilities of DBI, nor how it maps to CT-Lib,
TDS and 
ASE capabilities, but want to describe it in general:

Prepared statements, a.k.a. Dynamic SQL(*), is implemented in various
API's, in 
TDS and in ASE. Since ASE 11.9 these only exist in memory - prior to
version a stored procedure was created in tempdb. Prepared statements is
non-proprietary approach to improving performance.

In JDBC and ODBC this is implemented as the PreparedStatement() class.
In CT-Lib 
as the ct_dynamic() calls.

With the prepare call, users prepare a statement. This causes ASE to
create a 
procedure that exists just in memory, a.k.a. light-weight stored

Using a TDS sniffer, e.g. Ribo but Ethereal also knows TDS, one can
clearly see 
this sequence of calls. This also reveals the optimizations going on:
	- TDS RPC call - procedure invocation call-by-name: look up in
hash table to 
find procedure by name
	- parameters are provided to ASE in native form so no data type
	- no parsing or optimization in ASE

A "regular" SQL call is a LANGUAGE call, i.e. text is transmitted over
network, ASE parses the content and moves with its work.

Execution of a stored procedure can be either a LANGUAGE call or an RPC
"EXEC sp_help 'mytable'" through isql is a LANG call. It is transmitted
as text 
and parsed by ASE. However, calling sp_help through ct_command(
CS_RPC_CMD, ... ) or JDBC/ODBC CallableStatement() classes means calling
stored procedure as an RPC.

The take-away:
	- Prepared statement is in Sybase ASE, TDS and various API's.
	- RPC's are faster than LANG calls, hence prepared statements
are faster for 
_repeated_ executions than equivalent executions of "regular"
statements. (one 
prepare + 1,000 exec's with different literals is faster than 1,000
exec's of 
1,000 statements)
	- Executing a stored procedure as an RPC is faster than
executing it as a LANG 

*) circa 1980 this was a key improvement to the host-language
integration of SQL 
as it allowed developers to provide literals to e.g. WHERE-clauses in
(as opposed to compile time). Hence the name Dynamic SQL (as opposed to
Static SQL).