Re: Question about efficient ways to pass data from java to oracle

theRat wrote:
> Robert,
>
> updates are slow - for both batch updates and for converting data to
> arrays of structs that are passed into a stored procedure. In
> addition, gaining the connection object (when obtained through the
> DriverManager class) is also slow under load.

The first thing to do there is to use a connection poool. That's pretty much standard nowadays and will help you a lot - and you did it already.

> I don't believe the
> database is the problem.

Believing and knowing are two different things...

> It's true that we are doing some indexing
> and that that will slow things down a bit.

How complex is your data? Do you hand it over to a SP or to an insert statement? Did you test insert performanc with SQL PLus to determine where the bottleneck is? It could still be that your IO subsystem is too slow (you talked about db under load).

> What I'm really looking for is just other techniques for passing large
> amounts of data to oracle - techniques that I can try and compare to
> what I'm currently doing.

SQL*Loader and Data Pump come to mind.

> For example, I'd love to try
> Connection.setArray()

You probably mean PreparedStatement.setArray() http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#setArray(int,%20java.sql.Array)

> - but I can't find any examples out there. Or
> perhaps there is a way to declare a "stored procedure" in java and
> execute it in oracle (as opposed to an actual stored procedure). I'm
> just not sure what my options are.

AFAIK you can have Oracle execute Java code in the server. Never did that myself and I can't tell whether this would be a solution to your problem.

    robert Received on Thu Dec 15 2005 - 04:45:57 CST