Up Prev Next
From: Stefan Karlsson <Stefan dot Karlsson at sybase dot com>
Subject: Re: Is there a performance advantage to using placeholders with Sybase?
Date: Jan 8 2008 1:06PM
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.
- 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
- 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 runtime
(as opposed to compile time). Hence the name Dynamic SQL (as opposed to Static SQL).
Wechsler, Steven wrote:
> But does Sybase actually have the API functionality to prepare a SQL
> statement and then insert the parameters? I seem to recall reading
> somewhere that even though DBI supports it, behind the scenes CT-Library
> actually has to resend the entire command each time execute() is
> From: firstname.lastname@example.org [mailto:email@example.com]
> Sent: Monday, January 07, 2008 6:16 PM
> To: Wechsler, Steven; firstname.lastname@example.org
> Subject: RE: Is there a performance advantage to using placeholders with
> I would say yes. Sybase has to prepare the query (compile) every time
> you run a prepare. If you only 1 run prepare, then you're having to deal
> with query time only. This should be more efficient. I've been told
> there's a risk of some sort of funky tempdb usage based on the contents
> of the prepare when place holders are involved, but I've really never
> noticed an issue.
> P.S. If you're looking to optimize, then another thing you should look
> at is prepare_cached.
> From: email@example.com [mailto:firstname.lastname@example.org]
> On Behalf Of Wechsler, Steven
> Sent: Monday, January 07, 2008 4:25 PM
> To: email@example.com
> Subject: Is there a performance advantage to using placeholders with
> DBI documentation says that you're better off using placeholders rather
> than continully preparing and executing, or using $dbh->do(). Does this
> hold true with Sybase?
> Steve Wechsler | firstname.lastname@example.org
> Sybase/SQL Server/MySQL Database Administrator
> MTV Networks
Sybase Sverige AB, Knarrarnäsgatan 7, 164 40 Kista (SWEDEN)
Company Registration Number 556527-0229
The information contained in this email is confidential and intended only for
the addressee. If you are not the intended recipient (or have received this
e-mail in error) please notify the sender immediately and destroy this e-mail.
Any unauthorized copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.