Re: Adhoc query v. PL/SQL

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

Richard Hennessy wrote in message <35E3CF50.3BF9A688_at_mail.dec.com>...

snip

>
>Now to the problem. There is no performance benefit in executing the
>procedure and fetching the cursor
>compared to doing this using an adhoc query (SELECT * FROM A WHERE B =
>:1. At best the speed
>is the same. If I run the procedure using SQL_TRACE = TRUE, I see that
>the contents of SEL get
>parsed. Do stored procedures not get parsed once, when they get created
>? Or do they get parsed
>every time they get called ?
>
>Can anyone offer an explanation as to why there is no performance
>benefit in using the stored procedure ?
>
>(The Oracle version is 7.3.3 and statements are called from an OCI
>program).
>
>thanks in advance
>
>Richard Hennessy
>
>
>
Received on Wed Aug 26 1998 - 18:16:03 CDT