Hi,
I'm using hibernate 3.1
inside my program i was trying to make a select using hibernate createQuery. the query took around 140 MS. when i made the same select using JDBC connection, the query returned 16MS. the same time it takes in the MySQL server to run it.

Is there a way to make hibernate run faster ?

I can make the query predefined, because i'm building it on the fly...
The object of location is not changed during the run.. When I use JDBC connection, I just fetch all the relevant fields and then use regular constractor (as far as i understand, this is what hibernate does)

this is the query :
String sql = "from Locations l where l.locationSetID= :locationSetID and (
locationPattern='185021' OR locationPattern='18502' OR locationPattern='1850' OR locationPattern='185' OR locationPattern='18' OR locationPattern='1' OR locationPattern='' )
ORDER BY LENGTH(locationPattern) DESC";

Locations location = (Locations)session.createQuery(sql).setInteger("locationSetID", locsetId).setMaxResults(1).uniqueResult();


the xml mapping is:
<hibernate-mapping>
<class name="com.kayote.DBObjects.Locations" table="locations"
where="locationIsActive=1" mutable="false">

<id name="locationId" type="int">
<column name="locationID" />
<generator class="assigned" />
</id>

<property name="locationgroups" type="int" insert="false" update="false">
<column name="locationGroupID" not-null="true">
</column>
</property>
<property name="locationPattern" type="string">
<column name="locationPattern" length="32" not-null="true">
</column>
</property>
<property name="locationSetID" type="int">
<column name="locationSetID" not-null="true">
</column>
</property>
</class>
</hibernate-mapping>

I'm using MySQL 4
the table is:
CREATE TABLE `locations` (
`locationID` int(11) unsigned NOT NULL auto_increment,
`locationPattern` varchar(32) NOT NULL default '',
`locationGroupID` mediumint(8) unsigned NOT NULL default '0',
`locationSetID` mediumint(8) unsigned NOT NULL default '0',
`locationDesc` varchar(64) default NULL,
`locationIsActive` tinyint(4) NOT NULL default '1',
`locationName` varchar(64) default NULL,
`locationCountryID` mediumint(8) unsigned default NULL,
`locationModifyTS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`locationID`),
UNIQUE KEY `locationPhoneCode` (`locationPattern`,`locationSetID`),
KEY `locationGroupID` (`locationGroupID`),
KEY `locationSetID` (`locationSetID`),
KEY `locationCountryID` (`locationCountryID`),
CONSTRAINT `locations2_ibfk_1` FOREIGN KEY (`locationGroupID`) REFERENCES `locationgroups` (`lgID`),
CONSTRAINT `locations2_ibfk_2` FOREIGN KEY (`locationSetID`) REFERENCES `locationsets` (`locsetID`),
CONSTRAINT `locations2_ibfk_3` FOREIGN KEY (`locationCountryID`) REFERENCES `countries` (`countryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Thanks for any help

I wouldn't be surprised if your generated SQL contains SELECT DISTINCT. Try turning on show SQL (property hibernate.show_sql should be set to true in hibernate.cfg.xml) and looking at the generated SQL. My bet is you're making an apples to oranges comparison. Run the generated SQL on mysql and the run times should be close.

You don't provide any details on how you're getting the amount of time it takes to run the query. You will want to ensure a large enough sample is being taken to measure SQL run time and also be careful about what you're measuring - hibernate uses a JDBC connection that you specified in hibernate.cfg.xml so it's not accurate to say the issue is with hibernate running the SQL vs. a straight JDBC connection. The amount of time taken to execute the SQL through hibernate vs a JDBC connection should be exactly - all elements would be equivalent - driver, network connection, SQL...that leads me to conclude you're not running the same SQL.

Does that make sense?

the select generated in both cases is :
select
locations0_.locationID as locationID42_,
locations0_.locationGroupID as location2_42_,
locations0_.locationPattern as location3_42_,
locations0_.locationSetID as location4_42_
from
locations locations0_
where
(
locations0_.locationIsActive=1
)
and (
(
locations0_.locationSetID=?
)
and(
(
locationPattern='185021'
)
OR(
locationPattern='18502'
)
OR(
locationPattern='1850'
)
OR(
locationPattern='185'
)
OR(
locationPattern='18'
)
OR(
locationPattern='1'
)
OR(
locationPattern=''
)
)
)
order by
LENGTH(locationPattern)DESC limit ?

when the first param is the set ID i give, and the second is 1 (from setMaxResults(1) ).

this is the sql I check against directly in mysql and JDBC. in those case the sql return after 16 ms.
about time messure - i print a timestamp before and after the session call (or connection call in JDBC case)