需要一些幫助。我在這里拿了 Gord Thompson 的代碼How to increase performance for bulk INSERTs to ODBC linked tables in Access? 并對其進行了修改以適合我的情況。
我正在嘗試將名為“bulk_insert”的查詢（基于 MS Access DB 中的本地表）的內容復制到名為 dbo_tblCVR_Matching_tmp 的 SQL 鏈接表中。該查詢沒有計算欄位或函式或什么都沒有，只有 102 列純資料。我目前正在測驗 6K 到 10K 記錄范圍內的檔案。
代碼執行并在我得到該執行緒標題中的錯誤之前復制了許多記錄。我環顧四周，但沒有什么可以幫助我解決我的特定問題。不確定我是否必須清除或重繪 某些內容。這是我正在使用的兩個例程：
'==============================================================
'Gord Thompson Stackoverflow: https://stackoverflow.com/questions/25863473/how-to-increase-performance-for-bulk-inserts-to-odbc-linked-tables-in-access
'==============================================================
Sub bulk_insert()
Dim cdb As DAO.Database
Dim rst As DAO.Recordset
Dim t0 As Single
Dim i As Long
Dim c As Long
Dim valueList As String
Dim separator As String
Dim separator2 As String
t0 = Timer
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("SELECT * FROM bulk_insert", dbOpenSnapshot)
i = 0
valueList = ""
separator = ""
Do Until rst.EOF
i = i 1
valueList = valueList & separator & "("
separator2 = ""
For c = 0 To rst.Fields.Count - 1
valueList = valueList & separator2 & "'" & rst.Fields(c) & "'"
If c = 0 Then
separator2 = ","
End If
Next c
valueList = valueList & ")"
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
Dim qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("dbo_tblCVR_Matching_tmp").Connect
qdf.ReturnsRecords = False
qdf.sql = "INSERT INTO dbo.tblCVR_Matching_tmp (" & _
"Associate_Id , Recd_Date, Price_Sheet_Eff_Date, VenAlpha, Mfg_Name, Mfg_Model_Num, Fei_Alt1_Code, Mfg_Product_Num, Base_Model_Num, Product_Description," & _
"Qty_Base_UOM , Price_Invoice_UOM, Mfr_Pub_Sugg_List_Price, Mfr_Net_Price, IMAP_Pricing, Min_Order_Qty, UPC_GTIN, Each_Weight, Each_Length, Each_Width," & _
"Each_Height, Inner_Pack_GTIN_Num, Inner_Pack_Qty, Inner_Pack_Weight, Inner_Pack_Length, Inner_Pack_Width, Inner_Pack_Height, Case_GTIN_Num, Case_Qty," & _
"Case_Weight, Case_Length, Case_Width, Case_Height, Pallet_GTIN_Num, Pallet_Qty, Pallet_Weight, Pallet_Length, Pallet_Width, Pallet_Height, Pub_Price_Sheet_Eff_Date," & _
"Price_Sheet_Name_Num, Obsolete_YN, Obsolete_Date, Obsolete_Stock_Avail_YN, Direct_Replacement, Substitution, Shelf_Life_YN, Shelf_Life_Time, Shelf_Life_UOM," & _
"Serial_Num_Req_YN, LeadLaw_Compliant_YN, LeadLaw_3rd_Party_Cert_YN, LeadLaw_NonPotable_YN, Compliant_Prod_Sub, Compliant_Prod_Plan_Ship_Date, Green, GPF, GPM," & _
"GPC, Freight_Class, Gasket_Material, Battery_YN, Battery_Type, Battery_Count, MSDS_YN, MSDS_Weblink, Hazmat_YN, UN_NA_Num, Proper_Shipping_Name," & _
"Hazard_Class_Num, Packing_Group, Chemical_Name, ORMD_YN, NFPA_Storage_Class, Kit_YN, Load_Factor, Product_Returnable_YN, Product_Discount_Category," & _
"UNSPSC_Code, Country_Origin, Region_Restrict_YN, Region_Restrict_Regulations, Region_Restrict_States, Prop65_Eligibile_YN, Prop65_Chemical_Birth_Defect," & _
"Prop65_Chemical_Cancer, Prop65_Chemical_Reproductive, Prop65_Warning, CEC_Applicable_YN, CEC_Listed_YN, CEC_Model_Num, CEC_InProcess_YN, CEC_Compliant_Sub," & _
"CEC_Compliant_Sub_Cross_YN, Product_Family_Name, Finish, Kitchen_Bathroom, Avail_Order_Date, FEI_Exclusive_YN, MISC1, MISC2, MISC3" & _
") Values " & valueList
'this is the line that is always highlighted when the error occurs
qdf.Execute dbFailOnError
Set qdf = Nothing
Set cdb = Nothing
End Sub
uj5u.com熱心網友回復：
在您的回圈中，對值長度進行測驗。
我會在大約 4000 個字符處觸發插入，也許嘗試 8000 個字符。
此外，您想為此使用傳遞查詢，否則會很慢。
所以，代碼會像你說的那樣，但要確保輸出格式是 t-sql (sql server) 格式，而不是 JET/ACE sql 格式。
請注意，sql server 確實有插入的簡寫，我們希望使用這一事實，因為這會大量減少開銷（sql 語法）（并且查看您的代碼，您似乎確實在這樣做）。
所以，我們想要的格式是這樣的：
INSERT INTO tblBig (ID, FirstName, LastName, City)
VALUES (134, 'Albert', 'Kallal', 'Edmonton'),
VALUES (134, 'Albert', 'Kallal', 'Edmonton'),
VALUES (134, 'Albert', 'Kallal', 'Edmonton');
請注意，我們如何只需要一個插入命令來處理許多行。
因此，我們的代碼存根將如下所示：
Sub TestAppendNeedForSpeed()
' I wanted to allow PK inserts
With CurrentDb.QueryDefs("qryPass1")
.SQL = "SET IDENTITY_INSERT TBLbIG1 ON;"
.Execute
End With
Dim rstLocal As dao.Recordset
Set rstLocal = CurrentDb.OpenRecordset("tblBig")
Dim sBASE As String ' base sql insert string
Dim sValues As String ' our values() list built up
Dim t As Single
t = Timer
Dim i As Long
Dim j As Long
Dim ChunkSize As Long ' # length size of "text" to send to server
ChunkSize = 4000 ' I don't think going higher will help
sBASE = "INSERT INTO tblBig1 (ID,FirstName,LastName,City) VALUES "
Dim RowsInChunk As Long ' this will show rows that fit into a chunk - only FYI
Dim RowCountOut As Long
sValues = ""
Do While rstLocal.EOF = False
RowCountOut = RowCountOut 1
If sValues <> "" Then sValues = sValues & ","
RowsInChunk = RowsInChunk 1
With rstLocal
sValues = sValues & "(" & !ID & "," & qu(!FirstName) & "," & qu(!LastName) & "," & qu(!City) & ")"
End With
If (Len(sBASE) Len(sValues)) >= ChunkSize Then
' send data to server
With CurrentDb.QueryDefs("qryPass1")
.SQL = sBASE & sValues
.Execute
End With
Debug.Print "(" & RowCount & ") -- buffer out - " & RowsInChunk
RowsInChunk = 0
sValues = ""
DoEvents
End If
rstLocal.MoveNext
Loop
' send out last batch (if any)
If sValues <> "" Then
With CurrentDb.QueryDefs("qryPass1")
.SQL = sBASE & sValues
.Execute
End With
sValues = ""
End If
rstLocal.Close
t = Timer - t
Debug.Print "done - time = " & t
End Sub
因此，按照我們的布局方式，我們可以設定/調整/測驗/嘗試最佳塊大小。
您甚至沒有關閉并在同一個球部分中一次插入 4000 行。嘗試大約 4000 個字符，也許是 8000 個。有些系統，我看到大約 12000 個字符塊大小效果最好。
并且如前所述，使用上述傳遞查詢的想法 - 它也會運行得更快。
您可以期望通過上述方法提高大約 15 到 20 倍的速度。因此，代替 120 分鐘，您會看到大約 6 分鐘的時間。
所以，使用上面的模板和方法。當然，一行值可以是外部子（或函式）呼叫，但上述方法將為您提供最佳速度。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/487669.html