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

pete_at_mynix.org wrote in message news:<1049736335.55144.0_at_iapetus.uk.clara.net>...
> Hello, I was doing some experiments in Oracle 9i (9.0.3 to be precise)
> and Java stored procedures.
>
> Formerly (oracle 8) these were very slow, it seemed no JIT was used.
>
> Now I was pleased to note that Java code executed quite fast, clearly
> faster than PL/SQL. I made some compute intensive procedures both in
> PL/SQL and in Java to test this out.
>
> 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!
>
> Is this normal?!? It is like Java executes fast, faster than PL/SQL,
> but somehow the barrier between the SQL engine and Java engine is much
> larger than between SQL and PL/SQL.
>
> Is there any option/configuration possible to make Java perform just
> like PL/SQL when used in this manner?
>
> I was hoping to finally switch 100% to Java ISO PL/SQL, but this seems
> to be impossible still :(

Doesn't surprise me this, it's due to the SQL-oriented nature of what the JSP is trying to do. There's no better / more efficient language for handling SQL than PL/SQL.

See

http://asktom.oracle.com/pls/ask/f?p=4950:8:13851321981717055::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:883929178230,

for a great response by Tom Kyte on this subject. Received on Tue Apr 08 2003 - 05:30:00 CDT