- Set a CACHE value on the sequence you can live with.
- Don't bind the sequence value on INSERT
- Retrieve it for your program from the INSERT ( ss.NEXTVAL,....) RETURNING
id INTO ? " clause
Marko Hantula wrote in message ...
>Let's say I have a table & sequence like these :
>
>CREATE TABLE student(id NUMBER PRIMARY KEY, name VARCHAR2(50));
>CREATE SEQUENCE student_sequence START WITH 1 INCREMENT BY 1;
>
>
>Now let's say I have a Java method that creates a bunch
>of Student objects at once as follows :
>
>public Student[] createStudents(Connection Conn,
> String[] NameArray) throws
>Exception
>{
> Student[] Students = new Student[NameArray.length];
> long[] laIds = new long[NameArray.length];
>
> // Create ids
> for(int iIndex = 0; iIndex < NameArray.length; iIndex++)
> {
> Statement Stmt = Conn.createStatement();
> ResultSet Set = Stmt.executeQuery("SELECT student_sequence.NEXTVAL
>FROM dual");
>
> while(Set.next())
> laIds[iIndex] = Set.getLong(1);
>
> Set.close();
> Stmt.close();
> }
>
> // Create student objects
> PreparedStatement Stmt = Conn.prepareStatement("INSERT INTO
>student_table VALUES (?, ?)");
>
> for(int iIndex = 0; iIndex < NameArray.length; iIndex++)
> {
> // Add a batch to the statement
> Stmt.setLong(1, laIds[iIndex]);
> Stmt.setString(2, NameArray[iIndex]);
> Stmt.addBatch();
>
> // Create object
> Students[iIndex] = new Student(laIds[iIndex],
> NameArray[iIndex]);
> }
>
> // Execute batch & close
> Stmt.executeBatch();
> Stmt.close();
>
> return Students;
>}
>
>Now my question is, if the size of the student name array is
>large (e.g. 1000), how do I optimize the first part so that
>I would get all the ids at once in a batch operation,
>like I do in the insert part.
>The implementation above basically does N separate select
>statements from the database (across network) which is not too
>efficient.
>
>What would be the best way to optimize this code, should I
>possibly use a stored procedure that would return an array of
>ids or is there a better way to do this?
>I cannot use a bigger increment value in the sequence since
>the size of the array is not constant. Also, I need the ids because
>I have to return an array of Student objects, so I cannot use the
>sequence in the insert statement itself.
>
>Thanks,
>Marko
Received on Wed Oct 10 2001 - 05:50:26 CDT
