In my Java application I'm sending batches of records to persist into an Oracle database via JDBC. My setup is very similar to this page: Upscaling your JDBC app using Oracle object type collection. The only difference is that I'm calling a stored procedure instead of doing a direct insert.
You'll notice in that example that every time a new STRUCT is created, one of the parameters is the connection:
structEmployee[i] = new oracle.sql.STRUCT(descEmployee, conn, empValues);
We noticed that it takes a long time to create these structs, especially when the database is in Europe and the application is running in the US. When the database and application are both in London it speeds up, but makes me think that we are making database calls to create each new struct.
Is that true? If so, is there a way to batch it up so that it goes faster? I haven't been able to find any examples or anything in the Oracle docs that talks about performance tuning creating the creation of STRUCTS from Java, so any help would be appreciated.
A year after my original post I was re-examining the code that had the performance issue and finally found the solution to my problem! It was not the creation of the STRUCT, the problem I had was the lifecycle of my Struct descriptors. I was creating a struct descriptor for each batch, and that turned out to be the slow part.
In effect I had something like this:
foreach batch of 1000 records
create struct descriptor
foreach record in batch
create struct and add to array
end foreach
create array descriptor
create array from struct array
execute stored proc
end foreach
In the end I changed it so something like this:
create struct descriptor
create array descriptor
foreach batch of 1000 records
foreach record in batch
create struct
end foreach
create array from struct array
execute stored proc
end foreach
I also found out that struct descriptors can be used across connections, so you could theoretically have some kind of cache or pool of descriptors that could be used across the applications, even if it gets used with a different connection than when it was originally created. I did not run a test to see if a single descriptor was thread safe, but my best guess is that it's not.
Making the above change really helped improve performance. I got a 2X - 6X improvement by reusing the descriptors, depending on the number of attributes in the user defined type. The more attributes in the type the more improvement I saw.