Re: Stored procedure speed

A copy of this was sent to DM <amd_at_spam_spam.idirect.com> (if that email address didn't require changing) On Wed, 28 Jul 1999 12:29:46 -0400, you wrote:

>Hello everybody,
>I wanted to speed up a sql statement execution so I embedded it in a
>stored procedure and the result set is passed through a ref cursor to
>the client. I got no speed improvement. Does anyone know more about
>this? I mean, the general wisdom is that a stored procedure will give
>you a 20% increase in speed upfront because the sql is already parsed,

where did you read that?

>the datapath set up. It seems that it's not the case. The sql is a join
>and outer join of 4 tables with a parameter and it retrieves 899 rows.
>Thanks,
>Max Mera
>

the SQL in a stored procedure is stored in its textual format when a procedure is compiled. The first time the procedure executes that sql statement after the database is started, it gets parsed and compiled into the shared sql area and reused over and over.

it is not parsed and optimized at compile time -- this always happens at run time. It may happen very quickly if someone else ran the procedure before you did.

That aside -- putting a sql query into a stored procedure in general will not speed up its execution, especially if you are fetching from it on a client.

to speed up the query on the client have you:

• implemented array fetching? array fetch 100 rows at a time, massive (typically 7times or more) increase in throughput then a single row fetch.
• traced the query to see how it is being executed and whether you can rewrite it to be more efficient

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 28 1999 - 12:23:11 CDT