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

A quick look at the DBI code which implements prepare_cached() would seem 
to indicate that the cache isn't cleared, but is of course local to the 
connection.

If you do a lot of different statement prepares then this cache may grow 
quite a bit, and you will be holding resources on the data server for 
statements that probably won't be re-executed very often, so it isn't such 
a great idea.

However, if you have a loop with a few prepare() calls that are 
intermingled then prepare_cached() may certainly improve things for you.

Michael




[email protected] 
08.01.2008 10:13

To
[email protected], [email protected]
cc

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






Hi Michael,

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


Thanks
Peter


-----Original Message-----
From: [email protected]
To: [email protected]
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, 
etc). 

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

Michael 




"Wechsler, Steven" <[email protected]> 
Sent by: [email protected] 
08.01.2008 03:28 

To
<[email protected]> 
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: [email protected] [mailto:[email protected]] 
Sent: Monday, January 07, 2008 6:16 PM
To: Wechsler, Steven; [email protected]
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: [email protected] [mailto:[email protected]] On 
Behalf Of Wechsler, Steven
Sent: Monday, January 07, 2008 4:25 PM
To: [email protected]
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 | [email protected] 
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. 
More new features than ever. Check out the new AOL Mail! 


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