Re: Adhoc query v. PL/SQL

Hi Jerry

Thanks for the reply. The procedure I showed is just one example. The candicate sets are mostly small, with a handful having about 4000 rows. What I am doing is I have replaced each adhoc statement by a stored procedure, so now instead of calling say 20 adhoc statements I call 20 stored procedures. I am aware that it would be better to perform more logic in the procedures themselves and to call fewer of them but my project wouldn't survive such a re-design. However, even if I create the procedures
as part of a package body, my tests have consistently shown that adhoc is faster than PL/SQL (again, probably because I don't have more comprehensive procedure code).

What I'm looking for is confirmation that what I am seeing is correct, or is there maybe something I could still do to speed the whole thing up ?

regards and thanks

Richard Hennessy

Jerry Gitomer wrote:

> Hi Richard,
>
> How big is the candidate set in the table you are selecting from?
> The
> reason I ask is that if there are a lot of rows to evaluate and select
> from
> you should not expect to see any significant difference in performance
> since
> the parse of the select statement is relatively quick.
>
> regards
>
> Jerry
>
>
Received on Thu Aug 27 1998 - 02:47:32 CDT