sybperl-l Archive

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

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: todd.e.rinaldo@jpmorgan.com [mailto:todd.e.rinaldo@jpmorgan.com] 
> Sent: Monday, January 07, 2008 6:16 PM
> To: Wechsler, Steven; sybperl-l@peppler.org
> 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: owner-sybperl-l@peppler.org [mailto:owner-sybperl-l@peppler.org]
> On Behalf Of Wechsler, Steven
> Sent: Monday, January 07, 2008 4:25 PM
> To: sybperl-l@peppler.org
> 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 | steven.wechsler@mtvstaff.com 
> 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.