what could be the possible reason that these 2 nearly identical queries have such different performance?
1) only difference is that one query has a named parameter replaced with a hard-coded string
2) one without hard coded string takes about 22-30 secs to run! query with hard coded string only takes about 22-100 millisecs.
different execution plans? how would i find out what the actual execution plan was?
thanks,
tim
Hibernate:
SELECT r.*
FROM review_extra re, review r, product p, category_model cm, tmp_category_ancestor tca
WHERE tca.parent_category_id = :categoryId
AND cm.category_id = tca.child_category_id
AND cm.model_id = p.model_id
AND r.product_id = p.product_id
AND re.review_id = r.review_id
AND re.key = :rotdKey
LIMIT 1
Hibernate:
SELECT r.*
FROM review_extra re, review r, product p, category_model cm, tmp_category_ancestor tca
WHERE tca.parent_category_id = :categoryId
AND cm.category_id = tca.child_category_id
AND cm.model_id = p.model_id
AND r.product_id = p.product_id
AND re.review_id = r.review_id
AND re.key = 'review_of_the_day'
LIMIT 1
String sql = "SELECT r.*\n" +
"FROM review_extra re, review r, product p, category_model cm, tmp_category_ancestor tca\n" +
"WHERE tca.parent_category_id = :categoryId\n" +
"AND cm.category_id = tca.child_category_id\n" +
"AND cm.model_id = p.model_id\n" +
"AND r.product_id = p.product_id\n" +
"AND re.review_id = r.review_id\n" +
// "AND re.key = :rotdKey\n" +
"AND re.key = '" + ReviewExtra.KEY_ROTD + "'\n" +
"LIMIT 1";
SQLQuery query = HibernateUtil.getSession().createSQLQuery(sql);
query.setLong("categoryId", categoryId);
// query.setString("rotdKey", ReviewExtra.KEY_ROTD);
query.addEntity(Review.class);
Review review = (Review)query.uniqueResult();
return review;