You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Armin Waibel <ar...@apache.org> on 2005/04/23 19:12:59 UTC
PreparedStatement caching
Hi all,
I stumble across a performance issue when the sql-driver doesn't support
caching of PreparedStatements or doesn't work very well.
When running the 'perf-test' target against maxDB I got the following
result:
[ojb] ================================================================
[ojb] OJB PERFORMANCE TEST SUMMARY
[ojb] 12 concurrent threads, handle 500 objects per thread
[ojb] - performance mode - results per thread
[ojb] ================================================================
[ojb] API Period Total Total Insert Fetch Update Delete
[ojb] [sec] [sec] [%] [msec] [msec] [msec] [msec]
[ojb] ----------------------------------------------------------------
[ojb] JDBC 9.927 7.544 100 5392 68 1490 593
[ojb] ODMG 22.958 16.831 223 9595 325 4131 2779
[ojb] PB 22.127 17.16 227 10511 120 3776 2753
[ojb] OTM 40.804 32.639 433 23024 225 5056 4332
[ojb] ================================================================
You can see that all OJB API don't perform very well compared with plain
JDBC (PB is 120% slower!!). The problem is that OJB use for each object
a new PS instead reusing the old one.
I wrote a StatementCache and run this test again
[ojb] ================================================================
[ojb] OJB PERFORMANCE TEST SUMMARY
[ojb] 12 concurrent threads, handle 500 objects per thread
[ojb] - performance mode - results per thread
[ojb] ================================================================
[ojb] API Period Total Total Insert Fetch Update Delete
[ojb] [sec] [sec] [%] [msec] [msec] [msec] [msec]
[ojb] ----------------------------------------------------------------
[ojb] JDBC 10.083 8.248 100 5517 72 1652 1006
[ojb] ODMG 14.89 11.044 134 6764 366 2318 1595
[ojb] PB 13.948 11.22 136 7305 215 1997 1701
[ojb] OTM 22.872 17.597 213 12787 308 2435 2066
[ojb] ================================================================
Now it's totally different. The PB, ODMG API are only 35% slower than
plain JDBC. The statement cache boost performance.
Is it reasonable to include a new configurable feature
"statementCaching=on/off" in 1.0.x and 1.x or should always the
jdbc-driver or connection-pool do such things?
regards,
Armin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PreparedStatement caching
Posted by Thomas Dudziak <to...@gmail.com>.
On 4/23/05, Armin Waibel <ar...@apache.org> wrote:
> Hi all,
>
> I stumble across a performance issue when the sql-driver doesn't support
> caching of PreparedStatements or doesn't work very well.
<snip>
> Is it reasonable to include a new configurable feature
> "statementCaching=on/off" in 1.0.x and 1.x or should always the
> jdbc-driver or connection-pool do such things?
Don't know about 1.0.x, but for 1.1.x IMOthis is reasonable both as a
system-wide and a PC-level setting (thus allowing to turn it of for a
specific configuration) with on as the default.
Tom
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PreparedStatement caching
Posted by Armin Waibel <ar...@apache.org>.
Martin Kalén wrote:
> Anything that can boost OJB performance and that is switched off by default
> should only be a benefit, as far as I can tell.
>
> But please leave this disable by default since memory requirements usually
> rise quite rapidly if caching stuff that might hold on to ResultSet data
> etc
> and since it might introduce extra overhead for the drivers that already do
> caching of PreparedStatement automatically.
>
This was my intention, by default disabled and configurable at runtime.
I will set this on my todo-list for 1.x since this needs some changes in
StatementForClass interface.
> When you create your implementation, also consider dropping everything from
> you cache that does not have any positional parameter at all since caching
> eg:
> SELECT column FROM TABLE WHERE conditional_column = 1;
> SELECT column FROM TABLE WHERE conditional_column = 2;
> SELECT column FROM TABLE WHERE conditional_column = 3;
> ...etc
> will fill up the cache quickly and stop it from making greater benefit for
> proper reusable statements like:
> SELECT column FROM TABLE WHERE conditional_column = ?;
> (Since I assume you have some LRU/round robin or similar algorithm with
> a fixed max size.)
>
I will keep things as simple as possible and only cache
PreparedStatement instances and only till the used connection was
returned to pool (this should prevent any memory issues).
>
>
> Also, when you are working in this area: could you have a look at this
> class:
> org.apache.ojb.broker.accesslayer.SQLCachingStatementsForClass
>
> To me it looks really similar to StatementsForClassImpl, but never (?)
> had a
> commented entry in OJB.properties for StatementsForClassClass setting and
> is per Vadim's last patch outdated (not supporting ResultSet as out-param
> from callable statements). Could we just remove this class?
>
yep, this class can be removed.
regards,
Armin
> Regards,
> Martin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PreparedStatement caching
Posted by Martin Kalén <mk...@apache.org>.
Armin Waibel wrote:
> Now it's totally different. The PB, ODMG API are only 35% slower than
> plain JDBC. The statement cache boost performance.
>
> Is it reasonable to include a new configurable feature
> "statementCaching=on/off" in 1.0.x and 1.x or should always the
> jdbc-driver or connection-pool do such things?
Anything that can boost OJB performance and that is switched off by default
should only be a benefit, as far as I can tell.
But please leave this disable by default since memory requirements usually
rise quite rapidly if caching stuff that might hold on to ResultSet data etc
and since it might introduce extra overhead for the drivers that already do
caching of PreparedStatement automatically.
When you create your implementation, also consider dropping everything from
you cache that does not have any positional parameter at all since caching
eg:
SELECT column FROM TABLE WHERE conditional_column = 1;
SELECT column FROM TABLE WHERE conditional_column = 2;
SELECT column FROM TABLE WHERE conditional_column = 3;
...etc
will fill up the cache quickly and stop it from making greater benefit for
proper reusable statements like:
SELECT column FROM TABLE WHERE conditional_column = ?;
(Since I assume you have some LRU/round robin or similar algorithm with
a fixed max size.)
Also, when you are working in this area: could you have a look at this class:
org.apache.ojb.broker.accesslayer.SQLCachingStatementsForClass
To me it looks really similar to StatementsForClassImpl, but never (?) had a
commented entry in OJB.properties for StatementsForClassClass setting and
is per Vadim's last patch outdated (not supporting ResultSet as out-param
from callable statements). Could we just remove this class?
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PreparedStatement caching
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi armin,
if it affects performance that much, i think, we should provide this
feature in 1.1
i do not know how it interferes with the drivers cache or managed
environments.
jakob
Armin Waibel schrieb:
> Hi all,
>
> I stumble across a performance issue when the sql-driver doesn't
> support caching of PreparedStatements or doesn't work very well.
>
> When running the 'perf-test' target against maxDB I got the following
> result:
> [ojb] ================================================================
> [ojb] OJB PERFORMANCE TEST SUMMARY
> [ojb] 12 concurrent threads, handle 500 objects per thread
> [ojb] - performance mode - results per thread
> [ojb] ================================================================
> [ojb] API Period Total Total Insert Fetch Update Delete
> [ojb] [sec] [sec] [%] [msec] [msec] [msec] [msec]
> [ojb] ----------------------------------------------------------------
> [ojb] JDBC 9.927 7.544 100 5392 68 1490 593
> [ojb] ODMG 22.958 16.831 223 9595 325 4131 2779
> [ojb] PB 22.127 17.16 227 10511 120 3776 2753
> [ojb] OTM 40.804 32.639 433 23024 225 5056 4332
> [ojb] ================================================================
>
> You can see that all OJB API don't perform very well compared with
> plain JDBC (PB is 120% slower!!). The problem is that OJB use for each
> object a new PS instead reusing the old one.
>
> I wrote a StatementCache and run this test again
> [ojb] ================================================================
> [ojb] OJB PERFORMANCE TEST SUMMARY
> [ojb] 12 concurrent threads, handle 500 objects per thread
> [ojb] - performance mode - results per thread
> [ojb] ================================================================
> [ojb] API Period Total Total Insert Fetch Update Delete
> [ojb] [sec] [sec] [%] [msec] [msec] [msec] [msec]
> [ojb] ----------------------------------------------------------------
> [ojb] JDBC 10.083 8.248 100 5517 72 1652 1006
> [ojb] ODMG 14.89 11.044 134 6764 366 2318 1595
> [ojb] PB 13.948 11.22 136 7305 215 1997 1701
> [ojb] OTM 22.872 17.597 213 12787 308 2435 2066
> [ojb] ================================================================
>
> Now it's totally different. The PB, ODMG API are only 35% slower than
> plain JDBC. The statement cache boost performance.
>
> Is it reasonable to include a new configurable feature
> "statementCaching=on/off" in 1.0.x and 1.x or should always the
> jdbc-driver or connection-pool do such things?
>
> regards,
> Armin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org