You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomee.apache.org by pmo_tomee <ma...@gmail.com> on 2017/11/02 05:35:41 UTC
SQL insert taking too much time in TomEE-7.0.1
Hi Team,
I am using JPA and Hibernate-5.2.8 (as persistence provider) in TomEE.
Using Oracle 10g DB.
I tested to insert 400 rows in TomEE and JONAS server. TomEE taking 20 sec
but jonas taking only 4 sec.
I have tested in TomEE with batch insert by <property
name="hibernate.jdbc.batch_size" value="50" /> but taking 20 sec aslo.
I have checked the log in Oracle for both server given below.
*JONAS server*
--------------------
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 26 0.11 0.16 10 20 7331
380
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 27 0.11 0.16 10 20 7331
380
*Here JONAS inserting 380 rows by 26 Executions*
------------------------------------------------------------------------------------
TomEE server
-------------------------
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 380 0.01 0.01 0 0 0
0
Execute 380 1.26 1.28 9 393 6218
380
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 760 1.27 1.30 9 393 6218
380
*Here TomEE inserting 380 rows by 380 executions*
Could you help me why TomEE taking 20 sec to insert.
Is there any way to fixed it ?
Thanks & Regards
Prasenjit Mahato
mahato1987@gmail.com
--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by Steve Goldsmith <sg...@gmail.com>.
Indeed, I missed the Jonas piece :) No worries.
On Thu, Nov 2, 2017 at 12:32 PM, Andy Gumbrecht <ag...@tomitribe.com>
wrote:
> Yep, Steve, I'm sorry if I was short and I didn't look closely - Intention
> is not to start an argument, just to focus on the JPA solution for
> Prasenjit.
>
> Thanks,
>
> Andy.
>
>
> On 02/11/17 16:16, Steve Goldsmith wrote:
>
>> The issues is performance and if this is a one off batch insert then I'm
>> offering a solution. Alternatives are good and yes as I described before I
>> survived fine without all the JPA "features" with a production application
>> handling millions of transactions a day. This isn't theoretical and it
>> works in the real world.
>>
>> I'm not sure why you think there's no transactions. Caching is simple
>> using
>> JCache (my app did this as well) which is agnostic to the persistence
>> layer. If you think DbUtils adds 256% over pure JDBC then you really have
>> not used it before or done any testing with it. Most JDBC wrappers add
>> very
>> little latency as most of that is happening in the database. In essence
>> you
>> are writing about the same lines of code as well.
>>
>> Maybe you didn't take a close look at the tests, but it throws out the
>> first run to account for any initialization/lazy optimizations in JPA.
>> Again, you may not find it useful, but DbUtils been proven to be much
>> faster in production systems than JPA and that's what really matters to me
>> at least. If you can provision 3x more VMs for the same task then knock
>> yourself out.
>>
>> Sorry to annoy you and hopefully you will find a JPA solution for
>> Prasenjit.
>>
>>
> --
> Andy Gumbrecht
> https://twitter.com/AndyGeeDe
> http://www.tomitribe.com
> https://www.tomitribe.io
>
>
--
Steven P. Goldsmith
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by Andy Gumbrecht <ag...@tomitribe.com>.
Yep, Steve, I'm sorry if I was short and I didn't look closely -
Intention is not to start an argument, just to focus on the JPA solution
for Prasenjit.
Thanks,
Andy.
On 02/11/17 16:16, Steve Goldsmith wrote:
> The issues is performance and if this is a one off batch insert then I'm
> offering a solution. Alternatives are good and yes as I described before I
> survived fine without all the JPA "features" with a production application
> handling millions of transactions a day. This isn't theoretical and it
> works in the real world.
>
> I'm not sure why you think there's no transactions. Caching is simple using
> JCache (my app did this as well) which is agnostic to the persistence
> layer. If you think DbUtils adds 256% over pure JDBC then you really have
> not used it before or done any testing with it. Most JDBC wrappers add very
> little latency as most of that is happening in the database. In essence you
> are writing about the same lines of code as well.
>
> Maybe you didn't take a close look at the tests, but it throws out the
> first run to account for any initialization/lazy optimizations in JPA.
> Again, you may not find it useful, but DbUtils been proven to be much
> faster in production systems than JPA and that's what really matters to me
> at least. If you can provision 3x more VMs for the same task then knock
> yourself out.
>
> Sorry to annoy you and hopefully you will find a JPA solution for Prasenjit.
>
--
Andy Gumbrecht
https://twitter.com/AndyGeeDe
http://www.tomitribe.com
https://www.tomitribe.io
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by Steve Goldsmith <sg...@gmail.com>.
I mentioned the database latency in my previous post. My production app was
based on Oracle 11 and I saw similar insert performance. The usage scenario
definitely comes into play on deciding which technology to use. 15ms is
pretty good considering network latency is usually more than that unless
everything is on the same virtual network. Since we had servers all over
the US it could vary from 20ms to 100ms depending on the number of hops,
etc.
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by Paul Carter-Brown <pa...@smilecoms.com>.
Hi ,
Steve, I think what Andy is saying is that your results are mainly looking
at the overhead of the library itself (you are persisting to an in-memory
DB). In real world apps the overhead of the persistence library layer
compared to end-to-end latency of network, DB, logic etc etc would make
this difference much much smaller. As an example take this program and the
conclusion one could draw from it:
public static void main(String[] args) {
long start, end, noslflatency, withslflatency;
start = System.currentTimeMillis();
for (long i = 0; i < 10000000000L; i++) {
}
end = System.currentTimeMillis();
noslflatency = end - start;
start = System.currentTimeMillis();
for (long i = 0; i < 10000000000L; i++) {
log.debug("Here is a log line with a single parameter {}", i);
}
end = System.currentTimeMillis();
withslflatency = end - start;
long fasterPercent = (withslflatency -
noslflatency)*100/noslflatency;
System.out.println("Using SLF4J is " + fasterPercent + "% slower
than not using it even when no logging happens");
}
*My Result: Using SLF4J is 51% slower than not using it even when no
logging happens*
I'm sure you would agree that in a real world program, adding logging that
does not log does not make it 51% slower...
As for me: I use JPA where it adds value/speed to the development cycle and
where performance is paramount I use native queries. Between them, we are
doing over 100M transactions a day on TomEE with response times on SOAP
services of about 15ms avg. TomEE and JPA can perform no problem.
But back to the original question - the issue must surely be a config issue
and if the poster could provide more details then I'm sure we can help.
Paul
On 2 November 2017 at 17:18, Romain Manni-Bucau <rm...@gmail.com>
wrote:
> @Steve: question is not why JPA is slow but why a server is slower
> than another one (which leads to a misconfiguration).
> DBUnit/Hibernate-stateless etc can be good elsewhere but not sure it
> helps here :(
>
> Romain Manni-Bucau
> @rmannibucau | Blog | Old Blog | Github | LinkedIn
>
>
> 2017-11-02 16:16 GMT+01:00 Steve Goldsmith <sg...@gmail.com>:
> > The issues is performance and if this is a one off batch insert then I'm
> > offering a solution. Alternatives are good and yes as I described before
> I
> > survived fine without all the JPA "features" with a production
> application
> > handling millions of transactions a day. This isn't theoretical and it
> > works in the real world.
> >
> > I'm not sure why you think there's no transactions. Caching is simple
> using
> > JCache (my app did this as well) which is agnostic to the persistence
> > layer. If you think DbUtils adds 256% over pure JDBC then you really have
> > not used it before or done any testing with it. Most JDBC wrappers add
> very
> > little latency as most of that is happening in the database. In essence
> you
> > are writing about the same lines of code as well.
> >
> > Maybe you didn't take a close look at the tests, but it throws out the
> > first run to account for any initialization/lazy optimizations in JPA.
> > Again, you may not find it useful, but DbUtils been proven to be much
> > faster in production systems than JPA and that's what really matters to
> me
> > at least. If you can provision 3x more VMs for the same task then knock
> > yourself out.
> >
> > Sorry to annoy you and hopefully you will find a JPA solution for
> Prasenjit.
>
--
This email is subject to the disclaimer of Smile Communications at http://www.smilecoms.com/home/email-disclaimer/ <http://www.smilecoms.com/disclaimer>
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by Romain Manni-Bucau <rm...@gmail.com>.
@Steve: question is not why JPA is slow but why a server is slower
than another one (which leads to a misconfiguration).
DBUnit/Hibernate-stateless etc can be good elsewhere but not sure it
helps here :(
Romain Manni-Bucau
@rmannibucau | Blog | Old Blog | Github | LinkedIn
2017-11-02 16:16 GMT+01:00 Steve Goldsmith <sg...@gmail.com>:
> The issues is performance and if this is a one off batch insert then I'm
> offering a solution. Alternatives are good and yes as I described before I
> survived fine without all the JPA "features" with a production application
> handling millions of transactions a day. This isn't theoretical and it
> works in the real world.
>
> I'm not sure why you think there's no transactions. Caching is simple using
> JCache (my app did this as well) which is agnostic to the persistence
> layer. If you think DbUtils adds 256% over pure JDBC then you really have
> not used it before or done any testing with it. Most JDBC wrappers add very
> little latency as most of that is happening in the database. In essence you
> are writing about the same lines of code as well.
>
> Maybe you didn't take a close look at the tests, but it throws out the
> first run to account for any initialization/lazy optimizations in JPA.
> Again, you may not find it useful, but DbUtils been proven to be much
> faster in production systems than JPA and that's what really matters to me
> at least. If you can provision 3x more VMs for the same task then knock
> yourself out.
>
> Sorry to annoy you and hopefully you will find a JPA solution for Prasenjit.
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by Steve Goldsmith <sg...@gmail.com>.
The issues is performance and if this is a one off batch insert then I'm
offering a solution. Alternatives are good and yes as I described before I
survived fine without all the JPA "features" with a production application
handling millions of transactions a day. This isn't theoretical and it
works in the real world.
I'm not sure why you think there's no transactions. Caching is simple using
JCache (my app did this as well) which is agnostic to the persistence
layer. If you think DbUtils adds 256% over pure JDBC then you really have
not used it before or done any testing with it. Most JDBC wrappers add very
little latency as most of that is happening in the database. In essence you
are writing about the same lines of code as well.
Maybe you didn't take a close look at the tests, but it throws out the
first run to account for any initialization/lazy optimizations in JPA.
Again, you may not find it useful, but DbUtils been proven to be much
faster in production systems than JPA and that's what really matters to me
at least. If you can provision 3x more VMs for the same task then knock
yourself out.
Sorry to annoy you and hopefully you will find a JPA solution for Prasenjit.
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by Andy Gumbrecht <ag...@tomitribe.com>.
sgjava, I'm not sure why you would throw that in when trying to solve a
completely different problem?
Sure, DButils has it's uses and I'd not argue against using it if
someone prefers to use it, but that doesn't really help the issue here.
My favorite blog on the 'vs' topic is here:
https://techblog.bozho.net/orm-haters-dont-get-it/
What you are really saying is - "If you can live without tonnes and
tonnes and tonnes of JPA features then ..."
If we all went back to the good old days of JDBC then I could really get
some figures like 256% faster than DBUtils ;-)
Sorry if that comes across as a bit 'Mr Annoyed', but there's not any
kind of useful stats in the links provided. There's schema generation
going on, no transactions, no modelling , no caching and a whole bunch
of other bias going on in your tests that make those figures less than
useful.
Rant over,
Andy.
On 02/11/17 14:48, sgjava wrote:
> Take a look at https://github.com/sgjava/jpavsdbutils. It compares JPA to
> DbUtils. If you can live without some of the JPA features then DbUtils may
> work faster for you especially for batch inserts.
>
>
>
> --
> Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
>
--
Andy Gumbrecht
https://twitter.com/AndyGeeDe
http://www.tomitribe.com
https://www.tomitribe.io
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by sgjava <sg...@gmail.com>.
Take a look at https://github.com/sgjava/jpavsdbutils. It compares JPA to
DbUtils. If you can live without some of the JPA features then DbUtils may
work faster for you especially for batch inserts.
--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by Jonathan Gallimore <jo...@gmail.com>.
Do you have some sample code and a datasource config (minus anything
sensitive) we could use to test it out and see where the issue is?
Thanks
Jon
On Thu, Nov 2, 2017 at 11:13 AM, Romain Manni-Bucau <rm...@gmail.com>
wrote:
> Hi
>
> did you check your datasource pool configuration (you can through JMX
> in openejb mbeans)?
>
>
> Romain Manni-Bucau
> @rmannibucau | Blog | Old Blog | Github | LinkedIn
>
>
> 2017-11-02 6:35 GMT+01:00 pmo_tomee <ma...@gmail.com>:
> > Hi Team,
> >
> > I am using JPA and Hibernate-5.2.8 (as persistence provider) in TomEE.
> > Using Oracle 10g DB.
> >
> > I tested to insert 400 rows in TomEE and JONAS server. TomEE taking 20
> sec
> > but jonas taking only 4 sec.
> >
> > I have tested in TomEE with batch insert by <property
> > name="hibernate.jdbc.batch_size" value="50" /> but taking 20 sec aslo.
> >
> > I have checked the log in Oracle for both server given below.
> >
> > *JONAS server*
> > --------------------
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 1 0.00 0.00 0 0 0
> > 0
> > Execute 26 0.11 0.16 10 20 7331
> > 380
> > Fetch 0 0.00 0.00 0 0 0
> > 0
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 27 0.11 0.16 10 20 7331
> > 380
> >
> > *Here JONAS inserting 380 rows by 26 Executions*
> >
> > ------------------------------------------------------------
> ------------------------
> > TomEE server
> > -------------------------
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 380 0.01 0.01 0 0 0
> > 0
> > Execute 380 1.26 1.28 9 393 6218
> > 380
> > Fetch 0 0.00 0.00 0 0 0
> > 0
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 760 1.27 1.30 9 393 6218
> > 380
> >
> > *Here TomEE inserting 380 rows by 380 executions*
> >
> > Could you help me why TomEE taking 20 sec to insert.
> >
> > Is there any way to fixed it ?
> >
> > Thanks & Regards
> > Prasenjit Mahato
> > mahato1987@gmail.com
> >
> >
> >
> > --
> > Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-
> f979441.html
>
Re: SQL insert taking too much time in TomEE-7.0.1
Posted by Romain Manni-Bucau <rm...@gmail.com>.
Hi
did you check your datasource pool configuration (you can through JMX
in openejb mbeans)?
Romain Manni-Bucau
@rmannibucau | Blog | Old Blog | Github | LinkedIn
2017-11-02 6:35 GMT+01:00 pmo_tomee <ma...@gmail.com>:
> Hi Team,
>
> I am using JPA and Hibernate-5.2.8 (as persistence provider) in TomEE.
> Using Oracle 10g DB.
>
> I tested to insert 400 rows in TomEE and JONAS server. TomEE taking 20 sec
> but jonas taking only 4 sec.
>
> I have tested in TomEE with batch insert by <property
> name="hibernate.jdbc.batch_size" value="50" /> but taking 20 sec aslo.
>
> I have checked the log in Oracle for both server given below.
>
> *JONAS server*
> --------------------
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 26 0.11 0.16 10 20 7331
> 380
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 27 0.11 0.16 10 20 7331
> 380
>
> *Here JONAS inserting 380 rows by 26 Executions*
>
> ------------------------------------------------------------------------------------
> TomEE server
> -------------------------
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 380 0.01 0.01 0 0 0
> 0
> Execute 380 1.26 1.28 9 393 6218
> 380
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 760 1.27 1.30 9 393 6218
> 380
>
> *Here TomEE inserting 380 rows by 380 executions*
>
> Could you help me why TomEE taking 20 sec to insert.
>
> Is there any way to fixed it ?
>
> Thanks & Regards
> Prasenjit Mahato
> mahato1987@gmail.com
>
>
>
> --
> Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html