Postgres Pro
Downloads
Home   >   mailing lists

Thread: parameterized query much much slower than one with hard-coded string

parameterized query much much slower than one with hard-coded string

From
Timbo Chen
Date:
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;

Re: parameterized query much much slower than one with hard-coded string

From
Oliver Jowett
Date:
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

Re: parameterized query much much slower than one with hard-coded string

From
Oliver Jowett
Date:
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

Re: parameterized query much much slower than one with hard-coded string

From
Timbo Chen
Date:
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:
> 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

Re: parameterized query much much slower than one with hard-coded string

From
Tom Lane
Date:
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