Re: java stored procedures fast, but slow when called as SQL function

On 07 Apr 2003 19:25:33 +0200, pete_at_mynix.org wrote:

>Then I was dissapointed by the following: I made a very simple stored
>procedure (one which just returns the duplicated string/varchar as its
>single arument) and called it many times from an SQL statement:
>
>select count(distinct duplicate(some_col)) from some_tab
>
>some_tab has about 50000 records.
>
>The select count(distinct some_col) directly returns almost
>immediately (0.3s).
>
>The above statement when duplicate() is a PL/SQL function returns in
>1.3s. When duplicate() is a Java function it takes over 6s!

When calling a function like this in a SQL query, generally it's a good idea to build a function-based index. This needs a bit of setup with privileges and initialization parameters. I've done this with PL/SQL functions, although not with Java functions. PL/SQL has the DETERMINISTIC keyword in the function declaration, which asserts that the function always returns the same value given the same input (which might help speed up picking out the distinct return values). Don't know if there's an equivalent assertion in Java.

PL/SQL pays a penalty with number-crunching precisely because it is integrated so much with SQL. Operations on the NUMBER datatype involve library routines. Even INTEGER is just a floating-point number with nothing after the decimal point. PLS_INTEGER is a better choice for doing integer arithmetic in PL/SQL.

As you mentioned yourself, and as others have pointed out, both Java and PL/SQL have improved in performance lately. Who knows what the future holds...

John

--
Photo gallery: http://www.pbase.com/john_russell/