[hibernate-users] Newbie Performance Questions
Eric Kolotyluk
eric.kolotyluk at gmail.com
Mon Aug 15 00:57:46 EDT 2011
OK, I finally figured out the working HQL, and now performance is great!
Having three entities with m2m bidirectional associations took a while
to wrap my head around using HQL, but the following code works. I may be
able to optimize it a bit more, but now I'm just glad it works :-)
String hql = "SELECT new
com.kodak.vip.color.repository.SpotColor("
+ "color.name, "
+ "representationLAB.coordinates.l, "
+ "representationLAB.coordinates.a, "
+ "representationLAB.coordinates.b) "
+ "FROM "
+ "Library as library join
library.colors as lc, "
+ "Color as color join color.libraries
as cl join color.representations as cr, "
+ "Representation as representation
join representation.colors as rc, "
+ "RepresentationLAB as representationLAB "
+ "WHERE "
+ "library.name = '" + getName() + "' and "
+ "library.id = cl.id and "
+ "color.id = lc.id and "
+ "color.id = rc.id and "
+ "cr.id = representation.id and "
+ "representation.id =
representationLAB.id";
Query query =
hibernate.getCurrentSession().createQuery(hql);
return query.list();
On 2011-08-14 9:29 PM, Haswell, Joe wrote:
>
> Yeah. If you're doing something along the lines of createQuery("from
> Library library") and then accessing the colors of the library, it's
> definitely an N+1 selects problem. You can avoid this by doing a
> join-fetch ("from Library library join fetch library.colors color") or
> possibly by using the @BatchSize annotation.
>
>
> Best!
>
> *From:*Eric Kolotyluk [mailto:eric.kolotyluk at gmail.com]
> *Sent:* Sunday, August 14, 2011 10:22 PM
> *To:* Haswell, Joe
> *Subject:* Re: [hibernate-users] Newbie Performance Questions
>
> OK, please see the attachments.
>
> I am not really understanding how HQL transforms into SQL, as it was
> easy for me to write the SQL
>
> Cheers, Eric
>
> On 2011-08-14 9:14 PM, Haswell, Joe wrote:
>
> Can you include your entities?
>
> Thanks,
>
>
> Joe H. | HP Software
>
> *From:*hibernate-users-bounces at lists.jboss.org
> <mailto:hibernate-users-bounces at lists.jboss.org>
> [mailto:hibernate-users-bounces at lists.jboss.org] *On Behalf Of *Eric
> Kolotyluk
> *Sent:* Sunday, August 14, 2011 9:45 PM
> *To:* hibernate-users at lists.jboss.org
> <mailto:hibernate-users at lists.jboss.org>
> *Subject:* Re: [hibernate-users] Newbie Performance Questions
>
> I'm not sure, I' just using whatever the defaults are.
>
> The more I think of it the more I think the problem is that I am doing
> my iterations procedurally in Java, rather than declaratively in HQL.
> While I can use the following SQL query to get my results, I cannot
> figure out the corresponding HQL query.
>
> SELECT
> "Color"."name",
> "RepresentationLAB".coordinates_l,
> "RepresentationLAB".coordinates_b,
> "RepresentationLAB".coordinates_a
> FROM
> "ColorRepository"."Library",
> "ColorRepository"."Library_Color",
> "ColorRepository"."Color",
> "ColorRepository"."Color_Representation",
> "ColorRepository"."RepresentationLAB"
> WHERE
> "RepresentationLAB"."id" = "Color_Representation"."rightID" and
> "Color_Representation"."leftID" = "Color"."id" and
> "Color"."id" = "Library_Color"."rightID" and
> "Library_Color"."leftID" = "Library"."id" and
> "Library"."name" = 'PANTONE Goe coated'
>
> The HQL query I am trying follows, but when I run it nothing is returned
>
> String hql = "SELECT new
> com.kodak.vip.color.repository.SpotColor("
> + "colors.name, "
> + "representationLAB.coordinates.l, "
> + "representationLAB.coordinates.a, "
> + "representationLAB.coordinates.b) "
> + "FROM "
> + "Library as library join
> library.colors as colors, "
> + "Color as color join
> color.representations as representations, "
> + "RepresentationLAB as
> representationLAB "
> + "WHERE "
> + "representationLAB.id =
> representations.id and "
> + "representations.id = color.id and "
> + "color.id = colors.id and "
> + "colors.id = library.id and "
> + "library.name = '" + getName() + "'";
>
> Query query =
> hibernate.getCurrentSession().createQuery(hql);
> return query.list();
>
> Which generates the following SQL
>
> Hibernate:
> select
> color2_."name" as col_0_0_,
> representa6_."coordinates_l" as col_1_0_,
> representa6_."coordinates_a" as col_2_0_,
> representa6_."coordinates_b" as col_3_0_
> from
> "ColorRepository"."Library" library0_
> inner join
> "ColorRepository"."Library_Color" colors1_
> on library0_."id"=colors1_."leftID"
> inner join
> "ColorRepository"."Color" color2_
> on colors1_."rightID"=color2_."id" cross
> join
> "ColorRepository"."Color" color3_
> inner join
> "ColorRepository"."Color_Representation" representa4_
> on color3_."id"=representa4_."leftID"
> inner join
> "ColorRepository"."Representation" representa5_
> on representa4_."rightID"=representa5_."id" cross
> join
> "ColorRepository"."RepresentationLAB" representa6_
> inner join
> "ColorRepository"."Representation" representa6_1_
> on representa6_.id=representa6_1_."id"
> where
> representa6_.id=representa5_."id"
> and representa5_."id"=color3_."id"
> and color3_."id"=color2_."id"
> and color2_."id"=library0_."id"
> and library0_."name"='PANTONE Goe coated'
>
> Cheers, Eric
>
> On 2011-08-14 5:00 PM, Haswell, Joe wrote:
>
> Are the Library's colors lazily loaded? You could be encountering an
> N+1 selects issue.
>
> *From:*hibernate-users-bounces at lists.jboss.org
> <mailto:hibernate-users-bounces at lists.jboss.org>
> [mailto:hibernate-users-bounces at lists.jboss.org] *On Behalf Of *Eric
> Kolotyluk
> *Sent:* Sunday, August 14, 2011 5:11 PM
> *To:* hibernate-users at lists.jboss.org
> <mailto:hibernate-users at lists.jboss.org>
> *Subject:* [hibernate-users] Newbie Performance Questions
>
> I have been using Hibernate since January 2011, but more recently I am
> integrating things into some UI. For some context I have an
> application that deals with libraries of colors.
>
> Our current application stores the libraries in XML files under Castor
> on the local file system. A given library has 2,000 colors in it and
> takes maybe 200 ms to load.
>
> My JPA framework looks something like
>
> @Entity Library - m2m - @Entity Color - m2m - @Entity Representation
> (i.e. L*a*b*)
>
> In my application, loading from a PostgreSQL database via Hibernate
> the same data takes
>
> 1. 20 seconds to load with full logging enabled
> 2. 16 seconds to load with logging disabled
> 3. 4 seconds to reload a second time (probably due the default
> caching behavior)
> 4. 0.2 seconds from via SQL from PostgreSQL
> 5. 0.2 seconds to load from Castor
> 6. 0 seconds (usually) if I use SoftReferences to cache the data in
> memory
>
> Some of my questions are:
>
> 1. Is the performance I am seeing typical? Is it what most people
> would agree to expect from using Hibernate with a DBMS like
> PostgreSQL?
> 2. When logging is turned on, I see what looks like an obscene amount
> of SQL produced for only 2,000 colors. When logging is turned off,
> 16 seconds still seems like an awfully long time to load 2,000
> color representations, where running an SQL query to extract the
> same data takes 203 ms.
> 3. What exactly is taking so long? Using Hibernate I basically load
> the library object, then iterate over all the colors it has, then
> for each color object I get the representation object and load the
> L*a*b* data into the UI.
> 4. Am I making some lame newbie mistake somewhere, and what would
> that mistake be?
> 5. Should I be formulating an appropriate HQL statement, similar to
> my SQL statement, and loading things that way?
> 6. Is there some documentation on using Hibernate with Graphical User
> Interfaces, or some best practices documentation somewhere?
>
> Cheers, Eric
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-users/attachments/20110814/84650c13/attachment-0001.html
More information about the hibernate-users
mailing list