Re: [hibernate-users] Newbie Performance Questions
by Eric Kolotyluk
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@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@lists.jboss.org
> <mailto:hibernate-users-bounces@lists.jboss.org>
> [mailto:hibernate-users-bounces@lists.jboss.org] *On Behalf Of *Eric
> Kolotyluk
> *Sent:* Sunday, August 14, 2011 9:45 PM
> *To:* hibernate-users(a)lists.jboss.org
> <mailto:hibernate-users@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@lists.jboss.org
> <mailto:hibernate-users-bounces@lists.jboss.org>
> [mailto:hibernate-users-bounces@lists.jboss.org] *On Behalf Of *Eric
> Kolotyluk
> *Sent:* Sunday, August 14, 2011 5:11 PM
> *To:* hibernate-users(a)lists.jboss.org
> <mailto:hibernate-users@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
>
13 years, 4 months
- 1
- 0
Re: [hibernate-users] Newbie Performance Questions
by 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@lists.jboss.org
> [mailto:hibernate-users-bounces@lists.jboss.org] *On Behalf Of *Eric
> Kolotyluk
> *Sent:* Sunday, August 14, 2011 5:11 PM
> *To:* hibernate-users(a)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
>
13 years, 4 months
- 1
- 0
Newbie Performance Questions
by Eric Kolotyluk
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
13 years, 4 months
- 1
- 0
- ← Newer
- 1
- Older →