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: psinnottie at aol dot com
Subject: Re: Is there a performance advantage to using placeholders with Sybase?
Date: Jan 8 2008 9:13AM

 Hi Michael,

based on previous experience it seemed the statement cache used by prepare_cached was ever expanding. Is this still true? 




-----Original Message-----
Sent: Tue, 8 Jan 2008 7:40 am
Subject: RE: Is there a performance advantage to using placeholders with Sybase?

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,

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


"Wechsler, Steven"

Sent by:

08.01.2008 03:28




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


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.


[] On Behalf Of Wechsler, Steven

Sent: Monday, January 07, 2008 4:25 PM


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 |

Sybase/SQL Server/MySQL Database Administrator


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 for disclosures relating to UK legal entities.


More new features than ever.  Check out the new AOL Mail ! -