| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Are stored procedures faster than straight SQL?
A copy of this was sent to "Jerry Gitomer" <jgitomer_at_ictgroup.com>
(if that email address didn't require changing)
On Tue, 13 Oct 1998 12:38:08 -0400, you wrote:
>Hi Tom,
>
> I was under the impression that once a dynamic sql query was parsed it
>was held in the SGA and was not parsed again unless the SGA was flushed or,
>based on some type of least recently used algorithm, the query was flushed.
>If that is the case the only difference in performance would be on the first
>execution of the query.
>
that is true and its true for PL/SQL as well.
PL/SQL is stored parsed and compiled, this is true HOWEVER -- the sql contained within pl/sql is stored as text. It gets parsed and optimized the first time it is used (and then get reused over and over and over by that pl/sql routine as its used by other people)....
So, in this respect -- using SQL in pl/sql -- you mandate that query parse trees will be reused as efficiently as possible. you don't worry that each client application uses the same exact query text -- pl/sql does that for you.
As to which is more efficient over all the basic answer is.... YMMV - it will be different depending on the circumstances. Is there extra cpu power on the server? if yes, pl/sql might be faster, if no, sql with a client elsewhere processing the results might be faster. Is the network a bottleneck? if yes, pl/sql might be faster, else sql might be faster. And so on and so on and so one.
This is one of the questions that has no "yes/no" answer....
> This raises another issue -- is the time savings significant enough to
>matter?
>
>regards
>
>Jerry
>
>
>tomscott_at_nospam.abac.com wrote in message
><3623695d.89139016_at_news1.abac.com>...
>>> I am "but a newbie" to the world of Oracle and am wondering if Stored
>Procedures are faster than sending a straight line SQL command to query an
>Oracle 7.3.4 database and why?
>>>
>>>Much appreciated,
>>>Sean Dolan
>>
>>In general, yes. A stored procedure is already compiled and parsed,
>>whereas a dynamic query needs to go through that process. However, for
>>a small, simple query, that might only take milliseconds. The
>>difference really comes into play when, for example, you have to call
>>the query over and over again from within a loop.
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Oct 13 1998 - 12:17:51 CDT
 |
 |