我编写了一个方法，从Oracle服务器收集数据，对数据进行格式化和加密，然后将其插入MS SQL服务器。这种方法移动了大约60000条记录，时间有点长，而且有点草率。有人能看到清理它、加快速度的地方吗？
我认为可能需要改进的两个方面是当结果集被添加到列表中时。当列表被一次插入1000行到MS SQL表中时。
这是代码：
public static void get_random_selection(Connection ora_conn, Connection sql_conn) throws Exception, SQLException{
Statement sql_stmt = sql_conn.createStatement();
Statement ora_stmt = ora_conn.createStatement();
ResultSet sql_rs = null;
ResultSet ora_rs = null;
//Select the max QUARTER from RANDOM_SELECTION in MS SQL
sql_rs = sql_stmt.executeQuery("SELECT MAX(QUARTER) FROM RANDOM_SELECTION");
sql_rs.next();
int max_from_mssql = sql_rs.getInt(1);
ora_rs = ora_stmt.executeQuery("SELECT MAX(QUARTER) FROM RANDOM_SELECTION");
ora_rs.next();
int max_from_oracle = ora_rs.getInt(1);
//If the max_from_oracle is larger than max_from_mssql than the AL's and RL's in Oracle
//are fresher and need to be moved to MS SQL
//if (max_from_oracle > max_from_mssql){
if(1==1){
System.out.println("The RANDOM_SELECTION table in Oracle is more up to date than the RANDOM_SELECTION table in MS SQL.");
System.out.println("Retrieving RANDOM_SELECTION data from Oracle.");
//select items from RANDOM_SELECTION and DROPPER_CITY_BRK_2 that need to be moved
ora_rs = ora_stmt.executeQuery("select distinct(random_selection.randnum), "
+ "random_selection.quarter, "
+ "random_selection.ozip3, "
+ "random_selection.boxid, "
+ "random_selection.boxaddr, "
+ "random_selection.locdesc, "
+ "random_selection.loccity, "
+ "random_selection.lastmf, "
+ "random_selection.lastsat, "
+ "random_selection.boxtype, "
+ "random_selection.svcclas, "
+ "random_selection.dropzip5, "
+ "random_selection.dropper_id "
+ "from random_selection "
+ "where random_selection.dropper_id is not null "
+ "and random_selection.quarter = " + max_from_oracle + " "
+ "union "
+ "select distinct(random_selection.randnum), "
+ "random_selection.quarter, "
+ "random_selection.ozip3, "
+ "random_selection.boxid, "
+ "random_selection.boxaddr, "
+ "random_selection.locdesc, "
+ "random_selection.loccity, "
+ "random_selection.lastmf, "
+ "random_selection.lastsat, "
+ "random_selection.boxtype, "
+ "random_selection.svcclas, "
+ "random_selection.dropzip5, "
+ "dropper_city_brk_2.dropper_id "
+ "from random_selection, dropper_city_brk_2, dropper "
+ "where random_selection.ozip3 = dropper_city_brk_2.zip3 "
+ "and dropper.dropper_id = dropper_city_brk_2.dropper_id "
+ "and dropper.active = 1 "
+ "and dropper_city_brk_2.dropper_id <> 10002 "
+ "and random_selection.quarter = " + max_from_oracle + " "
+ "and random_selection.dropper_id is null");
System.out.println("Retrieved RANDOM_SELECTION data from Oracle.");
List<String[]> random_selection = new ArrayList<String[]>();
System.out.println("Assigning ResultSet to List.");
while (ora_rs.next()){
random_selection.add(new String[]{
ora_rs.getString("RANDNUM"),
ora_rs.getString("QUARTER"),
ora_rs.getString("OZIP3"),
ora_rs.getString("BOXID"),
ora_rs.getString("BOXADDR").replace("'"," "),
ora_rs.getString("LOCDESC") == null ? ora_rs.getString("LOCDESC") : ora_rs.getString("LOCDESC").replace("'",""),
ora_rs.getString("LOCCITY").replace("'", " "),
ora_rs.getString("LASTMF"),
ora_rs.getString("LASTSAT").equals("11:58pm") ? "null": ora_rs.getString("LASTSAT"),
ora_rs.getString("BOXTYPE"),
ora_rs.getString("SVCCLAS"),
ora_rs.getString("DROPZIP5"),
ora_rs.getString("DROPPER_ID")});
System.out.println(ora_rs.getRow());
}
System.out.println("Finished assigning ResultSet to List.");
//leading statement for the following loop
String query = "insert into random_selection "
+ "(RANDNUM,QUARTER,OZIP3,BOXID,BOXADDR,LOCDESC,LOCCITY,LASTMF,LASTSAT,BOXTYPE,SVCCLAS,DROPZIP5,DROPPER_ID) VALUES";
int jj = 0;
//loop through random_selection_array creating an INSERT statement to insert 999 entries at a time
//this is done to speed up the process
for(int ii = 0;ii<random_selection.size();ii++){
String[] array_holder = random_selection.get(ii);
query = query
+ "("
+ "'"+array_holder[0]+"',"
+ "'"+array_holder[1]+"',"
+ "'"+array_holder[2]+"',"
+ "'"+array_holder[3]+"',"
+ "'"+array_holder[4]+"',"
+ "'"+array_holder[5]+"',"
+ "'"+array_holder[6]+"',"
+ "'"+array_holder[7]+"',"
+ "'"+array_holder[8]+"',"
+ "'"+array_holder[9]+"',"
+ "'"+array_holder[10]+"',"
+ "'"+array_holder[11]+"',"
+ "'"+new sun.misc.BASE64Encoder().encode(encrypt(array_holder[12]))+"'),";
//every 999 iterations enter here
if (jj > 998){
//add |%| to the end of the string so that you can remove the final ','
query = query+"|%|";
query = query.replace(",|%|","");
System.out.println(query);
//sql_stmt.executeUpdate(query);
query = "insert into random_selection (RANDNUM,QUARTER,OZIP3,BOXID,BOXADDR,LOCDESC,LOCCITY,LASTMF,LASTSAT,BOXTYPE,SVCCLAS,DROPZIP5,DROPPER_ID) VALUES";
jj = 0;
}
jj++;
//the last few entries will be added one at a time to prevent nulls records from being inserted
if (ii > (random_selection.size() / 999) * 999){
//add |%| to the end of the string so that you can remove the final ','
query = query+"|%|";
query = query.replace(",|%|","");
System.out.println(query);
//sql_stmt.executeUpdate(query);
query = "insert into random_selection (RANDNUM,QUARTER,OZIP3,BOXID,BOXADDR,LOCDESC,LOCCITY,LASTMF,LASTSAT,BOXTYPE,SVCCLAS,DROPZIP5,DROPPER_ID) VALUES";
}
}
}
}
客户端希望避免在2台服务器之间进行任何打开的连接。
此问题可能是由于在MS SQL服务器上执行了大量插入。这样做不是很有效。
在两个数据库之间传输数据（无需链接）的正确方法是bcp传入和传出行。
首先，请记住，我在sybase之外没有任何BCP的经验。
-
弄清楚如何将您的数据转换为MS bcp格式。有几种方法可以做到这一点。我会尝试在oracledb和bcp中创建一个包含所有必需内容的视图，但使用encrypt（）的东西可能无法做到这一点。
-
bcp英寸
首先，你不应该猜测，而是应该向控制台写下你认为慢的每个区域实际需要多长时间。：）其次，您应该使用一个准备好的语句，并使用addBatch和executeBatch。在C#中，我会使用SqlBulkCopy，但我认为Java没有这样的类，所以你应该尝试addBatch和executeBatch。如果这仍然太慢，那么我将使用BULK INSERT：http://msdn.microsoft.com/en-us/library/ms188365.aspx