java - 使用java在sql server中插入大量行的更好方法？

Question

我正在尝试使用 Java 代码在 SQL Server 2008 中插入数十万条记录。在最短的时间内插入它们的最佳方法是什么？
我正在读取 xls 文件并将每一行插入数据库。我的 xls 文件包含大约 65000 行。插入数据库需要很多时间。我使用的是批处理，但它也需要太多时间。
请建议我插入数据的最佳优化方式。我的 Java 代码。

public String insertDataMigration(ArrayList<ArrayList<String>> temp,String iConstantQuery, Connection conn) {
        int rowCount=0;
        String error = null;
        try {
            pstmt = conn.prepareStatement(iConstantQuery);
            for (int i = 0; i < temp.size(); i++) {
                rowCount++;
                pstmt.setString(1, temp.get(i).get(0).toString());
                pstmt.setString(2, temp.get(i).get(1).toString());
                pstmt.setInt(3, Integer.parseInt(temp.get(i).get(2).toString()));
                pstmt.setDouble(4, Double.parseDouble(temp.get(i).get(3).toString()));
                pstmt.setDouble(5, Double.parseDouble(temp.get(i).get(4).toString()));
                pstmt.setDouble(6, Double.parseDouble(temp.get(i).get(5).toString()));
                pstmt.setDouble(7, Double.parseDouble(temp.get(i).get(6).toString()));
                pstmt.setString(8, temp.get(i).get(7).toString());
                pstmt.setInt(9, Integer.parseInt(temp.get(i).get(8).toString()));
                pstmt.setDouble(10, Double.parseDouble(temp.get(i).get(9).toString()));
                pstmt.setString(11, temp.get(i).get(10).toString());
                pstmt.setString(12, temp.get(i).get(11).toString());
                pstmt.setString(13, temp.get(i).get(12).toString());
                pstmt.setString(14, temp.get(i).get(13).toString());
                pstmt.executeUpdate();
//              pstmt.addBatch();
            }
//          pstmt.executeBatch();
            error="NO ERROR";
        } catch (Exception e) {
            try {
                e.printStackTrace();
                error=rowCount+"_"+e;
                pstmt.close();
                Constants.exceptionLogger.error("PayoutsDAO-insertData in try--" + e);
            } catch (SQLException e1) {
                Constants.exceptionLogger.error("PayoutsDAO-insertData in catch--" + e1);
            }
        } finally {
            try {
                pstmt.close();
            } catch (Exception e2) {
                Constants.exceptionLogger.error("PayoutsDAO-insertData in finally--"+ e2);
            }
        }
        return error;
    }

我的表结构

CREATE TABLE [dbo].[tblDataMigration_Bank](
    [REFERENCENO] [bigint] IDENTITY(1,1) NOT NULL,
    [Date] [nvarchar](50) NULL,
    [Name] [nvarchar](300) NULL,
    [Chq_No] [numeric](18, 0) NULL,
    [Debit] [numeric](18, 2) NULL,
    [Credit] [numeric](18, 2) NULL,
    [Balance] [numeric](18, 2) NULL,
    [Calculated_Balance] [numeric](18, 2) NULL,
    [Status] [nvarchar](50) NULL,
    [Pay_Request_No] [numeric](18, 0) NULL,
    [Pay_Request_Amt] [numeric](18, 2) NULL,
    [JE_No] [nvarchar](50) NULL,
    [Remarks] [nvarchar](300) NULL,
    [Reconciliation_Flag] [nvarchar](50) NULL,
    [Temp] [nvarchar](50) NULL,
    [UPLOADDATE] [datetime] NULL,
 CONSTRAINT [PK_tblDataMigration_Ledger] PRIMARY KEY CLUSTERED 
(
    [REFERENCENO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

视情况而定，但您需要使用批处理并在大约 50-100 行之后调用执行批处理。这应该是最有效的方法。您可以尝试使用不同的编号运行，因为这也取决于您的数据库实例。

一些石灰:

 ...
 pstmt.setString(12, temp.get(i).get(11).toString());
 pstmt.setString(13, temp.get(i).get(12).toString());
 pstmt.setString(14, temp.get(i).get(13).toString());
 pstmt.addBatch();
 if ((i + 1) % 100 == 0) {
      pstmt.executeBatch(); // Execute every 100 items.
 }

编辑:

此外，如果这还不够好，我建议您试试这个:

if ((i + 1) % 100 == 0) {
     pstmt.executeBatch(); // Execute every 100 items.
     psmt.clearParameters();
     psmt.clearBatch();
}

还有数据库级别的调整，你可以尝试阅读这个，它可能会有所帮助，但这需要更好地了解 sql server

https://blogs.datadirect.com/2012/05/how-to-bulk-insert-jdbc-batches-into-microsoft-sql-server-oracle-sybase.html