PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: michael dot x dot peppler at jpmchase dot com
Subject: RE: Is there a performance advantage to using placeholders with Sybase?
Date: Jan 8 2008 7:40AM

Yes, OpenClient has an API that creates a "prepared" statement (actually 
known as a light-weight stored procedure) for you, it analyses and checks 
datatypes for all parameters, and can then be used with great efficiency. 
It also takes care of any quoting issues for strings.

The tempdb caveat that Todd mentioned existed back in 10.0.x where ASE 
actually created a "real" stored proc in tempdb, which had some pretty 
serious side-effects on tempdb usage (locks on sysobjects, etc). Since 
11.5 (at least, maybe earlier) these temporary procs are only created in 
memory, so there is no hit on tempdb or on other resources.

Todd also mentions using prepare_cached(). While this is a good idea as it 
will help to keep prepared statements around when you need to reuse them 
one should be aware of the risk of using two cached statements at the same 
time: for example if one statement is a SELECT which returns many rows, 
and the other is an INSERT or UPDATE, and you execute the second statement 
before fetching all the rows in the first statement then you'll get an 
error because you can't have two active statements on the same connection.

Another limitation is that placeholders can only be used for single 
statement SQL batches (i.e. a single SELECT/INSERT/UPDATE/DELETE 
statement). You can't use placeholders if you want to submit a statement 
that includes other T-SQL code (flow control, conditionals, while loops, 
etc).

And as with stored procs, you can't pass TEXT or IMAGE columns as 
parameters to a prepared statement.

Michael





"Wechsler, Steven"  
Sent by: owner-sybperl-l@peppler.org
08.01.2008 03:28

To

cc

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






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 
 


-----------------------------------------
This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to UK legal entities.