You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by "M. Walter" <ma...@sbb.ch> on 2011/07/13 17:15:42 UTC
Entity generation is very time consuming
I have an Oracle 10g database and in the JEE application I use
OpenJPA-1.2.3-SNAPSHOT (which is shipped with IBM WebSphere 7 application
server). I'm loading 60.000 rows from a table with some booleans, strings,
numbers and timestamps. Nothing big and no blobs and things like that. The
generation of the corresponding JPA entities takes 24 seconds. This is very
long in my opinion. Is there any possibility to tune and speed things up
considerably? I think 60.000 entities should not be that much data so I have
to get a cup of coffee every time I trigger the select...
Do you have any hints for me in order to increase performance? Thank you!
P.S.: Unfortunately I can't upgrade to JPA 2.0, only JPA 1.0 features
allowed.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Entity-generation-is-very-time-consuming-tp6579389p6579389.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Entity generation is very time consuming
Posted by Rick Curtis <cu...@gmail.com>.
Sorry about the slow reply.
> found out that moving the cursor over 60000 rows takes 14 seconds!
Well that is good news for OpenJPA!
I will note that JPA wasn't designed for these sort of mass load (ETL)[1]
situations. It sounds like for this scenario we're going to be good enough,
but if you continue down the track you are, at some point JPA (most/all
providers) won't do quite what you are looking for.
[1] http://en.wikipedia.org/wiki/Extract,_transform,_load
On Mon, Jul 18, 2011 at 6:40 AM, M. Walter <ma...@sbb.ch> wrote:
> Okay I tried loading the data with plain JDBC. I found out that moving the
> cursor over 60000 rows takes 14 seconds! So I can't blame JPA for taking so
> much time. Well at least that's how Oracle 11g performs. Maybe there are
> other faster databases.
>
> --
>
--
*Rick Curtis*
Re: Entity generation is very time consuming
Posted by "M. Walter" <ma...@sbb.ch>.
Okay I tried loading the data with plain JDBC. I found out that moving the
cursor over 60000 rows takes 14 seconds! So I can't blame JPA for taking so
much time. Well at least that's how Oracle 11g performs. Maybe there are
other faster databases.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Entity-generation-is-very-time-consuming-tp6579389p6594377.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Entity generation is very time consuming
Posted by "M. Walter" <ma...@sbb.ch>.
Well I can't use typed queries because I use JPA 1.0.
This is the result of your suggestion:
[7/14/11 16:08:37:205 CEST] 00000013 Query 3 openjpa.Query: Trace:
Executing query: SELECT b.id, b.bezeichnung FROM Bp b
[7/14/11 16:08:37:205 CEST] 00000013 jdbc_SQL 3 openjpa.jdbc.SQL:
Trace: <t 1528126229, conn 690825517> executing prepstmnt 819474648 SELECT
t0.ID, t0.BEZEICHNUNG FROM BP t0
[7/14/11 16:08:37:221 CEST] 00000013 jdbc_SQL 3 openjpa.jdbc.SQL:
Trace: <t 1528126229, conn 690825517> [16 ms] spent
[7/14/11 16:08:51:784 CEST] 00000013 jdbc_JDBC 3 openjpa.jdbc.JDBC:
Trace: <t 1528126229, conn 690825517> [0 ms] close
14 seconds. Better. But still far away from fast for reading two little
fields...
Maybe JPA isn't made for reading tables with many rows. I wonder what
developers and users do in the meantime when they have to read and process
let's say 800000 or more data rows. Meet the girlfriend in the park? Go on
holidays? Hey we are living in the 21st century. Machines have power. ;-)
I will try a plain old JDBC database access next and see how it performs.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Entity-generation-is-very-time-consuming-tp6579389p6583445.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Entity generation is very time consuming
Posted by Rick Curtis <cu...@gmail.com>.
Just for giggles, what if you did something like this:
java.util.List<Object[]> datas =
emf.createEntityManager().createQuery("SELECT b.id, b.bezeichnung FROM Bp
b",Object[].class).getResultList();
java.util.List<BpSimpleDto> res = new ArrayList<BpSimpleDto>();
for(Object[] data : datas){
res.add(new BpSimpleDto(data[0],data[1]));
}
On Thu, Jul 14, 2011 at 8:13 AM, M. Walter <ma...@sbb.ch> wrote:
> No problem. This is the regular query:
>
> [7/13/11 15:24:35:380 CEST] 00000016 Query 3 openjpa.Query:
> Trace:
> Executing query: SELECT b FROM Bp b
> [7/13/11 15:24:35:380 CEST] 00000016 jdbc_SQL 3 openjpa.jdbc.SQL:
> Trace: <t 1040137727, conn 1793682153> executing prepstmnt 969750989 SELECT
> t0.ID, t0.ROW_MUT_VERSION, t0.ROW_ERF_TSTAMP, t0.ROW_ERF_USER,
> t0.ROW_MUT_TSTAMP, t0.ROW_MUT_USER, t0.ABKUERZUNG, t0.BEZEICHNUNG,
> t0.BEZEICHNUNG_LANG, t0.BPUIC, t1.ID, t1.ROW_MUT_VERSION,
> t1.ROW_ERF_TSTAMP,
> t1.ROW_ERF_USER, t1.ROW_MUT_TSTAMP, t1.ROW_MUT_USER, t1.CHECK_URL,
> t1.FORMAT, t1.FTP_CLIENT_FACTORY, t1.INTERVALL, t1.NAME, t1.TYP,
> t1.VERZEICHNIS_ARCHIV, t1.VERZEICHNIS_DB, t1.VERZEICHNIS_IN,
> t0.FIKTIV_BP_TF, t0.GUELTIG_BIS, t0.GUELTIG_VON, t0.HALT_AUF_VERLANGEN_TF,
> t2.ID, t2.ROW_MUT_VERSION, t2.ROW_ERF_TSTAMP, t2.ROW_ERF_USER,
> t2.ROW_MUT_TSTAMP, t2.ROW_MUT_USER, t2.ABKUERZUNG, t2.ROW_GUELTIG_BIS,
> t2.ROW_GUELTIG_VON, t3.ID, t3.ROW_MUT_VERSION, t3.ROW_ERF_TSTAMP,
> t3.ROW_ERF_USER, t3.ROW_MUT_TSTAMP, t3.ROW_MUT_USER, t3.BESCHREIBUNG_DE,
> t3.BESCHREIBUNG_EN, t3.BESCHREIBUNG_FR, t3.BESCHREIBUNG_IT,
> t3.ROW_GUELTIG_BIS, t3.ROW_GUELTIG_VON, t3.TEXT_DE, t3.TEXT_EN, t3.TEXT_FR,
> t3.TEXT_IT, t2.UIC_LAND, t2.WAEHRUNG, t2.ZEITZONE, t0.PRIO, t0.REGION,
> t0.ROW_GUELTIG_BIS, t0.ROW_GUELTIG_VON, t0.UIC_BP, t0.UIC_KONTROLLZIFFER,
> t0.X_GEO, t0.X_SWISS_GRID, t0.Y_GEO, t0.Y_SWISS_GRID, t0.Z_GEO,
> t0.Z_SWISS_GRID FROM BP t0, DATENQUELLE t1, LAND t2, TEXT_SD t3 WHERE
> t0.DATENQUELLE_ID = t1.ID(+) AND t0.LAND_ID = t2.ID(+) AND t2.TEXT_SD_ID =
> t3.ID(+)
> [7/13/11 15:24:35:442 CEST] 00000016 jdbc_SQL 3 openjpa.jdbc.SQL:
> Trace: <t 1040137727, conn 1793682153> [62 ms] spent
> [7/13/11 15:24:58:849 CEST] 00000016 jdbc_JDBC 3 openjpa.jdbc.JDBC:
> Trace: <t 1040137727, conn 1793682153> [0 ms] close
>
>
> And this query reads two fields from a table using constructor expression:
>
> [7/14/11 10:50:41:361 CEST] 00000015 Query 3 openjpa.Query:
> Trace:
> Executing query: SELECT NEW
> xxx.xxxxxx.xxxxxx.common.dto.simple.BpSimpleDto(b.id, b.bezeichnung) FROM
> Bp
> b
> [7/14/11 10:50:41:361 CEST] 00000015 jdbc_SQL 3 openjpa.jdbc.SQL:
> Trace: <t 1133790100, conn 451943152> executing prepstmnt 455416613 SELECT
> t0.ID, t0.BEZEICHNUNG FROM BP t0
> [7/14/11 10:50:41:408 CEST] 00000015 jdbc_SQL 3 openjpa.jdbc.SQL:
> Trace: <t 1133790100, conn 451943152> [47 ms] spent
> [7/14/11 10:51:04:642 CEST] 00000015 jdbc_JDBC 3 openjpa.jdbc.JDBC:
> Trace: <t 1133790100, conn 451943152> [0 ms] close
>
> As you can see both the first and second query take 23 seconds to execute.
> There are exactly 59035 rows in the table.
>
> --
*Rick Curtis*
Re: Entity generation is very time consuming
Posted by "M. Walter" <ma...@sbb.ch>.
No problem. This is the regular query:
[7/13/11 15:24:35:380 CEST] 00000016 Query 3 openjpa.Query: Trace:
Executing query: SELECT b FROM Bp b
[7/13/11 15:24:35:380 CEST] 00000016 jdbc_SQL 3 openjpa.jdbc.SQL:
Trace: <t 1040137727, conn 1793682153> executing prepstmnt 969750989 SELECT
t0.ID, t0.ROW_MUT_VERSION, t0.ROW_ERF_TSTAMP, t0.ROW_ERF_USER,
t0.ROW_MUT_TSTAMP, t0.ROW_MUT_USER, t0.ABKUERZUNG, t0.BEZEICHNUNG,
t0.BEZEICHNUNG_LANG, t0.BPUIC, t1.ID, t1.ROW_MUT_VERSION, t1.ROW_ERF_TSTAMP,
t1.ROW_ERF_USER, t1.ROW_MUT_TSTAMP, t1.ROW_MUT_USER, t1.CHECK_URL,
t1.FORMAT, t1.FTP_CLIENT_FACTORY, t1.INTERVALL, t1.NAME, t1.TYP,
t1.VERZEICHNIS_ARCHIV, t1.VERZEICHNIS_DB, t1.VERZEICHNIS_IN,
t0.FIKTIV_BP_TF, t0.GUELTIG_BIS, t0.GUELTIG_VON, t0.HALT_AUF_VERLANGEN_TF,
t2.ID, t2.ROW_MUT_VERSION, t2.ROW_ERF_TSTAMP, t2.ROW_ERF_USER,
t2.ROW_MUT_TSTAMP, t2.ROW_MUT_USER, t2.ABKUERZUNG, t2.ROW_GUELTIG_BIS,
t2.ROW_GUELTIG_VON, t3.ID, t3.ROW_MUT_VERSION, t3.ROW_ERF_TSTAMP,
t3.ROW_ERF_USER, t3.ROW_MUT_TSTAMP, t3.ROW_MUT_USER, t3.BESCHREIBUNG_DE,
t3.BESCHREIBUNG_EN, t3.BESCHREIBUNG_FR, t3.BESCHREIBUNG_IT,
t3.ROW_GUELTIG_BIS, t3.ROW_GUELTIG_VON, t3.TEXT_DE, t3.TEXT_EN, t3.TEXT_FR,
t3.TEXT_IT, t2.UIC_LAND, t2.WAEHRUNG, t2.ZEITZONE, t0.PRIO, t0.REGION,
t0.ROW_GUELTIG_BIS, t0.ROW_GUELTIG_VON, t0.UIC_BP, t0.UIC_KONTROLLZIFFER,
t0.X_GEO, t0.X_SWISS_GRID, t0.Y_GEO, t0.Y_SWISS_GRID, t0.Z_GEO,
t0.Z_SWISS_GRID FROM BP t0, DATENQUELLE t1, LAND t2, TEXT_SD t3 WHERE
t0.DATENQUELLE_ID = t1.ID(+) AND t0.LAND_ID = t2.ID(+) AND t2.TEXT_SD_ID =
t3.ID(+)
[7/13/11 15:24:35:442 CEST] 00000016 jdbc_SQL 3 openjpa.jdbc.SQL:
Trace: <t 1040137727, conn 1793682153> [62 ms] spent
[7/13/11 15:24:58:849 CEST] 00000016 jdbc_JDBC 3 openjpa.jdbc.JDBC:
Trace: <t 1040137727, conn 1793682153> [0 ms] close
And this query reads two fields from a table using constructor expression:
[7/14/11 10:50:41:361 CEST] 00000015 Query 3 openjpa.Query: Trace:
Executing query: SELECT NEW
xxx.xxxxxx.xxxxxx.common.dto.simple.BpSimpleDto(b.id, b.bezeichnung) FROM Bp
b
[7/14/11 10:50:41:361 CEST] 00000015 jdbc_SQL 3 openjpa.jdbc.SQL:
Trace: <t 1133790100, conn 451943152> executing prepstmnt 455416613 SELECT
t0.ID, t0.BEZEICHNUNG FROM BP t0
[7/14/11 10:50:41:408 CEST] 00000015 jdbc_SQL 3 openjpa.jdbc.SQL:
Trace: <t 1133790100, conn 451943152> [47 ms] spent
[7/14/11 10:51:04:642 CEST] 00000015 jdbc_JDBC 3 openjpa.jdbc.JDBC:
Trace: <t 1133790100, conn 451943152> [0 ms] close
As you can see both the first and second query take 23 seconds to execute.
There are exactly 59035 rows in the table.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Entity-generation-is-very-time-consuming-tp6579389p6583168.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Entity generation is very time consuming
Posted by Rick Curtis <cu...@gmail.com>.
Can you turn on SQL trace so we can see what is being generated? Perhaps
posting your Entity would also shed some light on what is going on.
On Thu, Jul 14, 2011 at 4:08 AM, M. Walter <ma...@sbb.ch> wrote:
> I have one single select query and the connection pool shows one connection
> being obtained.
>
> The entity has indeed many one-to-many and many-to-one relationships. So if
> that should be the problem I created a DTO which has only the primary key
> (Long) and a string. Then I used constructor expression filling up this DTO
> instead of the entity with all its dependencies (SELECT NEW MyDto(t.id,
> t.name) FROM Table t). I was very astonished about the fact that it just
> lasted as long as the original entity creation! How can this be explained?
> Nearly 27 seconds reading two fields from the database table and generating
> 60.000 DTOs? This is awfully low-performance! Are the JPA entities still
> generated with all relationships if constructor expressions are used?
>
> One thing: I'm using Oracle 11g, not 10g as written in my first posting.
>
> --
>
>
--
*Rick Curtis*
Re: Entity generation is very time consuming
Posted by "M. Walter" <ma...@sbb.ch>.
I have one single select query and the connection pool shows one connection
being obtained.
The entity has indeed many one-to-many and many-to-one relationships. So if
that should be the problem I created a DTO which has only the primary key
(Long) and a string. Then I used constructor expression filling up this DTO
instead of the entity with all its dependencies (SELECT NEW MyDto(t.id,
t.name) FROM Table t). I was very astonished about the fact that it just
lasted as long as the original entity creation! How can this be explained?
Nearly 27 seconds reading two fields from the database table and generating
60.000 DTOs? This is awfully low-performance! Are the JPA entities still
generated with all relationships if constructor expressions are used?
One thing: I'm using Oracle 11g, not 10g as written in my first posting.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Entity-generation-is-very-time-consuming-tp6579389p6582447.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Entity generation is very time consuming
Posted by Michael Dick <mi...@gmail.com>.
I don't have any general tips that will apply to every situation, so here
come more questions.
How are you loading the rows (one big query, or several smaller ones)?
Do you have a lot of relationships in your entities (the resulting joins can
slow things down).
Do you see a lot of connections being obtained from the connection pool?
-mike
On Wed, Jul 13, 2011 at 10:15 AM, M. Walter <ma...@sbb.ch> wrote:
> I have an Oracle 10g database and in the JEE application I use
> OpenJPA-1.2.3-SNAPSHOT (which is shipped with IBM WebSphere 7 application
> server). I'm loading 60.000 rows from a table with some booleans, strings,
> numbers and timestamps. Nothing big and no blobs and things like that. The
> generation of the corresponding JPA entities takes 24 seconds. This is very
> long in my opinion. Is there any possibility to tune and speed things up
> considerably? I think 60.000 entities should not be that much data so I
> have
> to get a cup of coffee every time I trigger the select...
>
> Do you have any hints for me in order to increase performance? Thank you!
>
> P.S.: Unfortunately I can't upgrade to JPA 2.0, only JPA 1.0 features
> allowed.
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Entity-generation-is-very-time-consuming-tp6579389p6579389.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>