Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: MASSIVE degradation of insert using sequence ID's via JDBC on 9i?

Re: MASSIVE degradation of insert using sequence ID's via JDBC on 9i?

From: bugbear <bugbear_at_trim_papermule.co.uk_trim>
Date: Mon, 27 Jun 2005 15:17:52 +0100
Message-ID: <42c00a90$0$30796$ed2619ec@ptn-nntp-reader01.plus.net>


Holger Baer wrote:
> bugbear wrote:
>

>> Dear all;
>> we are using an Oracle sequence to generate primary
>> keys in our DB application. This is done in the usual

>
>
> What's usual to you isn't that obvious to most people
> in this group. So please be more verbose.

OK. This code is run repeatedly just so we can time it sensibly. Our app. is not (normally) doing "bulk inserts"

In order to keep this message half-readable, I've appended our test harness/code to the end of this post.

> You're doing this to yourself. Why the two network roundtrips when one
> would be sufficient?
>
> insert into table values (table_sequence.nextval, ....)
>
> if you need the value of the sequence look into the returning
> clause.

Thanks for the tip; I think it's independent of the primary degradation issue, since even the existing code runs "o.k." (12 X as fast as 9i) under 10g but that concept is certainly good to know.

Thank you.

Oh, and (DAMN! :-() whilst tidying the code for presenting here, it now runs slowly locally (i.e. client and server on the same machine, running 9i)

     BugBear (code pasted here)

import java.sql.*;

public class TestInsert {

         public static void main(String[] args) throws SQLException {
                 if (args.length != 4) {
                         System.err.println("Args: <uri> <username> 
<password> <iterations>");
                         System.exit(1);
                 }
                 runTest(args[0], args[1], args[2], 
Integer.parseInt(args[3]));
         }

         private static String[] setUpStatements = new String[] {
                 "CREATE TABLE testtable (" +
                 "    testk NUMBER(5) PRIMARY KEY," +
                 "    testname VARCHAR2(40) NOT NULL UNIQUE" +
                 ")",
                 "CREATE SEQUENCE testtableseq START WITH 1"
         };

         private static String[] tearDownStatements = new String[] {
                 "DROP SEQUENCE testtableseq",
                 "DROP TABLE testtable"
         };

         public static void runTest(String uri, String username, String 
password, int iterations) throws SQLException {
                 DriverManager.registerDriver(new 
oracle.jdbc.OracleDriver());
                 Connection connection = 
DriverManager.getConnection(uri, username, password);
                 executeStatements(connection, setUpStatements);

                 long timeBefore = System.currentTimeMillis();
                 for (int i = 0; i < iterations; i++) {
                         executeInsert(connection, i);
                 }
                 long timeAfter = System.currentTimeMillis();

                 executeStatements(connection, tearDownStatements);

                 connection.close();

                 System.out.println("Time taken for " + iterations + " 
iterations: " + ((timeAfter - timeBefore) / 1000.0) + " sec");
         }

  private static void executeInsert(Connection connection, int i) throws SQLException {

                 connection.setAutoCommit(false);

                 Statement statement = connection.createStatement();
                 ResultSet results = statement.executeQuery("SELECT 
testtableseq.nextval FROM DUAL");
                 results.next();
                 int sequenceValue = results.getInt(1);
                 results.close();
                 statement.close();

                 PreparedStatement preparedStatement = 
connection.prepareStatement("INSERT INTO testtable(testk, testname) VALUES (?, ?)");
                 preparedStatement.setInt(1, sequenceValue);
                 preparedStatement.setString(2, "test" + i);
                 preparedStatement.executeUpdate();
                 preparedStatement.close();

                 connection.commit();
         }

         private static void executeStatements(Connection connection, 
String[] sqlLines) throws SQLException {
                 Statement statement = connection.createStatement();
                 for (int i = 0; i < sqlLines.length; i++) {
                         statement.executeUpdate(sqlLines[i]);
                 }
                 statement.close();
         }

} Received on Mon Jun 27 2005 - 09:17:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US