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,
And as with stored procs, you can't pass TEXT or IMAGE columns as
parameters to a prepared statement.
Sent by: firstname.lastname@example.org
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: email@example.com [mailto:firstname.lastname@example.org]
Sent: Monday, January 07, 2008 6:16 PM
To: Wechsler, Steven; email@example.com
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
P.S. If you’re looking to optimize, then another thing you should look at
From: firstname.lastname@example.org [mailto:email@example.com] 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 | firstname.lastname@example.org
Sybase/SQL Server/MySQL Database Administrator
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
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.