Re: *** JDBC Statement faster than PreparedStatement ***

Hi Jonatan. Here's a way to do batch inserts to Oracle really fast. Do it all with a simple Statement, with a batch string you build yourself:

  String big_batch = " BEGIN ";

  while (I'm not tired of batching)
    big_batch += " insert into myTable values(myValues)" + "; ";

  big_batch += " END;";

  st.executeUpdate(big_batch);

This will get rid of the major performance hit of the client-server chat for each row and/or driver inefficiencies. There is supposed to be a limit (64k?) on the size of your SQL, so maybe watch that... Let me know if this helps.

Joe Weinstein at BEA WebLogic

Jonatan Kelu wrote:

> 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

--

PS: Folks: BEA WebLogic is in S.F., and now has some entry-level positions for people who want to work with Java and E-Commerce infrastructure products. Send resumes to joe_at_beasys.com

                    The Weblogic Application Server from BEA
         JavaWorld Editor's Choice Award: Best Web Application Server
  Java Developer's Journal Editor's Choice Award: Best Web Application Server
     Crossroads A-List Award: Rapid Application Development Tools for Java
Intelligent Enterprise RealWare: Best Application Using a Component Architecture
               http://weblogic.beasys.com/press/awards/index.htm