java - 对准备好的语句进行性能调整

我正在编写一个程序，用于从oracle数据库表中提取大约700万条记录，然后将它们重新插入另一个表中。我的程序如下

import java.sql.*;
import java.util.Scanner;
import java.io.*;

public class Test_5000 {

    public  void Test_5000() throws SQLException, IOException {
         long startTime = System.nanoTime();     //processing timer starts

         DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );

         Connection DBConn = DriverManager.getConnection( "jdbc:oracle:thin:@IE1FUX004:1521:Database", "username", "password" );

        if (DBConn != null)
        {
            System.out.println("nSuccessfullly connected to Oracle DB");
            String SqlQuery = "SELECT * FROM Old_Table";

               Statement stmt = DBConn.createStatement();
               ResultSet rs=stmt.executeQuery(SqlQuery);

               String InsertQuery = "INSERT INTO NEW_Table (AIRPORT_Id, "+
                       "AIRPORT_Name, "+
                       "PROCESSING_Time, "+
                       //some more 32 fields
                        ") Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
               PreparedStatement ps = DBConn.prepareStatement(InsertQuery);
               ps.setFetchSize(1000);
               int batchsize =1000;
               int count=0;
              int k =0;
               System.out.println(InsertQuery);

               while(rs.next())
               {
                   ps.setString(1,rs.getString(1));
                   ps.setString(2,rs.getString(2));
                   ps.setInt(3, 201804);
                   ps.setString(4,rs.getString(4));
                   ps.setString(5,rs.getString(5));
                   ps.setString(6,rs.getString(6));
                   ps.setString(7,rs.getString(7));
                   ps.setString(8,rs.getString(8));
                   ps.setInt(9,rs.getInt(9));
                   ps.setString(10,rs.getString(10));
                   ps.setString(11,rs.getString(11));
                   ps.setInt(12,rs.getInt(12));
                   ps.setString(13,rs.getString(13));
                   ps.setString(14,rs.getString(14));
                   ps.setInt(15,rs.getInt(15));
                   ps.setInt(16,rs.getInt(16));
                   ps.setInt(17,rs.getInt(17));
                   ps.setString(18,rs.getString(18));
                   ps.setString(19,rs.getString(19));
                   ps.setString(20,rs.getString(20));
                   ps.setString(21,rs.getString(21));
                   ps.setString(22,rs.getString(22));
                   ps.setString(23,rs.getString(23));
                   ps.setInt(24,rs.getInt(24));
                   ps.setString(25,rs.getString(25));
                   ps.setString(26,rs.getString(26));
                   ps.setInt(27,rs.getInt(27));
                   ps.setInt(28,rs.getInt(28));
                   ps.setString(29,rs.getString(29));
                   ps.setString(30,rs.getString(30));
                   ps.setInt(31,rs.getInt(31));
                   ps.setString(32,rs.getString(32));

                   ps.addBatch();
                   DBConn.setAutoCommit(false);
                   //autocommit off
                  k=k+1;

                   if(++count % batchsize==0)
                   {
                       ps.executeBatch();
                       System.out.println(k);
                   }



        }
               ps.executeBatch();
               System.gc();

               System.out.println("" +count);

               DBConn.setAutoCommit(false);

               long endTime = System.nanoTime();       //Processing time ends here
               long duration = (endTime - startTime);
               System.out.println("time taken for processing is" + " " +duration);

               ps.close();
               rs.close();
               DBConn.close();

        }
        else
        {
            System.out.println("nFailed to connect to Oracle DB");
        }
    }
    public static void main( String[] args) throws SQLException, IOException
      {
          System.out.println( "Started" );
          Test_5000 NFDAPT4 = new Test_5000();
          NFDAPT4.Test_5000();
          System.out.println( "End" );
      }
    }

该程序平均需要5分钟来运行并将记录插入数据库。绝对不是JVM堆的问题。我尝试了不同的提取大小后，发现最佳提取大小为1000。任何人都可以在此程序中建议任何性能调整，以减少其处理时间。我的要求是使用Java执行此操作。
任何建议将不胜感激。先感谢您。

最佳答案

我建议直接插入而不是获取和插入。尝试查询为

Insert into NEW_Table
    Select * from OLD_Table

它将节省您的检索时间