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

Following up on Peter J. Holzer, 18 Apr 2003:

>> A JVM program MUST have the JVM started BEFORE it can execute.
> Same for VBA. Code not yet loaded cannot be executed :-)

Not at all the same. A VBA program does NOT need the VBA DLL interpreter running before it itself can start. In fact, it is the VBA program that loads the interpreter DLL. A Java program will NOT start unless the JVM is running. And it is the JVM that loads and starts the main class.

>> You can't 
>> start ANOTHER program in that same JVM address space unless the first 
>> program does that for you. 

Ever tried to start batch programs from inside a Java program? Not easy, eh?

> Every interpreter does that. An interpreter takes an instruction stream
> in a particular instruction set and executes it according to the rules
> for that instruction set. That is "completely isolated" from the OS. The
> interpreted program can never do anything except through the
> interpreter. The interpreter can implement instructions such as "open
> file", "execute program" or "call function X in DLL Y", but these have
> to implemented explicitely - that's not something "just an interpreter"
> comes naturally with.
> The authors of the Java class library chose to
> implement these functions with an added layer of permission checking
> (the "sandbox"). That makes Java a bit more complicated, but it doesn't
> change the fact that it is an interpreter nor makes it it any more
> "virtual machine-like".

An interpreter takes indeed an instruction stream in a given instruction set AND executes it according to the rules of the OS it is running in. It doesn't NEED to do anything else for the program.

A VM does a lot more than that. In JVM's case, the clearest example is the garbage collection. Which an interpreter doesn't have to do, it can be done at OS malloc level. Sun has chosen to make it part of the "virtual machine" environment in which Java programs run. That way they can provide garbage collection even if the OS they are running under doesn't have that ability.

To come back to the original:

The PL/SQL interpreter takes advantage of the environment it's running in and knows about it. It doesn't have to run in any other environment. It can therefore be very efficient in its interaction with it.

If Oracle wants to keep its JVM under the standard imposed by Sun (which they have to or they break the licence agreement), then they have to make it independent from the fact that it's running inside the database code. It will never therefore be as integrated with the database as PL/SQL can be.

Nor would it be desirable to introduce database dependencies into the JVM. For example, the db traffic must always go through the same mechanism of any other Java runtime: JDBC. Of course, Oracle could provide a special class for its own JVM to totally bypass JDBC. Doubt they will, as this would make any program written in its JVM non-portable and Sun wouldn't like that.

This will always make it slower to interface SQL to Java than it is to interface SQL and PL/SQL. Hence the timings you see when calling Java functions. And I've got news for you: it ain't gonna improve much more. Or else Oracle will be sued by Sun...

Note that I'm not saying it wouldn't be desirable. Just too risky for Oracle to do it. Particularly when NO OTHER db vendor is even remotely considering doing the same.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam