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

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 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
> called...
> ________________________________
> From: [] 
> Sent: Monday, January 07, 2008 6:16 PM
> To: Wechsler, Steven;
> Subject: RE: Is there a performance advantage to using placeholders with
> Sybase?
> 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: []
> On Behalf Of Wechsler, Steven
> Sent: Monday, January 07, 2008 4:25 PM
> To:
> Subject: Is there a performance advantage to using placeholders with
> Sybase?
> 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 | 
> Sybase/SQL Server/MySQL Database Administrator 
> 212-846-5683 
> 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.