Hi,

I started using Hibernate 1 month ago and now I'm stucked in some horrible performance degradation. I have a query that uses 6 tables making some sum, count, max and group by. The resulted method looks very nice but the execution time is almost 2s. I wrote the same method using PreparedStatement and Connection obtained from the Session object and surprise : it took 0.2s!

I also noticed that most of the queries take more time than using standard JDBC ! What can I do ?

Hibernate version: 2.1.4

Mapping documents:


Name and version of the database you are using: MySQL 4.0

The generated SQL (show_sql=true):
"select userdeta0_.ID as ID0_, userplan2_.pk_user_plan as pk_user_plan1_, director1_.ID as ID2_, "
+ "userdeta0_.PARENTID as PARENTID0_, userdeta0_.FIRSTNAME as FIRSTNAME0_, "
+ "userdeta0_.LASTNAME as LASTNAME0_, userdeta0_.COMPANY as COMPANY0_, "
+ "userdeta0_.DEPARTMENT as DEPARTMENT0_, userdeta0_.PHONE as PHONE0_, userdeta0_.CELL as CELL0_, "
+ "userdeta0_.EMAIL as EMAIL0_, userdeta0_.PASSWORD as PASSWORD0_, userdeta0_.ADDRESS as ADDRESS0_, "
+ "userdeta0_.CITY as CITY0_, userdeta0_.STATE as STATE0_, userdeta0_.ZIPCODE as ZIPCODE0_, "
+ "userdeta0_.COUNTRY as COUNTRY0_, userdeta0_.DATECREATED as DATECRE16_0_, userdeta0_.ACTIVE as ACTIVE0_, "
+ "userdeta0_.EDITTIME as EDITTIME0_, userdeta0_.TOTALPAID as TOTALPAID0_, userdeta0_.CODE as CODE0_, "
+ "userdeta0_.REMARK as REMARK0_, userdeta0_.TYPE as TYPE0_, userdeta0_.DIRECT_PUBLISHING as DIRECT_23_0_, "
+ "userdeta0_.SEND_SLIDETOUR as SEND_SL24_0_, userdeta0_.use_terminus as use_ter25_0_, "
+ "userdeta0_.TEMPLATE as TEMPLATE0_, userdeta0_.USE_EBAY as USE_EBAY0_, "
+ "userdeta0_.EBAY_TOKEN as EBAY_TOKEN0_, userdeta0_.EBAY_TOKEN_EXPIRES as EBAY_TO29_0_, "
+ "userdeta0_.NEXT_INVOICE_DATE as NEXT_IN30_0_, userdeta0_.FIXED_RATE as FIXED_RATE0_, "
+ "userdeta0_.DIRID as DIRID0_, userdeta0_.RESELLERID as RESELLERID0_, "
+ "userplan2_.charge as charge1_, userplan2_.included_images as included3_1_, "
+ "userplan2_.additional_image_price as addition4_1_, userplan2_.self_service_audio as self_ser5_1_, "
+ "userplan2_.professional_audio_price as professi6_1_, "
+ "userplan2_.included_professional_audio as included7_1_, userplan2_.start_date as start_date1_, "
+ "userplan2_.end_date as end_date1_, userplan2_.fk_plan as fk_plan1_, userplan2_.fk_user as fk_user1_, "
+ "director1_.DESCRIPTION as DESCRIPT2_2_, director1_.TITLE as TITLE2_, "
+ "director1_.LEVEL as LEVEL2_, director1_.NUM as NUM2_, "
+ "director1_.PARENTS as PARENTS2_, director1_.DATECREATED as DATECREA7_2_, "
+ "director1_.OWNER as OWNER2_, director1_.ACTIVE as ACTIVE2_, director1_.HIDE as HIDE2_, "
+ "userdeta0_.ID as x0_0_, userplan2_.pk_user_plan as x1_0_, sum(payments4_.PAYMENT) as x2_0_, "
+ "sum(invoices3_.TOTAL_CHARGE) as x3_0_, count(invoices3_.pk_invoice) as x4_0_, count(payments4_.ID) as x5_0_, "
+ "max(invoices3_.END_DATE) as x6_0_, director1_.ID as x7_0_ "
+ "from users userdeta0_ "
+ "inner join directories director1_ on userdeta0_.DIRID=director1_.ID "
+ "inner join user_plan userplan2_ on userdeta0_.ID=userplan2_.fk_user "
+ "left outer join invoice invoices3_ on userdeta0_.ID=invoices3_.fk_user "
+ "left outer join payments payments4_ on invoices3_.pk_invoice=payments4_.fk_invoice "
+ "where (userdeta0_.TYPE=2 )"
+ "AND(userdeta0_.RESELLERID=2 )AND(userplan2_.start_date<=NOW() )"
+ "and((userplan2_.end_date>=NOW() )OR(userplan2_.end_date is null )) "
+ "group by userdeta0_.ID");

the overhead is about 5% and can be above if you're not using hibernate well

so do it in jdbc, or show hql... if you really want our help

oh, and next time, don't compare to jdbc, hibernate and jdbc don't do the same things!

Hi,

First of all, thank you for the quick answer.

Here is my HQL :

final String SELECT =
"select userDetails from UserDetails as userDetails "
+ " WHERE userDetails.directoryDetails.parents LIKE '1-2-5%'"
+ " ORDER BY userDetails.parentid";

This operation take 300ms but if I am using simple JDBC call it takes 30ms !

I have a 1-m relation between UserDetails and DirectoryDetails

Please tell me if you need more information.

Thank you,
Cornel

are you lazying or eager fetching the association? if not, read the manual.

Hi,

I am sure that you know the Test from org.hibernate.auction package.

Here is my test result(on MySQL v4) :

long t0 = System.currentTimeMillis();;
test.viewAuctionsByDescription("auction item 1", 44);
System.out.println("TOTAL=" + (System.currentTimeMillis()-t0));

-------------> TOTAL=157

The resulted SQL is :

select this.id as id0_, this.description as descript2_0_, this.ends as ends0_, this.condition as condition0_, this.seller as seller0_, this.successfulBid as successf6_0_ from AuctionItem this where (lower(this.description) like ? and this.condition=?)

I used a trivial JDBC test using the above SQL and same conditions and I created the AuctionItem objects from ResultSet.

The result is concludent:
-------------> TOTAL=31

Please tell me what is wrong !

Thank you,
Cornel

what is wrong is your way of thinking:
1- turn off logs
2- use projection (if you don't understand, take a look at the manual!)
3- be sure a jdbc connection is opened in the pool before getting object