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

See comments inline.

pete_at_mynix.org wrote in news:1049912746.28535.0_at_despina.uk.clara.net:

> Noons <wizofoz2k_at_yahoo.com.au.nospam> writes:
>
>> And with SQL access, best case about 40 times
>> slower. Not 6. V8.1.7. As far as calling
>> Java functions from SQL like you tried, I haven't
>> even thought of doing it. What can I say?
>
> Aha, 8.1.7. That is a big difference with 9i. In 9i there seems to be
> a JIT, in 8i there was none. My measurements of pure java speed
> indicate that it is about 10 times faster in 9i.
>

In 9i there is also native PL/SQL compilation which uses the OS to run a binary and removes the need for an interpreter. I haven't used it but I should imagine that this is faster than in 8i too.

>>
>> I can. It's called the JVM. There is no such animal
>> in Oracle's SQL layer. Nor should there be one.
>> As long as Java relies on a JVM to execute (just about
>> no way this will ever change...), it won't be possible
>> to tightly integrate the two. Or else, ask Intel to
>> create a CPU with very, very fast context switching...
>
> What do you think PL/SQL depends on? It is interpreted, not even
> truely compiled. PL/SQL also depends on some run time interpreter, the
> only difference is that they don't call it "virtual machine".
>

Again, see compiled PL/SQL in 9i.

>> >
>> > But aparently they discourage its use in-built. Thats new for me.
>> >
>>
>> I'd suggest getting used to it... ;)
>
> I'm used to it since 5 years. But I keep feeling extremely limited in
> this archaic pascal-like language. Building data structures with
> VARRAYS and nested tables or index-by tables is plain horror.
>

Why would you do that anyway? You can use PL/SQL for what it is good at, getting and writing data and do the fiddly stuff in Java if its faster. Why the need for a single language that does everything?

Most of the time people are messing around with arrays it could probaly have been done simpler and faster in SQL anyway.

>>
>> The writing is on the wall: beans and all the other
>> J2EE stuff have been removed from the db JVM. Only basic
>> Java functionality is still there.
>> Largely for the benefit of those who have written a lot
>> with it. And of those things that just cannot possibly
>> be done with PL/SQL. Like sending e-mails from the db! ;)
>
> Can be done with PL/SQL :).
> Just look in the description of the utl_tcp package where there is an
> example of how to send mail.
>

Yes but some functionality like sending attachments is hell with utl_smtp, and a breeze with javamail. Again if the functionality already exists why re-invent it?

You could also look at it another way and say if PL/SQL is deficient in some aspects of performance, and Oracle have had it around for a lot longer than Java, couldn't they have addressed those thereby doing away with the need to use Java? Obviously not, with possibly the exception of native compilation, and I don't see why they should either.

Martin Received on Wed Apr 09 2003 - 18:37:19 CDT