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 | firstname.lastname@example.org
Sybase/SQL Server/MySQL Database Administrator
From: email@example.com [mailto:firstname.lastname@example.org]
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,
ASE capabilities, but want to describe it in general:
Prepared statements, a.k.a. Dynamic SQL(*), is implemented in various
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.
as the ct_dynamic() calls.
With the prepare call, users prepare a statement. This causes ASE to
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
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
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.
- 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"
prepare + 1,000 exec's with different literals is faster than 1,000
- 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