Re: Efficiency of Procedures/Functions Over In-line Code ?

Thomas Kyte wrote:
>
> A copy of this was sent to GHouck <hksys_at_teleport.com>
> (if that email address didn't require changing)
> On Wed, 30 Dec 1998 16:09:12 -0800, you wrote:
>
> >If I encapsulate a common SQL task in a Function, I find that
> >it is MUCH less efficient than performing the same with inline
> >code; is this normal behavior? Is there a way to make it more
> >efficient? Is that a trade-off for modularity?
> >
>
> Inline code is *always* faster then a procedure call in ANY language (eg: C++
> has an 'inline' directive for performance, it tells the compiler to put the code
> in place of a function call when building the object code, this increases
> performance by reducing the overhead of a procedure call, setting up the stack
> for the procedure, etc).
>
> In your example, the rtrim/ltrim/lower functions are 'builtins'. There is very
> little overhead to calling them. They are written in C and are very fast. When
> you wrapped the rtrim/ltrim/lower calls in pl/sql, you caused a recursive SQL
> call to be made for each and every row you updated. The overhead of this
> recursive call is somewhat high. For each row updated, a pl/sql procedure is
> begin bound to, invoked, interpreted, and results returned.
>
> I find calling pl/sql from SQL to be useful in typically 3 cases:
>
> 1.) I want to create a parameterized view. I will create a view that looks
> something like:
>
> create or replace view my_view
> as
> select * from some_table
> where some_column = ( select MY_FUNCTION from dual );
>
> What we did in this case was to make it such that MY_FUNCTION gets called ONCE
> per query instead of one per row per query. This is very fast and you don't
> even realize that you are calling pl/sql from sql.
>
> 2.) When I want to perform a function f(x) on a couple of rows, many of the rows
> won't call this function and I can use decode or something to short circut the
> evaluation.
>
> Lets say you have lots of data in a table. There is a 'security' column. 90%
> of the rows have NULL in this column (public access). 10% don't. For those 10%
> you need to run some pl/sql to do some tricky 'thinking' about whether the
> current user can see this row or not. You might create a secure view that looks
> like:
>
> create or replace view my_view
> as
> select * from my_table
> where decode( some_column, NULL, 1, my_security_check(some_column) ) = 1
>
> Now, you just need to write a function my_security_check that returns 1 or 0.
> When a non-null row is encountered, this function is called. If the row is NULL
> for that column, decode will answer the question quickly. This is fast as well.
>
> 3.) when you have a reasonable sized result set and want to format columns
> nicely for the requesting application. reasonable is in the eye of the
> beholder. Since the pl/sql function is getting called row by row for the RESULT
> set -- you typically don't notice the overhead since you fetch 10 rows, display
> them to the user, fetch 10 more. the overhead of calling out to pl/sql is
> spread over many fetches -- the user doesn't wait for them all to be called (as
> they did in your update). So, a function like:
>
> select my_function(x,y,z), some_other_columns
> from T
> where <some_where_clause>
>
> that returns a reasonable total number of rows, or is used by a client server
> application that "pages" through the result set (eg: like a forms default block
> would, fetching 10-15 rows at a time) works great too.
>
> there might be others then the 3 above, but they are the most common. If you
> want to encapsulate the functionality of ltrim/rtrim/lower for that update, you
> might consider encapsulating the entire update itself into a procedure that uses
> ltrim/rtrim/lower in the sql statement. then let the people call the procedure
> and the procedure hides even the update and all.
>
> >For example, if I want to trim and change the case of a column,
> >I might use:
> >
> > UPDATE MYTABLE SET COL1 = RTRIM(LTRIM(LOWER(COL1)))
> >
> >And it finishes (e.g., 6500 rows) in < 20 secs.
> >
> >If I use instead the following Function:
> >
> > FUNCTION TEST ( INDATA CHAR )
> > RETURN CHAR IS
> > BEGIN
> > RETURN LTRIM(RTRIM(LOWER(INDATA)));
> > END TEST;
> >
> >Then issue:
> >
> > UPDATE MYTABLE SET COL1 = TEST(COL1)
> >
> >It takes approximately 3-4 minutes, which is quite a change
> >in performance.
> >
> >Is there something wrong in the definition of my Function, or
> >its use? Perhaps my configuration? I am using Oracle 7.3.3 .
> >
> >Thanks much for any help,
> >
> >Geoff Houck
> >systems hk
> >hksys_at_teleport.com
> >http://www.teleport.com/~hksys
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>

Thomas,

Thank you very much for your response (and to others, as well). You've given me (us?) a lot to digest, and I appreciate the detail. I was comfortable with the idea that 'inline' or built-in functions probably operate on 'sets' of data, besides being more efficient in their own right. I guess I was surprised by the difference in performance between them and the procedure calls.

I will put more effort into creating parameterized procedures to operate flexibly on masses of data, as you seem to be suggesting. Sometimes you wish you knew everything there is to know, but then reality strikes!

Thanks again,

Geoff Houck

systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Thu Dec 31 1998 - 00:00:00 CST