oracle – 如何在Access中提高批量INSERT到ODBC链接表的性能?
我要导入CSV和TXT文件.我将文件导入Access,然后将记录插入到链接的Oracle表中.每个文件大约有300万行,这个过程需要很长时间才能完成.
导入Access非常快,但插入链接的Oracle表需要很长时间. 以下是我目前使用的流程: DoCmd.TransferText acImportFixed,"BUSSEP2014 Link Specification","tblTempSmartSSP",strFName,False db.Execute "INSERT INTO METER_DATA ([MPO_REFERENCE]) SELECT MPO_REFERENCE FROM tblTempSmartSSP;"` tblTempSmartSSP是一个访问表,METER_DATA是一个链接的Oracle表 我也试过直接导入到链表,这也很慢. 我怎样才能加快这个过程? 在将大量INSERT处理到Access中的ODBC链接表时,这种情况并不罕见.在以下Access查询的情况下INSERT INTO METER_DATA (MPO_REFERENCE) SELECT MPO_REFERENCE FROM tblTempSmartSSP 其中[METER_DATA]是一个ODBC链接表,而[tblTempSmartSSP]是一个本地(本机)访问表,ODBC在某种程度上是有限的,因为它必须能够容纳各种功能可能不同的目标数据库.很大.不幸的是,它通常意味着尽管单个Access SQL语句实际发送到远程(链接)数据库,但是对于本地表中的每一行都是单独的INSERT(或等效的).可以理解的是,如果本地表包含大量行,则可能会非常慢. 选项1:本地批量插入到远程数据库 所有数据库都有一个或多个本机机制用于批量加载数据:Microsoft SQL Server具有“bcp”和BULK INSERT,而Oracle具有“SQL * Loader”.这些机制针对批量操作进行了优化,通常会提供显着的速度优势.事实上,如果需要将数据导入Access并在传输到远程数据库之前“按摩”,则将修改后的数据转储回文本文件然后批量导入到远程数据库中仍然会更快. 选项2:在Access中使用传递查询 如果批量导入机制不是一个可行的选项,那么另一种可能性是在Access中构建一个或多个传递查询,以使用可以一次插入多行的INSERT语句来上载数据. 例如,如果远程数据库是SQL Server(2008或更高版本),那么我们可以像这样运行Access传递(T-SQL)查询 INSERT INTO METER_DATA (MPO_REFERENCE) VALUES (1),(2),(3) 使用一个INSERT语句插入三行. 根据另一个早期问题here的答案,Oracle的相应语法将是 INSERT ALL INTO METER_DATA (MPO_REFERENCE) VALUES (1) INTO METER_DATA (MPO_REFERENCE) VALUES (2) INTO METER_DATA (MPO_REFERENCE) VALUES (3) SELECT * FROM DUAL; 我使用具有10,000行的本机[tblTempSmartSSP]表,使用SQL Server(因为我无法访问Oracle数据库)测试此方法.代码 … Sub LinkedTableTest() Dim cdb As DAO.Database Dim t0 As Single t0 = Timer Set cdb = CurrentDb cdb.Execute _ "INSERT INTO METER_DATA (MPO_REFERENCE) " & _ "SELECT MPO_REFERENCE FROM tblTempSmartSSP",_ dbFailOnError Set cdb = Nothing Debug.Print "Elapsed time " & Format(Timer - t0,"0.0") & " seconds." End Sub …在我的测试环境中执行大约需要100秒. 相比之下,下面的代码,如上所述构建多行INSERT(使用Microsoft称之为Table Value Constructor)… Sub PtqTest() Dim cdb As DAO.Database,rst As DAO.Recordset Dim t0 As Single,i As Long,valueList As String,separator As String t0 = Timer Set cdb = CurrentDb Set rst = cdb.OpenRecordset("SELECT MPO_REFERENCE FROM tblTempSmartSSP",dbOpenSnapshot) i = 0 valueList = "" separator = "" Do Until rst.EOF i = i + 1 valueList = valueList & separator & "(" & rst!MPO_REFERENCE & ")" If i = 1 Then separator = "," End If If i = 1000 Then SendInsert valueList i = 0 valueList = "" separator = "" End If rst.MoveNext Loop If i > 0 Then SendInsert valueList End If rst.Close Set rst = Nothing Set cdb = Nothing Debug.Print "Elapsed time " & Format(Timer - t0,"0.0") & " seconds." End Sub Sub SendInsert(valueList As String) Dim cdb As DAO.Database,qdf As DAO.QueryDef Set cdb = CurrentDb Set qdf = cdb.CreateQueryDef("") qdf.Connect = cdb.TableDefs("METER_DATA").Connect qdf.ReturnsRecords = False qdf.sql = "INSERT INTO METER_DATA (MPO_REFERENCE) VALUES " & valueList qdf.Execute dbFailOnError Set qdf = Nothing Set cdb = Nothing End Sub …需要1到2秒才能产生相同的结果. (T-SQL表值构造函数限制为一次插入1000行,因此上面的代码比其他情况稍微复杂一些.) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |