Very Computer

I am trying to test my code, which selects only one row in join tables
of more than 40000 rows. First I didn't used the Prepared Statement

String sql = "select * from .... where ...";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sqlStatement);
get the row from the ResultSet
Time to return the data: 4s

Second I used the PreparedStatement:
ps=connection.prepareStatement(sql);
ps.setString( 1, "value" );
rs = ps.executeQuery();
time to return the data: 1min45s

I knew the PreparedStatement is ideall for OLTP systems with many
updates. But I was surprised to get a poor performance using it in my
DSS system.

Do I do any thing wrong ?

thanks

--
     - Mourad -

Sent via Deja.com http://www.deja.com/
Before you buy.

I made now a loop of 100 times to see wether it will make a difference
by using the same statement, but I have always a long response time...

any help, may be sample code for it ?
thanks.

--
     - Mourad -

Sent via Deja.com http://www.deja.com/
Before you buy.

Dear Mourad:

Have you declared the prepared statement before entering the loop?

Hi. Two things:

1 - Just to be sure, you're making one PreparedStatement, then re-executing
it 100 times in a loop, right?

2 - A PreparedStatement only saves the DBMS having to reparse the SQL and
generate a plan for each execution. If the SQL is simple, but the work it
describes for the DBMS to do is big, such as large result sets, joins, non-
indexed sort orders etc, are still going to be the bulk of the execution
time, and these won't change.
Joe

--

PS: Folks: BEA WebLogic is in S.F. with both entry and advanced positions for
people who want to work with Java and E-Commerce infrastructure products. Send

--------------------------------------------------------------------------------
                    The Weblogic Application Server from BEA                    
         JavaWorld Editor's Choice Award: Best Web Application Server
  Java Developer's Journal Editor's Choice Award: Best Web Application Server
     Crossroads A-List Award: Rapid Application Development Tools for Java    
Intelligent Enterprise RealWare: Best Application Using a Component Architecture
                 http://www.bea.com/press/awards_weblogic.html

IMHO the prepared statement allows the query optimizer to consider all
possible acces paths and generate an optimized query plan. In your
case, as you will be reading ALL the rows in table, there is no
optimal access plan - just read all the rows. Your longer response
time ist probably just the time required to 'compile' the query.

Try using the prepared statement on a more 'real world' example
involving maybe two or three tables and most importantly a WHERE
clause and several indexes on the tables. I'm sure you will see an
improvement or else your DB sucks - what DB ARE you using?

John Muir

Hi,

I have some other thoughs along the line of PreparedStatement performance. I
am going to do some testing to try to answer them myself, but I try the
thougts on this group first.

My concern is that I create new PreparedStatement-objects each time I run
the same query. It is not so stupid as it seems, maybe, but in an
application, in the data layer, I use one method to execute one query. The
method does all it own housekeeping; getting a connection from a pool,
creating and executing the query and converting the result to a Collection
or something of a "data carrier" :-). I would have liked to pool these
statements, because I do not like to create the statement over and over
again each time the method is called, but since the PreparedStatement
interface does not provide a setConnection method, it is hard to pool
PreparedStatements. I found someone who had done it (OOP Research) though,
and another simple solution could be; create a priority queue of Prepared
Statements, each using one of yuor precious connections, and when you need a
query that is not "queued", drop one of the existing and create a new one
using the old one's connection. It would be a rater easy thing to make, and
it wouldn't use more resources than a conventional connection pool, but ...
Is is worth the effort?

I haven't read any advanced details about JDBC, so therefore my questions
will propably be answered RTFM, but I try:

If I for instance create a PreparedStatement for "SELECT * FROM PERSON" in a
method. Then 10 minutes later the user does something that triggers the same
method. Will the query be compiled again in the database? The database will
probably cache the thing, but since I drop the connection between the calls
will it find the cached version during the next call? I'm sure there are
some performance penalty on the first call to a prepared statement, so if we
get "first call" all the times, I wander why the JDBC spec. doesn't give us
pooled statements (which is a more cool resource management thing than
regular connection pooling :-)

Sp1

Quote:

> If I for instance create a PreparedStatement for "SELECT * FROM PERSON" in
a
> method. Then 10 minutes later the user does something that triggers the
same
> method. Will the query be compiled again in the database? The database
will
> probably cache the thing, but since I drop the connection between the
calls
> will it find the cached version during the next call? I'm sure there are
> some performance penalty on the first call to a prepared statement, so if
we
> get "first call" all the times, I wander why the JDBC spec. doesn't give
us
> pooled statements (which is a more cool resource management thing than
> regular connection pooling :-)

It'll depend on the DB in question, of course, but one approach would be for
the DB to create a temporary stored procedure for each PreparedStatement.
In this case, the query plan of the procedure will be cached in the DB, so
future PreparedStatements could well use the cached version.  On the other
hand, if the procedure is local to your connection, this may be of no use
whatsoever :-/