Re: PreparedStatement Performance

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Tue, 27 Feb 2007 19:47:56 +0100
Message-ID: <es1ual$q3s$1@news3.zwoll1.ov.home.nl>

John.Planque_at_googlemail.com schreef:

>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT MODE: ALL_ROWS
> 0 SORT (ORDER BY)
Why all_rows? Are you using Java as a reporting tool?

And I would suspect a rewrite of:

SELECT id,code,startsat,endswith,ref
FROM
 PRDS WHERE startsat>= :1 AND ord = -1 AND id IN (SELECT id FROM   PRJS WHERE proj = :2) ORDER BY id,code

To:

SELECT prds.id

, prds.code
, prds.startsat
, prds.endswith
, prds.ref

FROM
 PRDS, PRJS

 WHERE prds.startsat>= :1
 AND   prds.ord = -1
 AND   prds.id = prjs.id
 and   prjs.proj = :2

ORDER BY id,code

would be faster - in lists tend to be slow, especially when they grow - which depends on the value of bind variable :2 (which you did not change, so this is off topic...) Depending on the definition, the join on id may need an outer join.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Tue Feb 27 2007 - 12:47:56 CST