Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #32966 > unrolled thread

Performance JPA

Back to article view | Back to comp.lang.java.programmer

Contents

  Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-02 09:47 +0100
    Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-02 14:23 +0100
      Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-03 07:52 +0100
        Re: Performance JPA Martin Gregorie <martin@address-in-sig.invalid> - 2015-11-03 11:16 +0000
          Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-03 19:11 +0100
            Re: Performance JPA Martin Gregorie <martin@address-in-sig.invalid> - 2015-11-03 19:15 +0000
              Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-04 09:28 +0100
                Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-10 08:07 +0100
                  Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-10 10:35 +0100
                    Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-11 06:20 +0100
                      Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-11 09:32 -0500
                  Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-10 19:56 -0500
                    Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-11 06:50 +0100
                      Re: Performance JPA Martin Gregorie <martin@address-in-sig.invalid> - 2015-11-11 12:17 +0000
                      Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-11 09:30 -0500
                        Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-11 23:20 +0100
                    Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-11 23:11 +0100
                      Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-11 19:10 -0500
                        Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-12 07:27 +0100
                          Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-13 08:30 +0100
                            Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-13 10:45 +0100
                              Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-13 11:06 +0100
                                Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-13 13:36 +0100
                                  Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-20 09:04 +0100
                            Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-13 21:46 -0500
                              Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-20 09:09 +0100
            Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-03 20:35 -0500
              Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-04 09:35 +0100
                Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-07 22:09 -0500
        Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-03 12:29 +0100
          Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-03 19:23 +0100
          Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-03 20:44 -0500
            Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-04 09:48 +0100
            Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-04 10:23 +0100
              Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-04 11:27 +0100
                Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-04 12:42 +0100
                  Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-07 22:18 -0500
                    Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-09 10:43 +0100
                      Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-20 09:13 +0100
              Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-07 22:12 -0500
                Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-09 10:22 +0100
                  Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-10 21:35 -0500
                    Re: Performance JPA Silvio <silvio@internet.com> - 2015-11-11 11:29 +0100
        Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-03 20:42 -0500
          Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-04 11:19 +0100
            Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-07 22:05 -0500
    Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-02 20:08 -0500
      Re: Performance JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-03 07:55 +0100
        Re: Performance JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-03 20:45 -0500
    Re: Performance JPA Lew <lewbloch@gmail.com> - 2015-11-16 14:43 -0800

Page 1 of 3  [1] 2 3  Next page →

#32966 — Performance JPA

We needed to change from JDBC to JPA. It looks like the performance is
10 to 20 times as slow as it was with JDBC. I could understand JPA to
be slower as JDBC, but not with this kind of difference. Are there any
common error that would explain this kind of penalty?

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [next] | [standalone]

#32969

On 11/02/2015 09:47 AM, Cecil Westerhof wrote:
> We needed to change from JDBC to JPA. It looks like the performance is
> 10 to 20 times as slow as it was with JDBC. I could understand JPA to
> be slower as JDBC, but not with this kind of difference. Are there any
> common error that would explain this kind of penalty?
>

I remember you came to us here a couple of months ago with the intent of 
moving an application from JDBC to JPA. You gave us little to go on for 
advice. I also remember telling you this day would probably come...

But let's put that aside and see if we can fix your situation. Please 
tell us what the typical database access from your application looks 
like and which things are particularly slow in comparison to how it was?

JDBC and JPA are incomparable. JDBC is an API to execute SQL statements 
against an RDBMS (with some very limited added abstractions to 
standardize access to meta-data). JPA is an object persistence 
implementation that uses an RDBMS for storage (via JDBC, btw). It has 
some quirky options to execute raw SQL but that is just plain stupid.

UNLESS you where already doing the second (persisting objects) manually 
using JDBC, the performance hit after switching to JPA is inevitable. 
Database access can be very quick, using JDBC or any other library.

But having an object persistence layer sitting between your application 
and the database can make thing stupendously inefficient for one 
application and it can also be a nice match with little added overhead 
for the other. It just depends on what your application is.

Please feel free to elaborate...

[toc] | [prev] | [next] | [standalone]

#32980

On Monday  2 Nov 2015 14:23 CET, silvio@internet.com wrote:

> On 11/02/2015 09:47 AM, Cecil Westerhof wrote:
>> We needed to change from JDBC to JPA. It looks like the performance
>> is 10 to 20 times as slow as it was with JDBC. I could understand
>> JPA to be slower as JDBC, but not with this kind of difference. Are
>> there any common error that would explain this kind of penalty?
>>
>
> I remember you came to us here a couple of months ago with the
> intent of moving an application from JDBC to JPA. You gave us little
> to go on for advice. I also remember telling you this day would
> probably come...
>
> But let's put that aside and see if we can fix your situation.
> Please tell us what the typical database access from your
> application looks like and which things are particularly slow in
> comparison to how it was?

At the moment I am told that everything is slower. I am told that a
query that took at most 5 seconds now takes one to two minutes. But I
asked them to measure the old situation and until now they did not do
this. I hope to get today real data about this. But several pages now
give a timeout.


> JDBC and JPA are incomparable. JDBC is an API to execute SQL
> statements against an RDBMS (with some very limited added
> abstractions to standardize access to meta-data). JPA is an object
> persistence implementation that uses an RDBMS for storage (via JDBC,
> btw). It has some quirky options to execute raw SQL but that is just
> plain stupid.
>
> UNLESS you where already doing the second (persisting objects)
> manually using JDBC, the performance hit after switching to JPA is
> inevitable. Database access can be very quick, using JDBC or any
> other library.

OK, JDBC was just using rowmapper and copying the fields. No
persistence at all. So maybe the difference is less strange as I
thought.


> But having an object persistence layer sitting between your
> application and the database can make thing stupendously inefficient
> for one application and it can also be a nice match with little
> added overhead for the other. It just depends on what your
> application is.
>
> Please feel free to elaborate...

As I understood it until now, there should be a little overhead, but
it can be worse I understand now. As said in the above rowmapper was
used. Now the application uses em.find to read. For displaying a page
only reads are done, so using em.find instead rs.get... seems to make
a very big difference. Is there a reason for this and can there be
something done about this?

By the way we use a generic function because a lot of classes are
extended. We use:
    public T read(K id) {
        return em.find(entityClass, id);
    }

We fill entityClass in the constructor with:
    entityClass = (Class<t>) genericSuperClass.getActualTypeArguments()[0];

In the create, update and delete functions we use an em.flush beside
the em.persist, em.merge and em.remove.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#32982

On Tue, 03 Nov 2015 07:52:46 +0100, Cecil Westerhof wrote:

> At the moment I am told that everything is slower. I am told that a
> query that took at most 5 seconds now takes one to two minutes. But I
> asked them to measure the old situation and until now they did not do
> this. I hope to get today real data about this. But several pages now
> give a timeout.
>
Usually slow retrievals mean that the DBMS is sequentially scanning 
tables rather than using indexes, so the obvious starting point is to see 
how the JPA queries differ from the ones used by JDBC and, if the 
difference can't be found by inspection, to use the DBMS query cost 
analysis facilities to run the queries manually and report execution plan 
used for them.

Typically the poor performance is that joins, WHERE or ORDER BY clauses 
on large tables are not supported by indexes: the solution is to add 
these indexes to the schema if the query is run frequently enough to 
justify doing so. 

Bad performance can also be the result of poor DB management: most DBMS 
use DB statistics (row counts, etc) as input to the query optimiser. If 
these stats aren't automatically updated by the DBMS and the DBA doesn't 
update them regularly, or turned auto-stats update off then the optimiser 
can do a spectacularly poor job, e.g. if stats say a 100,000 table has 
only 10 rows then most optimisers would scan it sequentially because a 
sequential scan on a table that fits in one disk block will always be 
quicker than indexed access.

Check whether the JPA has debugging/tracing facilities that can be used 
to show the queries that are being run and and cost or performance data 
for them.

Welcome to the world of RDBMS tuning: I hope your DBA is up to speed on 
this passtime!


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#32985

On Tuesday  3 Nov 2015 12:16 CET, Martin Gregorie wrote:

> On Tue, 03 Nov 2015 07:52:46 +0100, Cecil Westerhof wrote:
>
>> At the moment I am told that everything is slower. I am told that a
>> query that took at most 5 seconds now takes one to two minutes. But
>> I asked them to measure the old situation and until now they did
>> not do this. I hope to get today real data about this. But several
>> pages now give a timeout.
>>
> Usually slow retrievals mean that the DBMS is sequentially scanning
> tables rather than using indexes, so the obvious starting point is
> to see how the JPA queries differ from the ones used by JDBC and, if
> the difference can't be found by inspection, to use the DBMS query
> cost analysis facilities to run the queries manually and report
> execution plan used for them.

In principal the JPA should do about the same queries as the JDBC. I
am busy trying to get the logging of the queries at the database side
enabled. I hope I will succeed.

One remark is that I should use the unit test to look which statements
are generated. I have a hard time to convince them that a unit test is
something else as a live run.


> Typically the poor performance is that joins, WHERE or ORDER BY
> clauses on large tables are not supported by indexes: the solution
> is to add these indexes to the schema if the query is run frequently
> enough to justify doing so.
>
> Bad performance can also be the result of poor DB management: most
> DBMS use DB statistics (row counts, etc) as input to the query
> optimiser. If these stats aren't automatically updated by the DBMS
> and the DBA doesn't update them regularly, or turned auto-stats
> update off then the optimiser can do a spectacularly poor job, e.g.
> if stats say a 100,000 table has only 10 rows then most optimisers
> would scan it sequentially because a sequential scan on a table that
> fits in one disk block will always be quicker than indexed access.

But that should go wrong with JDBC and JPA I would think.


> Welcome to the world of RDBMS tuning: I hope your DBA is up to speed
> on this passtime!

I will need ‘some’ skills in convincing I am afraid. :'-(

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#32988

On Tue, 03 Nov 2015 19:11:48 +0100, Cecil Westerhof wrote:

>> Bad performance can also be the result of poor DB management: most DBMS
>> use DB statistics (row counts, etc) as input to the query optimiser. If
>> these stats aren't automatically updated by the DBMS and the DBA
>> doesn't update them regularly, or turned auto-stats update off then the
>> optimiser can do a spectacularly poor job, e.g. if stats say a 100,000
>> table has only 10 rows then most optimisers would scan it sequentially
>> because a sequential scan on a table that fits in one disk block will
>> always be quicker than indexed access.
> 
> But that should go wrong with JDBC and JPA I would think.
>
True, but are you running JPA against the original database instance that 
JDBC used to use, or has the data been copied into a clean development 
instance?

I would hope the latter is the case because performance comparisons are 
invalid unless the data volumes are the same for both old and new code 
but it would be quite unwise to be running code under system test against 
the main database. If the data has been copied, then you need to be sure 
that the DB stats on the DB copy are up to date, which is what I was on 
about. 

You also need to be certain that the schemas are identical: it only needs 
tweaks to have been applied to the old database during a late night 
emergency fix, i.e. indices added to tables or the physical placement and/
or partitioning of database components changed, and for the fixer to 
forget to or not be given time to record the changes in the development 
schema and documentation for this sort of error to creep into the system.

The worst part is that nobody will know this happened until the 
organisation needs to build a new integration test system after a major 
upgrade because its only then, when a clean instance has been set up and 
loaded with data from the old system that unexpected performance 
differences or outputs show up. Finding and fixing this sort of change 
can be a real headache.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#32995

On Tuesday  3 Nov 2015 20:15 CET, Martin Gregorie wrote:

> On Tue, 03 Nov 2015 19:11:48 +0100, Cecil Westerhof wrote:
>
>>> Bad performance can also be the result of poor DB management: most
>>> DBMS use DB statistics (row counts, etc) as input to the query
>>> optimiser. If these stats aren't automatically updated by the DBMS
>>> and the DBA doesn't update them regularly, or turned auto-stats
>>> update off then the optimiser can do a spectacularly poor job,
>>> e.g. if stats say a 100,000 table has only 10 rows then most
>>> optimisers would scan it sequentially because a sequential scan on
>>> a table that fits in one disk block will always be quicker than
>>> indexed access.
>>
>> But that should go wrong with JDBC and JPA I would think.
>>
> True, but are you running JPA against the original database instance
> that JDBC used to use, or has the data been copied into a clean
> development instance?

No. Until now it where two completely different environments. I at
last managed to get them to test on the same test database. I should
soon get more trustworthy measurements.


> I would hope the latter is the case because performance comparisons
> are invalid unless the data volumes are the same for both old and
> new code but it would be quite unwise to be running code under
> system test against the main database. If the data has been copied,
> then you need to be sure that the DB stats on the DB copy are up to
> date, which is what I was on about.
>
> You also need to be certain that the schemas are identical: it only
> needs tweaks to have been applied to the old database during a late
> night emergency fix, i.e. indices added to tables or the physical
> placement and/ or partitioning of database components changed, and
> for the fixer to forget to or not be given time to record the
> changes in the development schema and documentation for this sort of
> error to creep into the system.
>
> The worst part is that nobody will know this happened until the
> organisation needs to build a new integration test system after a
> major upgrade because its only then, when a clean instance has been
> set up and loaded with data from the old system that unexpected
> performance differences or outputs show up. Finding and fixing this
> sort of change can be a real headache.

In a way I am glad this happened. It proves the importance of really
good tests.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#33070

On Wednesday  4 Nov 2015 09:28 CET, Cecil Westerhof wrote:

> On Tuesday  3 Nov 2015 20:15 CET, Martin Gregorie wrote:
>
>> On Tue, 03 Nov 2015 19:11:48 +0100, Cecil Westerhof wrote:
>>
>>>> Bad performance can also be the result of poor DB management:
>>>> most DBMS use DB statistics (row counts, etc) as input to the
>>>> query optimiser. If these stats aren't automatically updated by
>>>> the DBMS and the DBA doesn't update them regularly, or turned
>>>> auto-stats update off then the optimiser can do a spectacularly
>>>> poor job, e.g. if stats say a 100,000 table has only 10 rows then
>>>> most optimisers would scan it sequentially because a sequential
>>>> scan on a table that fits in one disk block will always be
>>>> quicker than indexed access.
>>>
>>> But that should go wrong with JDBC and JPA I would think.
>>>
>> True, but are you running JPA against the original database
>> instance that JDBC used to use, or has the data been copied into a
>> clean development instance?
>
> No. Until now it where two completely different environments. I at
> last managed to get them to test on the same test database. I should
> soon get more trustworthy measurements.

I got some progress. Last week the old and new version where run in
the same environment/database. The performance problem still stood. So
there is certainly a problem with the new program. Because several
things have changed I am not sure what would be the problem, but the
rest is convinced it is a JPA problem.

In principal database logging is going to be enabled, but I am waiting
already a week for this.

Yesterday I was told that the queries themselves do not take a lot of
time. In my opinion it could then be one of the other changes. But I
am told that JPA does a lot of things beside retrieving things out of
the database and that those other things JPA is doing are the problem.
Sounds far-fetched to me, but this is the first time I am working with
JPA. Could they be right? If so: how do we pinpoint this?


I think it is time for profiling. As long as we are not sure what the
problem is, it is impossible to solve it. Until now I never had to
profile in Java. We work with Eclipse Kepler and IBM WebSpere
Application Server v8.0. What are good resources to learn to profile
the code?

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#33072

On 11/10/2015 08:07 AM, Cecil Westerhof wrote:
>
> I got some progress. Last week the old and new version where run in
> the same environment/database. The performance problem still stood. So
> there is certainly a problem with the new program. Because several
> things have changed I am not sure what would be the problem, but the
> rest is convinced it is a JPA problem.
>
> In principal database logging is going to be enabled, but I am waiting
> already a week for this.
>
> Yesterday I was told that the queries themselves do not take a lot of
> time. In my opinion it could then be one of the other changes. But I
> am told that JPA does a lot of things beside retrieving things out of
> the database and that those other things JPA is doing are the problem.
> Sounds far-fetched to me, but this is the first time I am working with
> JPA. Could they be right? If so: how do we pinpoint this?
>
>
> I think it is time for profiling. As long as we are not sure what the
> problem is, it is impossible to solve it. Until now I never had to
> profile in Java. We work with Eclipse Kepler and IBM WebSpere
> Application Server v8.0. What are good resources to learn to profile
> the code?
>

Hello Cecil,

Have you already looked into stuff like eager vs. lazy fetching? I have 
come across lots of performance issues caused by eager fetching.

Cheers,

Silvio

[toc] | [prev] | [next] | [standalone]

#33098

On Tuesday 10 Nov 2015 10:35 CET, silvio@internet.com wrote:

> On 11/10/2015 08:07 AM, Cecil Westerhof wrote:
>>
>> I got some progress. Last week the old and new version where run in
>> the same environment/database. The performance problem still stood.
>> So there is certainly a problem with the new program. Because
>> several things have changed I am not sure what would be the
>> problem, but the rest is convinced it is a JPA problem.
>>
>> In principal database logging is going to be enabled, but I am
>> waiting already a week for this.
>>
>> Yesterday I was told that the queries themselves do not take a lot
>> of time. In my opinion it could then be one of the other changes.
>> But I am told that JPA does a lot of things beside retrieving
>> things out of the database and that those other things JPA is doing
>> are the problem. Sounds far-fetched to me, but this is the first
>> time I am working with JPA. Could they be right? If so: how do we
>> pinpoint this?
>>
>>
>> I think it is time for profiling. As long as we are not sure what
>> the problem is, it is impossible to solve it. Until now I never had
>> to profile in Java. We work with Eclipse Kepler and IBM WebSpere
>> Application Server v8.0. What are good resources to learn to
>> profile the code?
>>
>
> Hello Cecil,
>
> Have you already looked into stuff like eager vs. lazy fetching? I
> have come across lots of performance issues caused by eager
> fetching.

That is only important with relations, or not? Those are mot used. At
list not on JPA level. There are some relations, but without link
tables. So they are fetched manually. In JDBC and in JPA. In principal
it is not possible to change the database, but I will try to get the
link tables implemented so we can implement relations.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#33107

On 11/11/2015 12:20 AM, Cecil Westerhof wrote:
> On Tuesday 10 Nov 2015 10:35 CET, silvio@internet.com wrote:
>> Have you already looked into stuff like eager vs. lazy fetching? I
>> have come across lots of performance issues caused by eager
>> fetching.
>
> That is only important with relations, or not? Those are mot used. At
> list not on JPA level.

So not the cause of the performance problem, but not getting all
benefits from JPA either.

Arne

[toc] | [prev] | [next] | [standalone]

#33086

On 11/10/2015 2:07 AM, Cecil Westerhof wrote:
> On Wednesday  4 Nov 2015 09:28 CET, Cecil Westerhof wrote:
>> No. Until now it where two completely different environments. I at
>> last managed to get them to test on the same test database. I should
>> soon get more trustworthy measurements.
>
> I got some progress. Last week the old and new version where run in
> the same environment/database. The performance problem still stood. So
> there is certainly a problem with the new program. Because several
> things have changed I am not sure what would be the problem, but the
> rest is convinced it is a JPA problem.

It is very likely that performance problems in a database application
relates to its usage of database, so it makes sense to focus
troubleshooting on that.

> In principal database logging is going to be enabled, but I am waiting
> already a week for this.

If you enable logging of the generated SQL then you can start moving
on instead of waiting for somebody else.

> Yesterday I was told that the queries themselves do not take a lot of
> time.

If that is true, which it may not be, then that points to not
being database.

>                                                               But I
> am told that JPA does a lot of things beside retrieving things out of
> the database and that those other things JPA is doing are the problem.
> Sounds far-fetched to me, but this is the first time I am working with
> JPA. Could they be right? If so: how do we pinpoint this?

JPA does not do things randomly. It does what you tell it to do.

Do you have any potential complications:
* relations that pull in many extra rows without enabling lazy loading
* relations that convert single object update into many row updates

Most of these problems are easily found during developer test
when enabling logging of generated SQL.

> I think it is time for profiling. As long as we are not sure what the
> problem is, it is impossible to solve it. Until now I never had to
> profile in Java. We work with Eclipse Kepler and IBM WebSpere
> Application Server v8.0. What are good resources to learn to profile
> the code?

I would start by getting that logging enabled.

Arne

[toc] | [prev] | [next] | [standalone]

#33099

On Wednesday 11 Nov 2015 01:56 CET, Arne Vajhøj wrote:

> On 11/10/2015 2:07 AM, Cecil Westerhof wrote:
>> On Wednesday  4 Nov 2015 09:28 CET, Cecil Westerhof wrote:
>>> No. Until now it where two completely different environments. I at
>>> last managed to get them to test on the same test database. I
>>> should soon get more trustworthy measurements.
>>
>> I got some progress. Last week the old and new version where run in
>> the same environment/database. The performance problem still stood.
>> So there is certainly a problem with the new program. Because
>> several things have changed I am not sure what would be the
>> problem, but the rest is convinced it is a JPA problem.
>
> It is very likely that performance problems in a database
> application relates to its usage of database, so it makes sense to
> focus troubleshooting on that.
>
>> In principal database logging is going to be enabled, but I am
>> waiting already a week for this.
>
> If you enable logging of the generated SQL then you can start moving
> on instead of waiting for somebody else.

The problem was that logging did not work. But I understood that they
solved the problem with that. Until know I was told to look at the
logging of the unit tests. I do not think that you can be sure that
unit tests will give an accurate representation of live behaviour.


>> Yesterday I was told that the queries themselves do not take a lot
>> of time.
>
> If that is true, which it may not be, then that points to not
> being database.

I found that even if they find it annoying I have to check and recheck
what I am told. For example the what I was told about the timings was
not correct. I was told 5 seconds with JDBC and 2 minutes with JPA.
That turned out to be 1 second and 19 seconds. Less of a difference,
but still way of.


>> But I am told that JPA does a lot of things beside retrieving
>> things out of the database and that those other things JPA is doing
>> are the problem. Sounds far-fetched to me, but this is the first
>> time I am working with JPA. Could they be right? If so: how do we
>> pinpoint this?
>
> JPA does not do things randomly. It does what you tell it to do.

That is what I would think. But this is the first time I work with
JPA, so I thought I should check if it would be possible that what
they are saying could be true.


> Do you have any potential complications: * relations that pull in
> many extra rows without enabling lazy loading * relations that
> convert single object update into many row updates

Not directly, no. Here is the strange part. There are some relations,
but they are manually fetched. In JDBC and JPA in the same way. So on
the one hand that should not be a problem. But on the other hand when
those fetches are disabled the JPA application is a lot faster.

Today the person who found this problem should be at the company. I
hope that face to face communication will go better as through instant
message.

As I understand it, there are several queries that are not necessary
and the other ones are quit inefficient. So one solution would be to
remove the unnecessary queries (after first identifying them) and then
rewriting the rest. That would probably solve the problems. And it
would be a good idea to do this. There is only one problem with this:
we do not pinpoint why we have such a big performance difference
between JDBC and JPA while we think we are doing the same. From an
academic point I like to understand why things go differently, but
even more important: there are about 20 to 40 other projects that also
need to be migrated: so if we could pinpoint the problem, we would
prevent the same troubles at the other migrations.

One change I managed to arrange: with the other migrations every week
some testing will be done instead of waiting till the migration is
‘finished’. That should circumvent the quagmire we got into now.


> Most of these problems are easily found during developer test
> when enabling logging of generated SQL.
>
>> I think it is time for profiling. As long as we are not sure what
>> the problem is, it is impossible to solve it. Until now I never had
>> to profile in Java. We work with Eclipse Kepler and IBM WebSpere
>> Application Server v8.0. What are good resources to learn to
>> profile the code?
>
> I would start by getting that logging enabled.

There was a problem with that, but I was yesterday notified that this
problem was solved.


Something else: I do not think it could be a problem, but better save
as sorry. I read somewhere that using @EmbeddedId could result in a
performance hit. Is that true? If so what could be the reason for
this? Because there are quit a few entities that use @EmbeddedId.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#33104

On Wed, 11 Nov 2015 06:50:02 +0100, Cecil Westerhof wrote:

> On Wednesday 11 Nov 2015 01:56 CET, Arne Vajhøj wrote:
> 
>> On 11/10/2015 2:07 AM, Cecil Westerhof wrote:
>>> In principal database logging is going to be enabled, but I am waiting
>>> already a week for this.
>>
>> If you enable logging of the generated SQL then you can start moving on
>> instead of waiting for somebody else.
>
Quite - and you can run the queries manually with the SQL execution tool, 
getting timings that you trust. Many of these will time the queries. If 
they won't, you can always run something like:

    time pgsql -c "select * from table-a order by key" testdb username

using the equivalent commands for your OS and RDBMS (this example uses 
PostgreSQL under Linux). 

Another benefit is that, when you've found the real dogs, you can make 
the SQL tool show you the query execution plan (for Postgres you'd run
"explain select * from table-a order by key").

Final point: if you capture the SQL queries that are actually executed 
during an acceptance test style session you should be able to list the 
queries in order of use frequency. This lets you concentrate your effort 
on the most commonly used bad performers: nobody cares if a query that 
runs only once at end-of-year is slow, but everybody will complain if one 
that runs each time an order detail line is entered isn't instant. 


> The problem was that logging did not work. But I understood that they
> solved the problem with that. Until know I was told to look at the
> logging of the unit tests. I do not think that you can be sure that unit
> tests will give an accurate representation of live behaviour.
>
Quite. You need to run actual production queries against a production-
size database of the answers will be meaningless. 

> As I understand it, there are several queries that are not necessary and
> the other ones are quit inefficient. So one solution would be to remove
> the unnecessary queries (after first identifying them) and then
> rewriting the rest.
>
Application and/or query level logging would help here.



-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#33106

On 11/11/2015 12:50 AM, Cecil Westerhof wrote:
> On Wednesday 11 Nov 2015 01:56 CET, Arne Vajhøj wrote:
>> On 11/10/2015 2:07 AM, Cecil Westerhof wrote:
>>> In principal database logging is going to be enabled, but I am
>>> waiting already a week for this.
>>
>> If you enable logging of the generated SQL then you can start moving
>> on instead of waiting for somebody else.
>
> The problem was that logging did not work. But I understood that they
> solved the problem with that. Until know I was told to look at the
> logging of the unit tests. I do not think that you can be sure that
> unit tests will give an accurate representation of live behaviour.

If the problem really is JPA generating bad SQL then I would say that
there is a pretty big chance that you will spot it already in the unit
tests.

You are not looking for 10% fine tuning of SQL - you are looking
for something big causing a x10 difference.

Even if it did not come up with something definite then the
exercise would give you a much better foundation for continuing
the troubleshooting.

>>> Yesterday I was told that the queries themselves do not take a lot
>>> of time.
>>
>> If that is true, which it may not be, then that points to not
>> being database.
>
> I found that even if they find it annoying I have to check and recheck
> what I am told. For example the what I was told about the timings was
> not correct. I was told 5 seconds with JDBC and 2 minutes with JPA.
> That turned out to be 1 second and 19 seconds. Less of a difference,
> but still way of.

That sort of problems is unfortunately very common.

>> Do you have any potential complications: * relations that pull in
>> many extra rows without enabling lazy loading * relations that
>> convert single object update into many row updates
>
> Not directly, no. Here is the strange part. There are some relations,
> but they are manually fetched. In JDBC and JPA in the same way. So on
> the one hand that should not be a problem. But on the other hand when
> those fetches are disabled the JPA application is a lot faster.

I think you should pick one of these cases. Look at the SQL in the JDBC
version. And look at the generated SQL in the JPA version.

>>> I think it is time for profiling. As long as we are not sure what
>>> the problem is, it is impossible to solve it. Until now I never had
>>> to profile in Java. We work with Eclipse Kepler and IBM WebSpere
>>> Application Server v8.0. What are good resources to learn to
>>> profile the code?
>>
>> I would start by getting that logging enabled.
>
> There was a problem with that, but I was yesterday notified that this
> problem was solved.
>
> Something else: I do not think it could be a problem, but better save
> as sorry. I read somewhere that using @EmbeddedId could result in a
> performance hit. Is that true? If so what could be the reason for
> this? Because there are quit a few entities that use @EmbeddedId.

In very pure ORM then you would have a single synthetic PK field.

But the other should work fine as well.

No harm done in taking a quick look at the generated SQL though
to verify that it turns out OK.

Arne

[toc] | [prev] | [next] | [standalone]

#33110

On Wednesday 11 Nov 2015 15:30 CET, Arne Vajhøj wrote:

> On 11/11/2015 12:50 AM, Cecil Westerhof wrote:
>> On Wednesday 11 Nov 2015 01:56 CET, Arne Vajhøj wrote:
>>> On 11/10/2015 2:07 AM, Cecil Westerhof wrote:
>>>> In principal database logging is going to be enabled, but I am
>>>> waiting already a week for this.
>>>
>>> If you enable logging of the generated SQL then you can start
>>> moving on instead of waiting for somebody else.
>>
>> The problem was that logging did not work. But I understood that
>> they solved the problem with that. Until know I was told to look at
>> the logging of the unit tests. I do not think that you can be sure
>> that unit tests will give an accurate representation of live
>> behaviour.
>
> If the problem really is JPA generating bad SQL then I would say
> that there is a pretty big chance that you will spot it already in
> the unit tests.

That is not the problem. I have got some data and what is happening is
that JPA waits 4.5 seconds before it starts executing the database
query. See my other reply.

>> Something else: I do not think it could be a problem, but better
>> save as sorry. I read somewhere that using @EmbeddedId could result
>> in a performance hit. Is that true? If so what could be the reason
>> for this? Because there are quit a few entities that use
>> @EmbeddedId.
>
> In very pure ORM then you would have a single synthetic PK field.
>
> But the other should work fine as well.
>
> No harm done in taking a quick look at the generated SQL though
> to verify that it turns out OK.

That turned out not to be the problem. With @EmbeddeId and with @Id
there is a pause of 4.5 seconds before the query is executed. So at
the moment it looks like @EmbeddedId does not have a bearing on this.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#33108

On Wednesday 11 Nov 2015 01:56 CET, Arne Vajhøj wrote:

> On 11/10/2015 2:07 AM, Cecil Westerhof wrote:
>> On Wednesday  4 Nov 2015 09:28 CET, Cecil Westerhof wrote:
>>> No. Until now it where two completely different environments. I at
>>> last managed to get them to test on the same test database. I
>>> should soon get more trustworthy measurements.
>>
>> I got some progress. Last week the old and new version where run in
>> the same environment/database. The performance problem still stood.
>> So there is certainly a problem with the new program. Because
>> several things have changed I am not sure what would be the
>> problem, but the rest is convinced it is a JPA problem.
>
> It is very likely that performance problems in a database
> application relates to its usage of database, so it makes sense to
> focus troubleshooting on that.
>
>> In principal database logging is going to be enabled, but I am
>> waiting already a week for this.
>
> If you enable logging of the generated SQL then you can start moving
> on instead of waiting for somebody else.
>
>> Yesterday I was told that the queries themselves do not take a lot
>> of time.
>
> If that is true, which it may not be, then that points to not
> being database.
>
>> But I am told that JPA does a lot of things beside retrieving
>> things out of the database and that those other things JPA is doing
>> are the problem. Sounds far-fetched to me, but this is the first
>> time I am working with JPA. Could they be right? If so: how do we
>> pinpoint this?
>
> JPA does not do things randomly. It does what you tell it to do.

At last I have some useful metrics. It certainly looks like it that
JPA is the problem: JPA pauses 4.5 seconds before it starts executing
the datbase queries.

We use the following code:
    public T read(K id) {
        System.out.println("######################################## read enter");
        T entity = em.find(entityClass, id);
        System.out.println("######################################## read leave");
        return entity;
    }

and this results in:
    [11-11-15 15:13:53:590 CET] 000000ad SystemOut O ######################################## read enter
    [11-11-15 15:13:58:112 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn -1937712648> executing prepstmnt 212337536 SELECT …
    [11-11-15 15:13:58:118 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn -1937712648> [6 ms] spent
    [11-11-15 15:13:58:119 CET] 000000ad jdbc_JDBC 3 openjpa.jdbc.JDBC: Trace: <t 728215633, conn -1937712648> [0 ms] close
    [11-11-15 15:13:58:120 CET] 000000ad SystemOut O ######################################## read leave


Somewhere else we have:
    System.out.println("######################################## 6");

    List<BeconBericht> berichten = query.getResultList();
    System.out.println("######################################## leave");

and this results in:
    [11-11-15 15:13:58:160 CET] 000000ad SystemOut O ######################################## 6
    [11-11-15 15:13:58:161 CET] 000000ad Query 3 openjpa.Query: Trace: Executing query: [SELECT bb FROM …
    [11-11-15 15:14:02:670 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn 1696992183> executing prepstmnt -729574466 SELECT …
    [11-11-15 15:14:02:677 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn 1696992183> [6 ms] spent
    [11-11-15 15:14:02:720 CET] 000000ad jdbc_JDBC 3 openjpa.jdbc.JDBC: Trace: <t 728215633, conn 1696992183> [8 ms] close
    [11-11-15 15:14:02:720 CET] 000000ad SystemOut O ######################################## leave

It probably has to do with the database that is used. When using Derby
with Unit Tests we do not have this problem. The test where we have
this problem uses a DB2 database running on a mainframe. When Those
4.5 seconds would disappear we would have a comparable performance
with JPA as we have with JDBC I think.

It should not be a problem with the connection pool. We are using an
IBM wasserver 8.0 and this should take care of the connection pool.

The relevant parts of the persistence.xml:
    <properties>
    <property name="openjpa.Compatibility" value="QuotedNumbersInQueries=true" />
    <property name="openjpa.ConnectionDriverName" value="com.ibm.db2.jcc.DB2Driver" />
    <property name="openjpa.ConnectionFactoryProperties" value="PrintParameters=true" />
    <property name="openjpa.jdbc.DBDictionary" value="org.apache.openjpa.jdbc.sql.DB2Dictionary" />
    <property name="openjpa.Log" value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE" />
    <property name="openjpa.DataCache" value="true (EnableStatistics=false)"/>
    <property name="openjpa.RuntimeUnenhancedClasses" value="unsupported" />
    <property name="openjpa.RemoteCommitProvider" value="sjvm"/>
    <property name="openjpa.ReadLockLevel" value = "none" />
    </properties>

Anyone an idea what is happening here and what to do about it?

If any other information is needed: let me know.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#33111

On 11/11/2015 5:11 PM, Cecil Westerhof wrote:
> On Wednesday 11 Nov 2015 01:56 CET, Arne Vajhøj wrote:
>> On 11/10/2015 2:07 AM, Cecil Westerhof wrote:
>>> But I am told that JPA does a lot of things beside retrieving
>>> things out of the database and that those other things JPA is doing
>>> are the problem. Sounds far-fetched to me, but this is the first
>>> time I am working with JPA. Could they be right? If so: how do we
>>> pinpoint this?
>>
>> JPA does not do things randomly. It does what you tell it to do.
>
> At last I have some useful metrics. It certainly looks like it that
> JPA is the problem: JPA pauses 4.5 seconds before it starts executing
> the datbase queries.
>
> We use the following code:
>      public T read(K id) {
>          System.out.println("######################################## read enter");
>          T entity = em.find(entityClass, id);
>          System.out.println("######################################## read leave");
>          return entity;
>      }
>
> and this results in:
>      [11-11-15 15:13:53:590 CET] 000000ad SystemOut O ######################################## read enter
>      [11-11-15 15:13:58:112 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn -1937712648> executing prepstmnt 212337536 SELECT …
>      [11-11-15 15:13:58:118 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn -1937712648> [6 ms] spent
>      [11-11-15 15:13:58:119 CET] 000000ad jdbc_JDBC 3 openjpa.jdbc.JDBC: Trace: <t 728215633, conn -1937712648> [0 ms] close
>      [11-11-15 15:13:58:120 CET] 000000ad SystemOut O ######################################## read leave
>
>
> Somewhere else we have:
>      System.out.println("######################################## 6");
>
>      List<BeconBericht> berichten = query.getResultList();
>      System.out.println("######################################## leave");
>
> and this results in:
>      [11-11-15 15:13:58:160 CET] 000000ad SystemOut O ######################################## 6
>      [11-11-15 15:13:58:161 CET] 000000ad Query 3 openjpa.Query: Trace: Executing query: [SELECT bb FROM …
>      [11-11-15 15:14:02:670 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn 1696992183> executing prepstmnt -729574466 SELECT …
>      [11-11-15 15:14:02:677 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn 1696992183> [6 ms] spent
>      [11-11-15 15:14:02:720 CET] 000000ad jdbc_JDBC 3 openjpa.jdbc.JDBC: Trace: <t 728215633, conn 1696992183> [8 ms] close
>      [11-11-15 15:14:02:720 CET] 000000ad SystemOut O ######################################## leave
>
> It probably has to do with the database that is used. When using Derby
> with Unit Tests we do not have this problem. The test where we have
> this problem uses a DB2 database running on a mainframe. When Those
> 4.5 seconds would disappear we would have a comparable performance
> with JPA as we have with JDBC I think.

I think you are on the right track now.

> It should not be a problem with the connection pool. We are using an
> IBM wasserver 8.0 and this should take care of the connection pool.
>
> The relevant parts of the persistence.xml:
>      <properties>
>      <property name="openjpa.Compatibility" value="QuotedNumbersInQueries=true" />
>      <property name="openjpa.ConnectionDriverName" value="com.ibm.db2.jcc.DB2Driver" />
>      <property name="openjpa.ConnectionFactoryProperties" value="PrintParameters=true" />
>      <property name="openjpa.jdbc.DBDictionary" value="org.apache.openjpa.jdbc.sql.DB2Dictionary" />
>      <property name="openjpa.Log" value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE" />
>      <property name="openjpa.DataCache" value="true (EnableStatistics=false)"/>
>      <property name="openjpa.RuntimeUnenhancedClasses" value="unsupported" />
>      <property name="openjpa.RemoteCommitProvider" value="sjvm"/>
>      <property name="openjpa.ReadLockLevel" value = "none" />
>      </properties>
>
> Anyone an idea what is happening here and what to do about it?
>
> If any other information is needed: let me know.

That persistence.xml does not look correct.

<property name="openjpa.ConnectionDriverName" 
value="com.ibm.db2.jcc.DB2Driver" />

specifies a JDBC driver not DataSource.

That may (*) make JPA not use WAS connection pool but try to establish
a connection for every operation. And that is very bad.

*) I don't know OpenJPA well enough to say.

The technique may work fine for a local database with a simple login
protocol but with a remote database and complex protocol, then it could
create a huge overhead.

But we would need to see the rest of the persistence,xml to be sure
(the standard part actually determines whether it is driver or
data source).

Try see:

http://stackoverflow.com/questions/3526434/ibm-websphere-jpa-configuration-how-to-update-persistence-xml

for an example of a persistence.xml that looks way
more I would expect (no driver, data source, all managed).

Arne

[toc] | [prev] | [next] | [standalone]

#33113

On Thursday 12 Nov 2015 01:10 CET, Arne Vajhøj wrote:

> That persistence.xml does not look correct.
>
> <property name="openjpa.ConnectionDriverName"
> value="com.ibm.db2.jcc.DB2Driver" />
>
> specifies a JDBC driver not DataSource.
>
> That may (*) make JPA not use WAS connection pool but try to
> establish a connection for every operation. And that is very bad.

That was what I thought was happening. (A new connection every time.)


> The technique may work fine for a local database with a simple login
> protocol but with a remote database and complex protocol, then it
> could create a huge overhead.
>
> But we would need to see the rest of the persistence,xml to be sure
> (the standard part actually determines whether it is driver or
> data source).
>
> Try see:
>
> http://stackoverflow.com/questions/3526434/ibm-websphere-jpa-configuration-how-to-update-persistence-xml
>
> for an example of a persistence.xml that looks way
> more I would expect (no driver, data source, all managed).

I have been trying some things, but until now without success. I need
to go to the office again and from there I do not have newsgroup
access, so some silence on my part again. I will communicate my
progress when I am back home.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#33116

On Thursday 12 Nov 2015 07:27 CET, Cecil Westerhof wrote:

> On Thursday 12 Nov 2015 01:10 CET, Arne Vajhøj wrote:
>
>> That persistence.xml does not look correct.
>>
>> <property name="openjpa.ConnectionDriverName"
>> value="com.ibm.db2.jcc.DB2Driver" />
>>
>> specifies a JDBC driver not DataSource.
>>
>> That may (*) make JPA not use WAS connection pool but try to
>> establish a connection for every operation. And that is very bad.
>
> That was what I thought was happening. (A new connection every
> time.)
>
>
>> The technique may work fine for a local database with a simple
>> login protocol but with a remote database and complex protocol,
>> then it could create a huge overhead.
>>
>> But we would need to see the rest of the persistence,xml to be sure
>> (the standard part actually determines whether it is driver or
>> data source).
>>
>> Try see:
>>
>> http://stackoverflow.com/questions/3526434/ibm-websphere-jpa-configuration-how-to-update-persistence-xml
>>
>> for an example of a persistence.xml that looks way
>> more I would expect (no driver, data source, all managed).
>
> I have been trying some things, but until now without success. I
> need to go to the office again and from there I do not have
> newsgroup access, so some silence on my part again. I will
> communicate my progress when I am back home.

Problem solved. I removed openjpa.ConnectionDriverName and
openjpa.jdbc.DBDictionary.

The unit starts with:
    <persistence-unit name="cvu-persistence-test" transaction-type="JTA">
    <description>Persistence Testen</description>
    <jta-data-source>jdbc/cvu/datasource</jta-data-source>

    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <validation-mode>AUTO</validation-mode>
    <properties>

I added transaction-type, jta-data-source and validation-mode.

The validation-mode needs to be entered just before properties,
otherwise it does not work.

The performance is now comparable with the performance of JDBC. So I
proved the naysayers wrong.

OneToMany relations are also going to be implemented, so maybe the JPA
version will be even faster as the JDBC version. ;-)


One question: I removed
    <property name="openjpa.jdbc.DBDictionary" value="org.apache.openjpa.jdbc.sql.DB2Dictionary" />

It works, but I was told that I could better put it back, because it
was used to optimise complex queries. Is this true?

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

Page 1 of 3  [1] 2 3  Next page →

Back to top | Article view | comp.lang.java.programmer

csiph-web