*** JDBC Statement faster than PreparedStatement ***

In doing various experiments measuring the performance of JDBC PreparedStatement against that of the equivalent ordinary Statement, I was surprised to discover that in certain instances (which in some case may be the majority of cases) the ordinary JDBC Statement is faster than the PreparedStatement.

I'll briefly outline the conditions under which I was testing.

I was using the Oracle JDBC 1.22 OCI driver for Oracle 8.0.x. My experiment consisted of doing 10,000 inserts into an empty table and timing how long it takes. The table contained only "VARCHAR2" columns of length 10, declared as "NOT NULL". Autocommit was turned off and the transaction was only committed once after all 10,000 records had been inserted. The experiment was repeated with different numbers of columns.

My findings were that if the number of columns is < 20 or so, PreparedStatement worked faster than Statement. However, for more than around 20 columns, Statement was faster - and this even more so the greater the number of columns. I found that the performance degradation using Statement was pretty much linear with the number of columns in the table. However, with Prepared Statement, the performance degradation was worse than linear, thus causing it to diverge more greatly from the performance of Statement the greater the number of columns there was.

An even more interesting thing I found was that one thing that Oracle claimed was a performance improvement actually performs even worse. That is, the Oracle documentation says that you can cast the PreparedStatement down to an OraclePreparedStatement and then execute the "setExecuteBatch(int)" method that specifies the number of executions of PreparedStatement that should be batched together for a batch execution so as to save server round-trip delays. I set this to a value of 100. What I found was that for a small number of columns, this did actually improve performance. However, again for more than about 20 columns, the performance of this degraded to be worse than the ordinary Statement, and even worse than the ordinary PreparedStatement that this performance improvement is supposed to make more efficient!!! I couldn't believe it!

I think someone's stuffed up pretty bad at Oracle, or they never tested the driver for tables with more than 20 columns!

You can make your own program that inserts 10,000 records into a database table with say 50 or 100 VARCHAR2 columns, committing only after all records are inserted, and using both Statement and PreparedStatement to do this. It shouldn't be too complicated, and this will show these results immediately.

Please, can somebody explain to me why this is the case. Why does the ordinary Statement perform better than the PreparedStatement for tables with greater than 20 columns? And why does the PreparedStatement using batch execution perform even worse? This seems to go against all the conventional database wisdom out there.

If you do have any ideas, or if you have the real answer, please contact me at my below e-mail address as I'm not always able to read the news group. But please also do post it to the newsgroup for anybody else that may be interested.

Thanks,

Jonatan Kelu
ACUS, UNISYS
Phone: +61-2-9390-1328
E-mail: Jonatan.Kelu_at_au.unisys.com Received on Mon Feb 28 2000 - 03:32:34 CST