Hi!
I was a little confused when tow quite similar Criteria queries differed so much in performance.
Simplifying my model is as follow:
Element contain many PropertyValue (properties)
PropertyValue contain an AbstractProperty representing the type (property)
PropertyValue contain one Element representing the owner and one Element representing the value
PropertyValue contain the AbstractProperty definition
CRITERIA 1
Code:
session.createCriteria(Element.class).
add(Restrictions.gt("left", 2).
add(Restrictions.lt("right", 10)
.createAlias("properties", "props").
.createAlias("props.property", "prop")
.add(Restrictions.eq("prop.url", "paper:author"))
.createAlias("props.value", "vl")
.add(Restrictions.like("vl.name", (char) i + "%"))
.list();
CRITERIA 2
Code:
session.createCriteria(PropertyValue.class)
.setProjection(Projections.property("owner"))
.createAlias("property", "prop")
.add(Restrictions.eq("prop.url", "paper:author"))
.createAlias("value", "vl")
.add( Restrictions.like("vl.name", (char) i + "%"))
.createCriteria("owner")
.add(Restrictions.gt("left", 2)
.add(Restrictions.lt("right", 10) .list();
The two criterias return the same results and generates the following SQLs:
CRITERIA 1
Code:
select
this_.url as url0_3_,
this_.name as name0_3_,
this_.instance as instance0_3_,
this_.description as descript4_0_3_,
this_.insertedOn as insertedOn0_3_,
this_.rate as rate0_3_,
this_.parentID as parentID0_3_,
this_.lft as lft0_3_,
this_.rgt as rgt0_3_,
propertyva1_.id as id2_0_,
propertyva1_.elementID as elementID2_0_,
propertyva1_.propertyID as propertyID2_0_,
propertyva1_.valueID as valueID2_0_,
propertyva1_.primitiveValue as primitiv5_2_0_,
prop2_.url as url1_1_,
prop2_.name as name1_1_,
prop2_.inverseName as inverseN4_1_1_,
prop2_.elementID as elementID1_1_,
prop2_.domain as domain1_1_,
prop2_.type as type1_1_,
prop2_.minRange as minRange1_1_,
prop2_.maxRange as maxRange1_1_,
prop2_.propertyType as property2_1_1_,
vl3_.url as url0_2_,
vl3_.name as name0_2_,
vl3_.instance as instance0_2_,
vl3_.description as descript4_0_2_,
vl3_.insertedOn as insertedOn0_2_,
vl3_.rate as rate0_2_,
vl3_.parentID as parentID0_2_,
vl3_.lft as lft0_2_,
vl3_.rgt as rgt0_2_
from
element this_
inner join
property_value propertyva1_
on this_.url=propertyva1_.elementID
inner join
abstract_property prop2_
on propertyva1_.propertyID=prop2_.url
inner join
element vl3_
on propertyva1_.valueID=vl3_.url
where
this_.lft>?
and this_.rgt<?
and prop2_.url=?
and vl3_.name like ?
CRITERIA 2
Code:
select
this_.elementID as y0_
from
property_value this_
inner join
element element3_
on this_.elementID=element3_.url
inner join
abstract_property prop1_
on this_.propertyID=prop1_.url
inner join
element vl2_
on this_.valueID=vl2_.url
where
prop1_.url=?
and vl2_.name like ?
and element3_.lft>?
and element3_.rgt<?
The firts criteria is 4 times slower than the second, so I realized the first criteria returns lots of properties, so it must be trying to build lots of objects. The rest of the SQL is pretty much the same.
How do I make the first query return only the Element and forget about all the other entities that are joined in the criteria? Is that really the problem????
Thanks!
Login
FAQ
Search