从txt (MS Access 2013)导入到SQL Server 2016速度较慢

Question

我在我的本地PC上有一个txt文件，必须检查这个文件，然后使用MS Access中的存储过程逐行上载到SQL Server2016中。看起来Access总是运行得快2行，然后停了一下。

在MS Access中，我使用了这个函数：

Public Function ImportData(FileString As String)
Dim WholeLine As String
Dim cc As Variant

Dim sapPurchaseDocument As String
Dim sapPartNumber As String
Dim sapPartName As String
Dim sapDocumentDate As String
Dim sapSupplier As String
Dim sapPlant As String
Dim sapSLoc As String
Dim sapQuantity As Double
Dim sapUOM As String
Dim sapTargetQuantity As Double
Dim sapDeliveryDate As String
Dim sapPrevQuantity As Double
Dim sapReceivedQuantity As Double
Dim sapIssuedQuantity As Double
Dim sapDeliveredQuantity As Double
Dim sapPurchaseRequisition As String
Dim sapPurchaseRequisitionItem As String
Dim sapCreationIndicatior As String
Dim sapNoOfPositions As Double
Dim totalCount As Integer
Dim sapPurchaseDocumentItem As String

Dim rs As New ADODB.Recordset
Call GetConnection
Set rs.ActiveConnection = myCN

If Right(FileString, 3) = "txt" Then
    totalCount = GetRowCount(FileString)

    Open FileString For Input As #1
    i = 0
    Do While Not EOF(1)
        Line Input #1, WholeLine

        If Left(WholeLine, 3) = "| 4" Then
            'Debug.Print WholeLine
            cc = Split(WholeLine, "|")

            sapPurchaseDocument = Trim(cc(1))
            sapPartNumber = Trim(Replace(cc(2), ".", ""))
            sapPartName = Trim(Replace(cc(3), "'", ""))
            sapDocumentDate = Right(cc(4), 4) & "-" & Mid(cc(4), 4, 2) & "-" & Left(cc(4), 2)
            sapSupplier = cc(5)
            sapPlant = cc(6)
            sapSLoc = cc(7)
            sapQuantity = Replace(cc(8), ",", "")
            sapUOM = Trim(cc(9))
            sapTargetQuantity = Replace(cc(10), ",", "")
            sapDeliveryDate = Right(cc(11), 4) & "-" & Mid(cc(11), 4, 2) & "-" & Left(cc(11), 2)
            sapPrevQuantity = cc(12)
            sapReceivedQuantity = Replace(cc(13), ",", "")
            sapIssuedQuantity = Replace(cc(14), ",", "")
            sapDeliveredQuantity = Replace(cc(15), ",", "")
            sapPurchaseRequisition = Trim(cc(16))
            sapPurchaseRequisitionItem = Trim(cc(17))
            sapCreationIndicatior = cc(18)
            sapNoOfPositions = cc(19)
            sapPurchaseDocumentItem = Trim(cc(20))
            strSQL = "spInsertUpdateSAPME2M '" & sapPurchaseDocument & "', '" & sapPartNumber & "', '" & sapPartName & "', '" & _
                     sapDocumentDate & "', '" & sapSupplier & "', '" & sapPlant & "', '" & sapSLoc & "', " & _
                     sapQuantity & ", '" & sapUOM & "', " & sapTargetQuantity & ", '" & sapDeliveryDate & "', " & _
                     sapPrevQuantity & ", " & sapReceivedQuantity & ", " & sapIssuedQuantity & ", " & _
                     sapDeliveredQuantity & ", '" & sapPurchaseRequisition & "', '" & sapPurchaseRequisitionItem & "', '" & _
                     sapCreationIndicatior & "', '" & sapNoOfPositions & "', '" & sapPurchaseDocumentItem & "'"
            rs.Open (strSQL)
            DoEvents

        End If
    i = i + 1
    Debug.Print i
    Forms!frm_Overview.lblStatus.Caption = "Record " & i & " of " & totalCount & " loaded. Please wait!"
    DoEvents
    'Refresh
    Loop
MsgBox "Import done"
End If

Close #1

End Function

在SQL Server上，我有一个如下所示的存储过程：

USE [MOBILEPRINT]
GO
/****** Object:  StoredProcedure [dbo].[spInsertUpdateSAPME2M]    Script Date: 5/25/2020 11:39:31 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CHANGE NO ACTION
ALTER PROCEDURE [dbo].[spInsertUpdateSAPME2M] 
    -- Add the parameters for the stored procedure here
           @sapPurchaseDocument varchar(50),
           @sapPartNumber varchar(50),
           @sapPartName varchar(300),
           @sapDocumentDate date,
           @sapSupplier varchar(50),
           @sapPlant varchar(100),
           @sapSLoc varchar(50),
           @sapQuantity float,
           @sapUOM varchar(50),
           @sapTargetQuantity float,
           @sapDeliveryDate date,
           @sapPrevQuantity float,
           @sapReceivedQuantity float,
           @sapIssuedQuantity float,
           @sapDeliveredQuantity float,
           @sapPurchaseRequisition varchar(50),
           @sapPurchaseRequisitionItem varchar(50),
           @sapCreationIndicatior varchar(50),
           @sapNoOfPositions varchar(50),
           @sapPurchaseDocumentItem varchar(50)
AS
BEGIN TRANSACTION
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @RESULT int
    DECLARE @UPDATE_CHECK int
    DECLARE @UpdateDate datetime = GetDate()

    BEGIN
        SELECT @RESULT = COUNT(sapPurchaseDocument) FROM SAP_ME2M WHERE sapPurchaseDocument = @sapPurchaseDocument AND sapPartNumber = @sapPartNumber
            IF ISNULL(@RESULT,0) = 0
                BEGIN 
                INSERT INTO SAP_ME2M (
                    sapPurchaseDocument, 
                    sapPartNumber, 
                    sapPartName, 
                    sapDocumentDate, 
                    sapSupplier, 
                    sapPlant, 
                    sapSLoc,
                    sapQuantity, 
                    sapUOM, 
                    sapTargetQuantity, 
                    sapDeliveryDate, 
                    sapPrevQuantity, 
                    sapReceivedQuantity, 
                    sapIssuedQuantity,
                    sapDeliveredQuantity, 
                    sapPurchaseRequisition, 
                    sapPurchaseRequisitionItem, 
                    sapCreationIndicatior, 
                    sapNoOfPositions,
                    ChangeDate,
                    sapPurchaseDocumentItem)
                VALUES
                    (@sapPurchaseDocument, @sapPartNumber, @sapPartName, @sapDocumentDate, @sapSupplier, @sapPlant, 
                    @sapSLoc, @sapQuantity, @sapUOM, @sapTargetQuantity, @sapDeliveryDate, @sapPrevQuantity, 
                    @sapReceivedQuantity, @sapIssuedQuantity, @sapDeliveredQuantity, @sapPurchaseRequisition, 
                    @sapPurchaseRequisitionItem, @sapCreationIndicatior, @sapNoOfPositions, @UpdateDate, @sapPurchaseDocumentItem)
                END
            ELSE
                SELECT @UPDATE_CHECK = COUNT(*) FROM SAP_ME2M WHERE 
                                                                       sapPurchaseDocument          = @sapPurchaseDocument          AND
                                                                       sapPartNumber                = @sapPartNumber                AND
                                                                       sapPartName                  = @sapPartName                  AND
                                                                       sapDocumentDate              = @sapDocumentDate              AND
                                                                       sapSupplier                  = @sapSupplier                  AND
                                                                       sapPlant                     = @sapPlant                     AND
                                                                       sapSLoc                      = @sapSLoc                      AND
                                                                       sapQuantity                  = @sapQuantity                  AND
                                                                       sapUOM                       = @sapUOM                       AND
                                                                       sapTargetQuantity            = @sapTargetQuantity            AND
                                                                       sapDeliveryDate              = @sapDeliveryDate              AND
                                                                       sapPrevQuantity              = @sapPrevQuantity              AND
                                                                       sapReceivedQuantity          = @sapReceivedQuantity          AND
                                                                       sapIssuedQuantity            = @sapIssuedQuantity            AND
                                                                       sapDeliveredQuantity         = @sapDeliveredQuantity         AND
                                                                       sapPurchaseRequisition       = @sapPurchaseRequisition       AND
                                                                       sapPurchaseRequisitionItem   = @sapPurchaseRequisitionItem   AND
                                                                       sapCreationIndicatior        = @sapCreationIndicatior        AND
                                                                       sapNoOfPositions             = @sapNoOfPositions             AND
                                                                       sapPurchaseDocumentItem      = @sapPurchaseDocumentItem
                IF @UPDATE_CHECK = 0
                    BEGIN
                    UPDATE SAP_ME2M SET 
                                        sapPartName                 = @sapPartName                  ,
                                        sapDocumentDate             = @sapDocumentDate              ,
                                        sapSupplier                 = @sapSupplier                  ,
                                        sapPlant                    = @sapPlant                     ,
                                        sapSLoc                     = @sapSLoc                      ,
                                        sapQuantity                 = @sapQuantity                  ,
                                        sapUOM                      = @sapUOM                       ,
                                        sapTargetQuantity           = @sapTargetQuantity            ,
                                        sapDeliveryDate             = @sapDeliveryDate              ,
                                        sapPrevQuantity             = @sapPrevQuantity              ,
                                        sapReceivedQuantity         = @sapReceivedQuantity          ,
                                        sapIssuedQuantity           = @sapIssuedQuantity            ,
                                        sapDeliveredQuantity        = @sapDeliveredQuantity         ,
                                        ChangeDate                  = @UpdateDate
                    WHERE               
                                        sapPartNumber               = @sapPartNumber                AND
                                        sapPartName                 = @sapPartName                  AND
                                        sapDocumentDate             = @sapDocumentDate              AND
                                        sapSupplier                 = @sapSupplier                  AND
                                        sapPlant                    = @sapPlant                     AND
                                        sapSLoc                     = @sapSLoc                      AND
                                        sapPurchaseDocumentItem     = @sapPurchaseDocumentItem
                    END
    END
COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON);

我必须上传约30000个记录，这需要一个多小时的时刻。

如果你有什么建议，请让我知道。

Answer 1

要快速传输数据，请使用已启用批处理操作的断开连接的记录集。

Dim conn As ADODB.Connection
Call GetConnection
Set conn = myCN
Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Table1", conn, adOpenForwardOnly, adLockBatchOptimistic
'Disconnect
Set rs.ActiveConnection = Nothing
Dim i As Long
For i = 1 To 3000
    rs.AddNew
    rs.Fields(1) = i
Next
'Reconnect
Set rs.ActiveConnection = conn
'Batch insert
rs.UpdateBatch
Debug.Print Now()

对我来说，这在2秒内执行，但这在很大程度上取决于SQL服务器的位置。

然后，在上传时对数据集进行进一步处理。基于每个记录的处理通常会很慢。