PreparedStatement Performance

PreparedStatement Performance

Post by moth.. » Sat, 02 Dec 2000 04:00:00



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.

 
 
 

PreparedStatement Performance

Post by moth.. » Sat, 02 Dec 2000 04:00:00


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 -



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

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

 
 
 

PreparedStatement Performance

Post by Carlos F. Engui » Mon, 04 Dec 2000 04:00:00


Dear Mourad:

Have you declared the prepared statement before entering the loop?


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



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

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

 
 
 

PreparedStatement Performance

Post by Joseph Weinstei » Wed, 06 Dec 2000 04:00:00



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

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

> --
>      - Mourad -



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

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

--

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

 
 
 

PreparedStatement Performance

Post by John Mui » Thu, 07 Dec 2000 15:20:39


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


>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

 
 
 

PreparedStatement Performance

Post by Espen Frimann Kore » Tue, 19 Dec 2000 03:07:03


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


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

 
 
 

PreparedStatement Performance

Post by Kenny MacLeo » Wed, 20 Dec 2000 00:59:30




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 :-/
 
 
 

1. Performance problem in using preparedStatement with mssqlserver driver

I am using MS SQL Server 2000 and has a relatively large
table (200000 rows) with a column (called OrderState) that
is a Varchar type. An index was created on this column to
speed up searches. My application is written in Java and
it uses PreparedStatement to select records from this
table. The performance was good until I recently switched
to use mssqlserver JDBC driver provided by Microsoft (I
was using JDBC-ODBC driver before). With the new
mssqlserver driver, every string parameter in the prepared
statement is converted to unicode (NVARCHAR), as shown
below:

exec sp_executesql N'SELECT OrderId from OrderInformation

The problem with this is that, in searching for an index
value that will match N'CN_TS', SQL Server will perform an
index scan (instead of an index seek) and convert every
index value to NVARCHAR in order to compare it to
N'CN_TS'. As a result, the search becomes very slow.
This did not happen when I was using the JDBC-ODBC driver,
which converts the same prepared statement to:



In this case, SQL Server simply performs an index seek,
which is fast.

One way to solve the problem is to change the type of
OrderState to NVARCHAR (from VARCHAR), but I am hoping to
have a better solution.

Any ideas? I appreciate any help any one can provide.
Thanks in advance. - Carlos

2. Resource Files

3. Fact or Fiction: Caching PreparedStatements implies better performance?

4. Check SQL 6.5 Sort Order

5. JDBC preparedStatement horible performance

6. Where to change datetime on SQL server

7. Performance of Updatable ResultSet vs. PreparedStatement

8. Vertex Pittsburgh

9. Oracle Performance implications with many PreparedStatements.

10. SQL Server Performance Problem - Good query performance, bad update performance

11. Bug in PreparedStatement.setDate

12. preparedStatements and inplicit Transactions