[hibernate-users] Newbie Performance Questions

[Eric Kolotyluk]

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] *On Behalf Of *Eric 
> Kolotyluk
> *Sent:* Sunday, August 14, 2011 5:11 PM
> *To:* 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/09c9698a/attachment.html