Hi all,
I'm facing a performance problem which I think must be for a large part due to my lack of skill and/or knowledge :
Well I'm rewriting a legacy app made with a RAD tool (Delphi like)
In this app there is two tables I have to deal with carefully.
Table TabA is quite simple
Code:
TabA
--------
@GeneratedValue(strategy = GenerationType.AUTO)
id: Long
StrFld : String
@OneToMany(mappedBy = "source", cascade = {CascadeType.REMOVE})
private Set<TabB > relatedSourceSet= new HashSet<TabB >();
@OneToMany(mappedBy = "target", cascade = {CascadeType.REMOVE})
private Set<TabB > relatedTargetSet= new HashSet<TabB >();
Table TabB is a table maintaining relations between instance of TabA
Code:
TabB
--------
@GeneratedValue(strategy = GenerationType.AUTO)
id: Long
@ManyToOne()
@JoinColumn
Source : TabA
@ManyToOne()
@JoinColumn
Target : TabA
@ManyToOne
RelationType: TabRelation
I got a TabRelation where we maintain a dozen or so kind of relation.Well that's quite simple , I hope I was clear enough on this first part. The entities are really simple
Then I got a EJB (in fact EJB or seam Component ; I tried both
it's an EJB3 / SEAM application)
wich is in charge of IMPORTING instances of this from a file ( the file can be XML or labelled)
I already took care of taking the file , parsing it and splitting it...
so my EJB make all this step then start doing the persist / update job
Quote:
Code:
TabA tabA_instanceSub;
TabA tabA_instance;
....
querySt = "SELECT t FROM TabA descT WHERE UPPER(t.StrFld) = :item";
query = em.createQuery(querySt);
query.setParameter("item", listVal.get(i2).trim().toUpperCase());
lstRes = query.getResultList();
if (lstRes.isEmpty()) {
//This one is brand new !!
tabA_instance = new TabA();
tabA_instance.setStrFld(listVal.get(i2).trim());
em.persist(tabA_instance);
} else {
//We got it already
tabA_instance = (TabA) lstRes.get(0);
}
.....
...
TabB tabB_instance;
querySt = "SELECT relT FROM TabB relT WHERE relT.relationType = :relationType";
querySt = querySt + " AND UPPER(relT.Source.StrFld) = :StrFld1 AND UPPER(relT.target.StrFld) = :StrFld2";
query = em.createQuery(querySt);
query.setParameter("relationType", DESCR_REL_TYPE);
query.setParameter("StrFld1", tabA_instance.getStrFld().toUpperCase());
query.setParameter("StrFld2", tabA_instanceSub.getStrFld().toUpperCase());
lstRes = query.getResultList();
if (lstRes.isEmpty()) {
tabB_instance = new TabB(tabA_instance, tabA_instanceSub, REL_TYP_TS);
em.persist(tabB_instance);
}
Well I tried to simplify everything I can for sake of clarity but all is here
: i'm in a transaction , I look to see if I already got the current item ? if not I create (and persist it) then I look for the relation and same simple process do I got it ?
Not so horrible hey ?!
Well now my problem is this : with a file with 9887 instances of TabA; and 10632 relations instances of tabB it took less than 5min in the legacy app and 3 hours with my JEE stuff !!
No need to say this is not possible for the users the are doing this quite every day ...
No way for the import file to be an sql.. it came from different sources that's why it pass through a xml so i can handle it.
Anyway if I have to i will do something on it but 2 questions here
-how should I handle the Id which are JPA- annoted to be generated !!!??
-And how to handle the fact tha I have to chase duplicate BEFORE creating a new instance of tabA ??
the transformation of the file to produce the arraylist I feed on is a matter of seconds. so it's clearly not the point here.
Which way should I tune the transaction to get back to some correct performance ?
Maybe I should try
WITHOUT any transaction just to see ??
I d'idn't try yet because I need to be in a transaction it' s a requirement
Thanks for your help
Hibernate version: 3.3.1
Mapping documents: JPA
Code between sessionFactory.openSession() and session.close():
Full stack trace of any exception that occurs:
Name and version of the database you are using: MySql 5 and also Derby (both gives the same perf)
The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:
Login
FAQ
Search
