Re: Entity generation is very time consuming
Okay I tried loading the data with plain JDBC. I found out that moving the cursor over 6 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
Sorry about the slow reply. found out that moving the cursor over 6 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 marc.wal...@sbb.ch wrote: Okay I tried loading the data with plain JDBC. I found out that moving the cursor over 6 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
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
Just for giggles, what if you did something like this:
java.util.ListObject[] datas =
emf.createEntityManager().createQuery(SELECT b.id, b.bezeichnung FROM Bp
b,Object[].class).getResultList();
java.util.ListBpSimpleDto res = new ArrayListBpSimpleDto();
for(Object[] data : datas){
res.add(new BpSimpleDto(data[0],data[1]));
}
On Thu, Jul 14, 2011 at 8:13 AM, M. Walter marc.wal...@sbb.ch wrote:
No problem. This is the regular query:
[7/13/11 15:24:35:380 CEST] 0016 Query 3 openjpa.Query:
Trace:
Executing query: SELECT b FROM Bp b
[7/13/11 15:24:35:380 CEST] 0016 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] 0016 jdbc_SQL 3 openjpa.jdbc.SQL:
Trace: t 1040137727, conn 1793682153 [62 ms] spent
[7/13/11 15:24:58:849 CEST] 0016 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] 0015 Query 3 openjpa.Query:
Trace:
Executing query: SELECT NEW
xxx.xx.xx.common.dto.simple.BpSimpleDto(b.id, b.bezeichnung) FROM
Bp
b
[7/14/11 10:50:41:361 CEST] 0015 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] 0015 jdbc_SQL 3 openjpa.jdbc.SQL:
Trace: t 1133790100, conn 451943152 [47 ms] spent
[7/14/11 10:51:04:642 CEST] 0015 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
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] 0013 Query 3 openjpa.Query: Trace: Executing query: SELECT b.id, b.bezeichnung FROM Bp b [7/14/11 16:08:37:205 CEST] 0013 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] 0013 jdbc_SQL 3 openjpa.jdbc.SQL: Trace: t 1528126229, conn 690825517 [16 ms] spent [7/14/11 16:08:51:784 CEST] 0013 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 80 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.
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
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 marc.wal...@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.
