Huge performance hit when using Java/OJDBC

Question

Hello,

I am having some strange behavior with the using the Java with the "ojdbc.jar" as the client.

I have a simple program that tries to just inserts 500 rows using one query. (an INSERT ALL FROM dual).

This program takes 25 seconds to complete in Java. This is the time the statement.execute() method takes to complete.

Wen I run the query from SQLplus/SQLdeveloper it takes less than 1 second.

Any ideas?

Answer 1

Sounds like you're getting a new connection for every insert.

Answer 2

While your performance issues seem excessive and beyond what should be caused by it, it's worth noting that the Java JDBC-ODBC drivers are notoriously slow. If possible, use the native drivers for your database, as they will generally be much faster.

Answer 3

In general you may want to avoid very large SQL statements, and especially the INSERT ALL FROM DUAL trick. I don't know why, but I've seen several cases where Oracle will take a very long time to parse large SQL statements. For some reason "insert into table_name (select 'asdf' from dual union all select 'asdf from dual ...)" seems to work much better, but it still starts to slow down after a while.

I once timed different sizes of similar queries, and the parse time seemed to grow exponentially at some point. That was on 10.2.0.3.0 I believe. But I can't reproduce the issue on 10.2.0.1.0 right now, maybe it's only a bug for a particular version?

I'd recommend that you use the UNION ALL version instead, and decrease the amount of rows sent per statement. Another solution is to build a PL/SQL block to do the inserts. Oracle seems to parse large PL/SQL better than large SQL, but that solution is more complicated.

(Btw, it's probably just be a coincidence that it ran faster in SQL Developer. Since the problem is with the parse time, the second time you run it will always be faster.)