You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Salminen Pasi <Pa...@tapiola.fi> on 2007/07/30 16:07:34 UTC
How to cache prepared statements?
Hi,
It seems like OpenJPA is closing the prepared statement after each
persist call. Is there any way to avoid this behaviour? For example,
this code snippet
TestEntity te;
for( int i = 0; i < 100; i++ )
{
te = new TestEntity( );
te.setId( ( short ) i );
te.setName( "John Doe" );
te.setNickName( "JoJo" );
_em.persist( te );
}
seems to generate zillions of prepares and closes (in this case 100).
...
2795 [main] INFO jdbc.audit - 1. Connection.prepareStatement(INSERT INTO
PERSON (id, NAME, NICKNAME) VALUES (?, ?, ?)) returned
net.sf.log4jdbc.PreparedStatementSpy@1f0e1f0e
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setShort(1, 98)
returned
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(2, "John
Doe") returned
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(3, "JoJo")
returned
2795 [main] INFO jdbc.sqlonly - INSERT INTO PERSON (id, NAME, NICKNAME)
VALUES (98, 'John Doe', 'JoJo')
2795 [main] INFO jdbc.sqltiming - INSERT INTO PERSON (id, NAME,
NICKNAME) VALUES (98, 'John Doe', 'JoJo') {executed in 0 msec}
2795 [main] INFO jdbc.audit - 1. PreparedStatement.executeUpdate()
returned 1
2795 [main] INFO jdbc.audit - 1. PreparedStatement.close() returned
2795 [main] INFO jdbc.audit - 1. Connection.prepareStatement(INSERT INTO
PERSON (id, NAME, NICKNAME) VALUES (?, ?, ?)) returned
net.sf.log4jdbc.PreparedStatementSpy@36763676
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setShort(1, 99)
returned
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(2, "John
Doe") returned
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(3, "JoJo")
returned
2795 [main] INFO jdbc.sqlonly - INSERT INTO PERSON (id, NAME, NICKNAME)
VALUES (99, 'John Doe', 'JoJo')
2795 [main] INFO jdbc.sqltiming - INSERT INTO PERSON (id, NAME,
NICKNAME) VALUES (99, 'John Doe', 'JoJo') {executed in 0 msec}
2795 [main] INFO jdbc.audit - 1. PreparedStatement.executeUpdate()
returned 1
2795 [main] INFO jdbc.audit - 1. PreparedStatement.close() returned
I think it's horrible waste of resources even with prepared statement
caches. It may work with transactional load (Java EE style) but with
batches it probably doesn't. Any ideas?
Regards,
Paci
Re: How to cache prepared statements?
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Also note that these is an example of using OpenJPA with DBCP at:
http://openjpa.apache.org/faq.html
On Jul 30, 2007, at 8:16 AM, Patrick Linskey wrote:
> Hi,
>
> The built-in OpenJPA DataSource implementation doesn't do any prepared
> statement caching. If you plug in a DataSource (such as DBCP, for
> example) that does handle prepared statement caching, OpenJPA should
> integrate well with it.
>
> -Patrick
>
> On 7/30/07, Salminen Pasi <Pa...@tapiola.fi> wrote:
>> Hi,
>>
>> It seems like OpenJPA is closing the prepared statement after each
>> persist call. Is there any way to avoid this behaviour? For example,
>> this code snippet
>>
>> TestEntity te;
>>
>> for( int i = 0; i < 100; i++ )
>> {
>> te = new TestEntity( );
>> te.setId( ( short ) i );
>> te.setName( "John Doe" );
>> te.setNickName( "JoJo" );
>> _em.persist( te );
>> }
>>
>> seems to generate zillions of prepares and closes (in this case 100).
>>
>> ...
>> 2795 [main] INFO jdbc.audit - 1. Connection.prepareStatement
>> (INSERT INTO
>> PERSON (id, NAME, NICKNAME) VALUES (?, ?, ?)) returned
>> net.sf.log4jdbc.PreparedStatementSpy@1f0e1f0e
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setShort(1, 98)
>> returned
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(2, "John
>> Doe") returned
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(3,
>> "JoJo")
>> returned
>> 2795 [main] INFO jdbc.sqlonly - INSERT INTO PERSON (id, NAME,
>> NICKNAME)
>> VALUES (98, 'John Doe', 'JoJo')
>> 2795 [main] INFO jdbc.sqltiming - INSERT INTO PERSON (id, NAME,
>> NICKNAME) VALUES (98, 'John Doe', 'JoJo') {executed in 0 msec}
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.executeUpdate()
>> returned 1
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.close() returned
>> 2795 [main] INFO jdbc.audit - 1. Connection.prepareStatement
>> (INSERT INTO
>> PERSON (id, NAME, NICKNAME) VALUES (?, ?, ?)) returned
>> net.sf.log4jdbc.PreparedStatementSpy@36763676
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setShort(1, 99)
>> returned
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(2, "John
>> Doe") returned
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(3,
>> "JoJo")
>> returned
>> 2795 [main] INFO jdbc.sqlonly - INSERT INTO PERSON (id, NAME,
>> NICKNAME)
>> VALUES (99, 'John Doe', 'JoJo')
>> 2795 [main] INFO jdbc.sqltiming - INSERT INTO PERSON (id, NAME,
>> NICKNAME) VALUES (99, 'John Doe', 'JoJo') {executed in 0 msec}
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.executeUpdate()
>> returned 1
>> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.close() returned
>>
>> I think it's horrible waste of resources even with prepared statement
>> caches. It may work with transactional load (Java EE style) but with
>> batches it probably doesn't. Any ideas?
>>
>> Regards,
>>
>> Paci
>>
>
>
> --
> Patrick Linskey
> 202 669 5907
Re: How to cache prepared statements?
Posted by Patrick Linskey <pl...@gmail.com>.
Hi,
The built-in OpenJPA DataSource implementation doesn't do any prepared
statement caching. If you plug in a DataSource (such as DBCP, for
example) that does handle prepared statement caching, OpenJPA should
integrate well with it.
-Patrick
On 7/30/07, Salminen Pasi <Pa...@tapiola.fi> wrote:
> Hi,
>
> It seems like OpenJPA is closing the prepared statement after each
> persist call. Is there any way to avoid this behaviour? For example,
> this code snippet
>
> TestEntity te;
>
> for( int i = 0; i < 100; i++ )
> {
> te = new TestEntity( );
> te.setId( ( short ) i );
> te.setName( "John Doe" );
> te.setNickName( "JoJo" );
> _em.persist( te );
> }
>
> seems to generate zillions of prepares and closes (in this case 100).
>
> ...
> 2795 [main] INFO jdbc.audit - 1. Connection.prepareStatement(INSERT INTO
> PERSON (id, NAME, NICKNAME) VALUES (?, ?, ?)) returned
> net.sf.log4jdbc.PreparedStatementSpy@1f0e1f0e
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setShort(1, 98)
> returned
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(2, "John
> Doe") returned
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(3, "JoJo")
> returned
> 2795 [main] INFO jdbc.sqlonly - INSERT INTO PERSON (id, NAME, NICKNAME)
> VALUES (98, 'John Doe', 'JoJo')
> 2795 [main] INFO jdbc.sqltiming - INSERT INTO PERSON (id, NAME,
> NICKNAME) VALUES (98, 'John Doe', 'JoJo') {executed in 0 msec}
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.executeUpdate()
> returned 1
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.close() returned
> 2795 [main] INFO jdbc.audit - 1. Connection.prepareStatement(INSERT INTO
> PERSON (id, NAME, NICKNAME) VALUES (?, ?, ?)) returned
> net.sf.log4jdbc.PreparedStatementSpy@36763676
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setShort(1, 99)
> returned
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(2, "John
> Doe") returned
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(3, "JoJo")
> returned
> 2795 [main] INFO jdbc.sqlonly - INSERT INTO PERSON (id, NAME, NICKNAME)
> VALUES (99, 'John Doe', 'JoJo')
> 2795 [main] INFO jdbc.sqltiming - INSERT INTO PERSON (id, NAME,
> NICKNAME) VALUES (99, 'John Doe', 'JoJo') {executed in 0 msec}
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.executeUpdate()
> returned 1
> 2795 [main] INFO jdbc.audit - 1. PreparedStatement.close() returned
>
> I think it's horrible waste of resources even with prepared statement
> caches. It may work with transactional load (Java EE style) but with
> batches it probably doesn't. Any ideas?
>
> Regards,
>
> Paci
>
--
Patrick Linskey
202 669 5907