Hi Jeff,

I guess you are right about performance, at least for the time being with MySQL.
However I would encourage you to use PreparedStatements for the following
reasons:

1. more portable - as you say, you will get big benefits in Oracle, for example,
this way

2. easier for the programmer - if any of your variable data might contain quote
characters or other chars requiring escape sequences, the JDBC driver handles
all this for you if you use PreparedStatements. If you use the ordinary
Statement object, then the String you construct for the query must have all
these things escaped by hand.

In essence, even if there is no significant performance benefit, I'd say you get
more robust code for virtually no extra cost. Just my $0.02 worth.

Hope this helps Keith

-----Original Message----- From: Jeff Mathis [mailto:jmat...@predict.com] Sent: Wed 21/05/2003 23:39 To: ja...@lists.mysql.com Cc: Subject: Statements vs PreparedStatements

Hi folks,

I'm using the 3.07 release with mysql 4.04. All our tables are InnoDB tables. The API has a class per table, with several classes sharing inheritance heirarchies as appropriate. I've got a central DBStore class that everything goes through for queries. I have my own set of "Attribute" classes, LongAttr, IntAttr, DateAttr, etc, that everything coming from the database becomes rather than using java.sql objects or primitives. These classes take care of formatting themselves for queries, using or not using quotes when necessary, etc. When loading from a resultset, I always call the appropriate getXXX method with a column index.

For everything I do, I create a statement from my connection, issue the query, look at the result set, then close the statement. Because mysql does not "compile" sql and cache it, I have been of the opinion that it is not necessary to create prepared statements. In contrast, with an Oracle database, use of PreparedStatements is almost required.

The question is, would using PreparedStatements speed things up? I don't think I'm suffering from any performance hit now. About the only thing I've noticed is when tables get a lot of columns (~80 or so), setting all the attributes seems to be slower. But, programmatically, is it wrong to do things as I've indicated?

always looking for improvements ...

jeff

-- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company jmat...@predict.com 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505