Обсуждение: parameterized query much much slower than one with hard-coded string
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;
On 10 November 2011 17:44, Timbo Chen <timbo@powerreviews.com> wrote: > what could be the possible reason that these 2 nearly identical queries have > such different performance? > different execution plans? Yes. > how would i find out what the actual execution > plan was? EXPLAIN ANALYZE? Oliver
On 10 November 2011 21:30, Timbo Chen <timbo@powerreviews.com> wrote: > i did do an explain analyze on the query by running the query directly on > the database. > just wondering if there was a way to get the execution plan for the prepared > statement. Run the EXPLAIN ANALYZE via a prepared statement that's parameterized in the same way. You get the output back as a separate resultset IIRC Oliver
i did do an explain analyze on the query by running the query directly on the database.
just wondering if there was a way to get the execution plan for the prepared statement.
On Wed, Nov 9, 2011 at 9:57 PM, Oliver Jowett <oliver@opencloud.com> wrote:
On 10 November 2011 17:44, Timbo Chen <timbo@powerreviews.com> wrote:> different execution plans?
> what could be the possible reason that these 2 nearly identical queries have
> such different performance?
Yes.EXPLAIN ANALYZE?
> how would i find out what the actual execution
> plan was?
Oliver
Timbo Chen <timbo@powerreviews.com> writes:
> just wondering if there was a way to get the execution plan for the
> prepared statement.
Prepare an equivalently-parameterized statement by hand:
PREPARE foo(int,text,...) AS ...
and then do EXPLAIN [ANALYZE] EXECUTE foo(...)
regards, tom lane