<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
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 :-)<br>
<br>
String hql = "SELECT new
com.kodak.vip.color.repository.SpotColor("<br>
+ "color.name, "<br>
+ "representationLAB.coordinates.l,
"<br>
+ "representationLAB.coordinates.a,
"<br>
+ "representationLAB.coordinates.b)
"<br>
+ "FROM "<br>
+ "Library as library join
library.colors as lc, "<br>
+ "Color as color join
color.libraries as cl join color.representations as cr, "<br>
+ "Representation as representation
join representation.colors as rc, "<br>
+ "RepresentationLAB as
representationLAB "<br>
+ "WHERE "<br>
+ "library.name = '" + getName() +
"' and "<br>
+ "library.id = cl.id and "<br>
+ "color.id = lc.id and "<br>
+ "color.id = rc.id and "<br>
+ "cr.id = representation.id and "<br>
+ "representation.id =
representationLAB.id";<br>
<br>
Query query =
hibernate.getCurrentSession().createQuery(hql);<br>
<br>
return query.list();<br>
<br>
<br>
On 2011-08-14 9:29 PM, Haswell, Joe wrote:
<blockquote
cite="mid:FA54083CC7846B4DA1C0C5741E1C16D66DCC7B4BFF@GVW1341EXA.americas.hpqcorp.net"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
<meta name="Generator" content="Microsoft Word 12 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:611473499;
mso-list-template-ids:-890472104;}
@list l0:level1
{mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level4
{mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level7
{mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1
{mso-list-id:1255749180;
mso-list-template-ids:-1471805262;}
@list l1:level1
{mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level2
{mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level3
{mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level4
{mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level5
{mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level6
{mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level7
{mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level8
{mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level9
{mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l2
{mso-list-id:1714649938;
mso-list-template-ids:-2048651518;}
@list l3
{mso-list-id:1814903760;
mso-list-template-ids:-860716116;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><br>
Best!<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF
1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">From:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">
Eric Kolotyluk [<a class="moz-txt-link-freetext" href="mailto:eric.kolotyluk@gmail.com">mailto:eric.kolotyluk@gmail.com</a>] <br>
<b>Sent:</b> Sunday, August 14, 2011 10:22 PM<br>
<b>To:</b> Haswell, Joe<br>
<b>Subject:</b> Re: [hibernate-users] Newbie Performance
Questions<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">OK, please see the attachments.<br>
<br>
I am not really understanding how HQL transforms into SQL, as
it was easy for me to write the SQL<br>
<br>
Cheers, Eric<br>
<br>
On 2011-08-14 9:14 PM, Haswell, Joe wrote: <o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Can
you include your entities? </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Thanks,
<br>
<br>
<br>
</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Joe
H. | HP Software</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #B5C4DF
1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">From:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">
<a moz-do-not-send="true"
href="mailto:hibernate-users-bounces@lists.jboss.org">hibernate-users-bounces@lists.jboss.org</a>
[<a moz-do-not-send="true"
href="mailto:hibernate-users-bounces@lists.jboss.org">mailto:hibernate-users-bounces@lists.jboss.org</a>]
<b>On Behalf Of </b>Eric Kolotyluk<br>
<b>Sent:</b> Sunday, August 14, 2011 9:45 PM<br>
<b>To:</b> <a moz-do-not-send="true"
href="mailto:hibernate-users@lists.jboss.org">hibernate-users@lists.jboss.org</a><br>
<b>Subject:</b> Re: [hibernate-users] Newbie Performance
Questions</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">I'm not sure, I' just using whatever the
defaults are.<br>
<br>
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.<br>
<br>
SELECT<br>
"Color"."name",<br>
"RepresentationLAB".coordinates_l, <br>
"RepresentationLAB".coordinates_b, <br>
"RepresentationLAB".coordinates_a<br>
FROM <br>
"ColorRepository"."Library",<br>
"ColorRepository"."Library_Color", <br>
"ColorRepository"."Color", <br>
"ColorRepository"."Color_Representation", <br>
"ColorRepository"."RepresentationLAB"<br>
WHERE <br>
"RepresentationLAB"."id" = "Color_Representation"."rightID"
and<br>
"Color_Representation"."leftID" = "Color"."id" and<br>
"Color"."id" = "Library_Color"."rightID" and<br>
"Library_Color"."leftID" = "Library"."id" and<br>
"Library"."name" = 'PANTONE Goe coated'<br>
<br>
The HQL query I am trying follows, but when I run it nothing
is returned<br>
<br>
String hql = "SELECT new
com.kodak.vip.color.repository.SpotColor("<br>
+ "colors.name, "<br>
+
"representationLAB.coordinates.l, "<br>
+
"representationLAB.coordinates.a, "<br>
+
"representationLAB.coordinates.b) "<br>
+ "FROM "<br>
+ "Library as library join
library.colors as colors, "<br>
+ "Color as color join
color.representations as representations, "<br>
+ "RepresentationLAB as
representationLAB "<br>
+ "WHERE "<br>
+ "representationLAB.id =
representations.id and "<br>
+ "representations.id =
color.id and "<br>
+ "color.id = colors.id and "<br>
+ "colors.id = library.id and
"<br>
+ "library.name = '" +
getName() + "'";<br>
<br>
Query query =
hibernate.getCurrentSession().createQuery(hql);<br>
return query.list();<br>
<br>
Which generates the following SQL<br>
<br>
Hibernate: <br>
select<br>
color2_."name" as col_0_0_,<br>
representa6_."coordinates_l" as col_1_0_,<br>
representa6_."coordinates_a" as col_2_0_,<br>
representa6_."coordinates_b" as col_3_0_ <br>
from<br>
"ColorRepository"."Library" library0_ <br>
inner join<br>
"ColorRepository"."Library_Color" colors1_ <br>
on library0_."id"=colors1_."leftID" <br>
inner join<br>
"ColorRepository"."Color" color2_ <br>
on colors1_."rightID"=color2_."id" cross <br>
join<br>
"ColorRepository"."Color" color3_ <br>
inner join<br>
"ColorRepository"."Color_Representation" representa4_
<br>
on color3_."id"=representa4_."leftID" <br>
inner join<br>
"ColorRepository"."Representation" representa5_ <br>
on representa4_."rightID"=representa5_."id" cross
<br>
join<br>
"ColorRepository"."RepresentationLAB" representa6_ <br>
inner join<br>
"ColorRepository"."Representation" representa6_1_ <br>
on representa6_.id=representa6_1_."id" <br>
where<br>
representa6_.id=representa5_."id" <br>
and representa5_."id"=color3_."id" <br>
and color3_."id"=color2_."id" <br>
and color2_."id"=library0_."id" <br>
and library0_."name"='PANTONE Goe coated'<br>
<br>
Cheers, Eric<br>
<br>
On 2011-08-14 5:00 PM, Haswell, Joe wrote: <o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Are
the Library’s colors lazily loaded? You could be
encountering an N+1 selects issue.</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #B5C4DF
1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">From:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">
<a moz-do-not-send="true"
href="mailto:hibernate-users-bounces@lists.jboss.org">hibernate-users-bounces@lists.jboss.org</a>
[<a moz-do-not-send="true"
href="mailto:hibernate-users-bounces@lists.jboss.org">mailto:hibernate-users-bounces@lists.jboss.org</a>]
<b>On Behalf Of </b>Eric Kolotyluk<br>
<b>Sent:</b> Sunday, August 14, 2011 5:11 PM<br>
<b>To:</b> <a moz-do-not-send="true"
href="mailto:hibernate-users@lists.jboss.org">hibernate-users@lists.jboss.org</a><br>
<b>Subject:</b> [hibernate-users] Newbie Performance
Questions</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">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.<br>
<br>
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.<br>
<br>
My JPA framework looks something like<br>
<br>
@Entity Library - m2m - @Entity Color - m2m - @Entity
Representation (i.e. L*a*b*)<br>
<br>
In my application, loading from a PostgreSQL database via
Hibernate the same data takes<o:p></o:p></p>
<ol start="1" type="1">
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0
level1 lfo3">20 seconds to load with full logging enabled<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0
level1 lfo3">16 seconds to load with logging disabled<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0
level1 lfo3">4 seconds to reload a second time (probably due
the default caching behavior)<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0
level1 lfo3">0.2 seconds from via SQL from PostgreSQL<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0
level1 lfo3">0.2 seconds to load from Castor<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0
level1 lfo3">0 seconds (usually) if I use SoftReferences to
cache the data in memory<o:p></o:p></li>
</ol>
<p class="MsoNormal">Some of my questions are:<o:p></o:p></p>
<ol start="1" type="1">
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1
level1 lfo6">Is the performance I am seeing typical? Is it
what most people would agree to expect from using Hibernate
with a DBMS like PostgreSQL?<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1
level1 lfo6">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.<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1
level1 lfo6">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.<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1
level1 lfo6">Am I making some lame newbie mistake somewhere,
and what would that mistake be?<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1
level1 lfo6">Should I be formulating an appropriate HQL
statement, similar to my SQL statement, and loading things
that way?<o:p></o:p></li>
<li class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1
level1 lfo6">Is there some documentation on using Hibernate
with Graphical User Interfaces, or some best practices
documentation somewhere?<o:p></o:p></li>
</ol>
<p class="MsoNormal">Cheers, Eric<o:p></o:p></p>
</div>
</blockquote>
</body>
</html>