RE: Is there a performance advantage to using placeholders with Sybase?

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



Steve Wechsler | [email protected]
Sybase/SQL Server/MySQL Database Administrator
212-846-5683

MTV Networks


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

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
this 
version a stored procedure was created in tempdb. Prepared statements is
a 
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
procedure.

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
conversion.
	- no parsing or optimization in ASE


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


Execution of a stored procedure can be either a LANGUAGE call or an RPC
call: 
"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(
my_command, 
CS_RPC_CMD, ... ) or JDBC/ODBC CallableStatement() classes means calling
the 
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 
call.


*) 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
runtime 
(as opposed to compile time). Hence the name Dynamic SQL (as opposed to
Static SQL).

HTH,

/Stefan