You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Alexandre Borgoltz <al...@smartjog.com> on 2005/06/09 10:02:12 UTC
Statement pooling
Hey everyone,
When accessing DB data, OJB uses a relatively small set of different
queries. The default implementation uses PreparedStatement - which is a
good thing :) with connection pooling.
I was wondering ig it was planned to use statement-level pooling to
speed up thing even more? Is there an already existing implementation
that uses statement pooling? Or maybe are there drawbacks in reusing
statements in OJB?
Thank you in advance,
And thank you for that great product!
--
Alexandre BORGOLTZ
Head of Technology
SmartJog SA
Phone: +33 (0)1 4996 6324
Fax: +33 (0)1 4996 6405
Mobile: +33 (0)6 8882 1417
alexandre.borgoltz@smartjog.com
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Statement pooling
Posted by Martin Kalén <mk...@apache.org>.
Alexandre Borgoltz wrote:
> I am using oracle8i with 8i client jar. Using that -thin- 8i JDBC driver
> , the performance tests show there is a huge difference between
> *recreating a connection each time - obviously ultimately slow
> *using a single connection but re-preparing a PreparedStatement with the
> same query - better
> *using a single connection and a single PreparedStatement, only calling
> setXXX methods between calls to .executeQuery() - the best
Even though the OJB "Oracle" platform impl will not directly control any
Oracle JDBC-driver extensions, you can still control eg Oracle row
pre-fetching with the new JDBC attributes (again, this requires you
to check out from CVS or wait for 1.0.4).
See link [1] for the complete list of JDBC properties.
You can use the Oracle 10.1.0 JDBC-driver to access your Oracle 8i RDBMS,
if it is v8.1.7.
(A new driver can almost always talk to older servers,
see link [2] for a complete compatibility matrix.)
To set Oracle row-prefetch you add the following to your OJB connection-pool
configuration:
<attribute
attribute-name="jdbc.defaultRowPrefetch"
attribute-value="(your choice here)"/>
Same warning applies for high Oracle row-prefetch as for a large DBCP
Statement pool - the JVM can quickly throw OutOfMemory exceptions if
you don't increase heap size with -Xmx.
All of these tweaks are highly application-dependent and you cannot
tell any generic "magic" values that will work well for all application
profiles. It is very dependent on wether you access large Collections
in serial order (use high row pre-fetch and OJB proxy-prefetch) or
mostly get smaller result sets in random order (turn off Oracle row-
prefetch and definately try to use OJB proxies for referenced objects
if you don't know for sure that you will access all references).
Running your OJB application for a while with IronTrack [3] attached
is a great way of monitoring the application profile a get hints
for new database indices and/or good OJB configuration settings.
Regards,
Martin
[1] http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#05_01
(scroll down to "Connection Properties Recognized by Oracle 10.1.0 JDBC Drivers")
[2] http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#02_02
[3] http://www.irongrid.com/irontracksql/
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Statement pooling
Posted by Alexandre Borgoltz <al...@smartjog.com>.
Thanks for the complete and fast answer, Martin!
>Some JDBC-drivers already perform this on the client-side
I am using oracle8i with 8i client jar. Using that -thin- 8i JDBC driver
, the performance tests show there is a huge difference between
*recreating a connection each time - obviously ultimately slow
*using a single connection but re-preparing a PreparedStatement with the
same query - better
*using a single connection and a single PreparedStatement, only calling
setXXX methods between calls to .executeQuery() - the best
I'll check it out very soon. Thanks again!
Cheers,
*Alexandre BORGOLTZ**
*Head of Technology
**SmartJog SA*
*Phone: +33 (0)1 4996 6324
Fax: +33 (0)1 4996 6405
Mobile: +33 (0)6 8882 1417
alexandre.borgoltz@smartjog.com <ma...@smartjog.com>
Martin Kalén wrote:
> Alexandre Borgoltz wrote:
>
>> When accessing DB data, OJB uses a relatively small set of different
>> queries. The default implementation uses PreparedStatement - which is
>> a good thing :) with connection pooling.
>> I was wondering ig it was planned to use statement-level pooling to
>> speed up thing even more? Is there an already existing implementation
>> that uses statement pooling? Or maybe are there drawbacks in reusing
>> statements in OJB?
>
>
> Some JDBC-drivers already perform this on the client-side, Oracle JDBC
> is an example of this. In the "Oracle9i" platform impl we set Oracle-
> specific statement caching (ie client-side pooling) and row pre-fetch
> directly from OJB.
>
>
> We have also added the possibility to use the Statement pool
> functionality
> in Commons DBCP. This will be possible in the upcoming OJB 1.0.4 or if
> you
> check out OJB from CVS from the OJB_1_0_RELEASE branch.
>
> (It should also be merged to CVS HEAD, it's only because my own lack of
> time that this did not happen yet.)
>
> You control Statement pooling by setting the following attributes on the
> connection pool in your repository_database:
> "dbcp.poolPreparedStatements" to "true"
> "dbcp.maxOpenPreparedStatements" to max # of statements in pool
> (or "0" for no limit -- but beware of OutOfMemory exceptions!)
>
> See also the preview of the next site-documentation at the following URL
> (WARNING: transient content, please do not use for linking - this will
> be the next main OJB website):
> http://people.apache.org/~mkalen/ojb/site/docu/guides/repository.html#Custom+attributes
>
>
>
> NB: You need to switch from the default ConnectionFactory to
> ConnectionFactoryDBCPImpl
> in your OJB.properties for any custom dbcp.*-attributes to have any
> effect.
>
> Both the default pooled OJB ConnectionFactory-implementation and the DBCP
> implementation use Commons Pool for the underlying object pools so
> performance,
> configuration and behaviour will be very similar.
>
> Regards,
> Martin
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Statement pooling
Posted by Martin Kalén <mk...@apache.org>.
Alexandre Borgoltz wrote:
> When accessing DB data, OJB uses a relatively small set of different
> queries. The default implementation uses PreparedStatement - which is a
> good thing :) with connection pooling.
> I was wondering ig it was planned to use statement-level pooling to
> speed up thing even more? Is there an already existing implementation
> that uses statement pooling? Or maybe are there drawbacks in reusing
> statements in OJB?
Some JDBC-drivers already perform this on the client-side, Oracle JDBC
is an example of this. In the "Oracle9i" platform impl we set Oracle-
specific statement caching (ie client-side pooling) and row pre-fetch
directly from OJB.
We have also added the possibility to use the Statement pool functionality
in Commons DBCP. This will be possible in the upcoming OJB 1.0.4 or if you
check out OJB from CVS from the OJB_1_0_RELEASE branch.
(It should also be merged to CVS HEAD, it's only because my own lack of
time that this did not happen yet.)
You control Statement pooling by setting the following attributes on the
connection pool in your repository_database:
"dbcp.poolPreparedStatements" to "true"
"dbcp.maxOpenPreparedStatements" to max # of statements in pool
(or "0" for no limit -- but beware of OutOfMemory exceptions!)
See also the preview of the next site-documentation at the following URL
(WARNING: transient content, please do not use for linking - this will
be the next main OJB website):
http://people.apache.org/~mkalen/ojb/site/docu/guides/repository.html#Custom+attributes
NB: You need to switch from the default ConnectionFactory to ConnectionFactoryDBCPImpl
in your OJB.properties for any custom dbcp.*-attributes to have any effect.
Both the default pooled OJB ConnectionFactory-implementation and the DBCP
implementation use Commons Pool for the underlying object pools so performance,
configuration and behaviour will be very similar.
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org