<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
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:
<blockquote
cite="mid:FA54083CC7846B4DA1C0C5741E1C16D66DCC7B4BEC@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-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 l1
{mso-list-id:1255749180;
mso-list-template-ids:-1471805262;}
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">Are
the Library’s colors lazily loaded? You could be
encountering an N+1 selects issue.<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">
<a class="moz-txt-link-abbreviated" href="mailto:hibernate-users-bounces@lists.jboss.org">hibernate-users-bounces@lists.jboss.org</a>
[<a class="moz-txt-link-freetext" 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 class="moz-txt-link-abbreviated" href="mailto:hibernate-users@lists.jboss.org">hibernate-users@lists.jboss.org</a><br>
<b>Subject:</b> [hibernate-users] Newbie Performance
Questions<o:p></o:p></span></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 lfo1">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 lfo1">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 lfo1">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 lfo1">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 lfo1">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 lfo1">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 lfo2">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 lfo2">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 lfo2">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 lfo2">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 lfo2">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 lfo2">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>