Hello,
i have encountered a strange n+1 problem and severe performance degradation on MSSQL with either official and/or opensource (jtds) drivers alike.
i believe i have the right mapping, and i suspect that maybe inheritence causes the problem (nothing else left :-).
Hibernate version: 3.2.2
Mapping documents: (just fragments)
There is many-to-one relation mapped as shown bellow -- default (lazy) mapping on both sides.
There are times when i must access plenty of that objects (cca 5000) and attributes from both of them are needed. In that case i use the Criteria and/or HQL query to explicitly fetch the objects (Criteria shown below).
from BookEntryOperation to BookEntry
Code:
<!-- BookEntryOperation -->
<subclass name="BookEntryOperation" discriminator-value="book_entry">
<property name="newDate">
<column name="new_date" not-null="false"/>
</property>
<property name="newAmount">
<column name="new_amount" not-null="false"/>
</property>
<!-- should be uniq, 1:1, some db (MSSQL) have problems -->
<many-to-one name="bookEntry" class="BookEntry" column="book_entry_id"
not-null="false" unique="false"/>
</subclass>
from BookEntry to BookEntryOperation
Code:
<one-to-one name="operation" class="BookEntryOperation" cascade="all"
property-ref="bookEntry"/>
Code between sessionFactory.openSession() and session.close():When executing this criteria, i got the right sql query with left join (as shown below),
BUT i also get
n queries fetching the single objects (no matter what db engine -- MySQL, HSQLdb, MSSQL).
Code:
Criteria c = session.createCriteria(BookEntryOperation.class);
c.setFetchMode("bookEntry", FetchMode.JOIN)
.createCriteria("bookEntry");
Disjunction or = Restrictions.disjunction();
or.add(Restrictions.between("date", getFromDate(), getToDate()));
or.add(Restrictions.between("newDate", getFromDate(), getToDate()));
c.add(or);
addOperationAssocCriteria(c);
return c;
In case of MySQL or HSQLdb the speed is sufficient and the queries just run through my log returning almost immediatelly and i would say there is no performace loss (still strange to me, however, that though i got the right left join query and the objects shoul already be selected, the db runs another n queries to get them).
On MSSQL the performance is terrible. On MySQl or HSQL i do the queries (the 1 with left join and then the n to get that objects -- though the running of that n queries is strange to me as mentioned), do the computation, and generate the result (business object that gets later translated to HTML) in seconds. MSSQL runs the same queries and the generations tooks about 5 minutes -- during those 5 minute the n queries is written to log .
I did check for general db settings (as far as my knowledge goes) -- the autoCommit, transactionIsolation and everything seems to be just fine.
Full stack trace of any exception that occurs:no exception
Name and version of the database you are using:MySQL 5.0, HSQLdb 1.8, MSSQL 2000/2005
The generated SQL (show_sql=true):Code:
9349 [main] DEBUG (SQL) org.hibernate.jdbc.AbstractBatcher.log(AbstractBatcher.java:393) -
/* criteria query */ select
this_.operation_id as operation1_10_1_,
this_.typex as typex10_1_,
this_.amount as amount10_1_,
this_.datex as datex10_1_,
this_.status as status10_1_,
this_.description as descript7_10_1_,
this_.confirmationNumber as confirma8_10_1_,
this_.currency_id as currency9_10_1_,
this_.account_id as account10_10_1_,
this_.counter_party_id as counter11_10_1_,
this_.organization_unit_id as organiz12_10_1_,
this_.new_date as new16_10_1_,
this_.new_amount as new17_10_1_,
this_.book_entry_id as book18_10_1_,
bookentry1_.book_entry_id as book1_6_0_,
bookentry1_.ctime as ctime6_0_,
bookentry1_.mtime as mtime6_0_,
bookentry1_.creator as creator6_0_,
bookentry1_.owner as owner6_0_,
bookentry1_.business_id as business6_6_0_,
bookentry1_.business_var_id as business7_6_0_,
bookentry1_.purchase_date as purchase8_6_0_,
bookentry1_.posting_date as posting9_6_0_,
bookentry1_.settlement_date as settlement10_6_0_,
bookentry1_.description as descrip11_6_0_,
bookentry1_.import_warning as import12_6_0_,
bookentry1_.user_override as user13_6_0_,
bookentry1_.book_entry_type_id as book14_6_0_,
bookentry1_.group_id as group15_6_0_,
bookentry1_.import_file_id as import16_6_0_
from
operation this_
inner join
book_entry bookentry1_
on this_.book_entry_id=bookentry1_.book_entry_id
where
this_.subclass='book_entry'
and (
this_.datex between ? and ?
or this_.new_date between ? and ?
)
9481 [main] DEBUG (SQL) org.hibernate.jdbc.AbstractBatcher.log(AbstractBatcher.java:393) -
/* load cz.facility.facicash.app.core.BookEntryOperation */ select
bookentryo0_.operation_id as operation1_10_0_,
bookentryo0_.typex as typex10_0_,
bookentryo0_.amount as amount10_0_,
bookentryo0_.datex as datex10_0_,
bookentryo0_.status as status10_0_,
bookentryo0_.description as descript7_10_0_,
bookentryo0_.confirmationNumber as confirma8_10_0_,
bookentryo0_.currency_id as currency9_10_0_,
bookentryo0_.account_id as account10_10_0_,
bookentryo0_.counter_party_id as counter11_10_0_,
bookentryo0_.organization_unit_id as organiz12_10_0_,
bookentryo0_.new_date as new16_10_0_,
bookentryo0_.new_amount as new17_10_0_,
bookentryo0_.book_entry_id as book18_10_0_
from
operation bookentryo0_
where
bookentryo0_.book_entry_id=?
and bookentryo0_.subclass='book_entry'
Debug level Hibernate log excerpt:
see above
- - - -
I suspect that maybe the inheritance mapping has something to do with it (the last idea i have) -- in this particular case i use table-per-class-hierarchy strategy and although i select only _BookEntryOperation_ in my query and there are none superclasses defined, the implementation asks with a single query to fetch all the attributes of a given class in case it would be a super class? If so, is there any work arround?
Thanks a lot for any comment, martin.
Login
FAQ
Search
