回圈遍歷OleDbDataReader.Read以生成帶分隔符的文本檔案。為什么這段代碼在回圈時會變慢？

我有一個 VB.NET 應用程式，它使用 Oracle OleDbDataReader 從 Oracle 資料庫中提取約 1500 萬行并將它們寫入 | 分隔的文本檔案。

Private Sub GenerateTextSqlReportWithCurrent(report As TblreportEntity, filename As String)

        Const batchSize = 20000

        Dim encryption As New ClassEncrypt

        'get data
        LogEvent($"INFO:          Opening DataReader for report {report.ReportName}")
        Dim reader As OleDbDataReader = IMOracle2.GetDataReader(report.Sql, IMOracle2.GetConnectString(My.Settings.DB_Instance, encryption.Decrypt(My.Settings.DB_UserID), encryption.Decrypt(My.Settings.DB_PWD)))
        LogEvent($"INFO: Finished Opening DataReader for report {report.ReportName}")
        
        LogEvent($"INFO: writing {report.ReportName} to {filename}")
        WriteToFile(filename, GetColumnTitlesHeader(reader), False)
        
        Dim batch As New StringBuilder()

        Dim lastReport As DateTime = DateTime.Now()
        Dim rowCount As Integer
        While reader.Read()
            For i = 0 To reader.FieldCount - 1
                Dim output As String
               '' output = Replace(reader(i).ToString, vbCr, "")
                output = Replace(reader.GetValue(i).ToString, vbCr, String.Empty)
                output = Replace(output, vbLf, String.Empty)
                output = Replace(output, "|", String.Empty)

                batch.Append(output)
                If i < reader.FieldCount - 1 Then
                    batch.Append("|")
                End If
            Next i

            batch.Append(vbCrLf)
            rowCount  = 1

            If rowCount Mod batchSize = 0 Then
                Dim now = Date.Now
                Dim sinceLastSeconds = DateDiff(DateInterval.Second, lastReport, now)
                lastReport = now
                LogEvent($"INFO: Processing row {rowCount} {sinceLastSeconds}s since last")
                
                Dim fileWriteStart = Date.Now
                'LogEvent($"INFO: Starting Writing {rowCount} row(s) to file for {report.ReportName}. {sinceLastSeconds}s since last")
                WriteToFile(filename, batch.ToString(), True)

                Dim fileWriteSeconds = DateDiff(DateInterval.Second, fileWriteStart, Date.Now)
                LogEvent($"INFO: Finished Writing another {batchSize} row(s) to file in {fileWriteSeconds}s for {report.ReportName}")

                batch.Clear()

            End If
        End While

        'LogEvent($"INFO: Starting Writing {rowCount} row(s) to {filename} for {report.ReportName}")
        WriteToFile(filename, batch.ToString(), True)
        LogEvent($"INFO: Finished Writing last row(s) to {filename} for {report.ReportName}")

        End Sub

Public Shared Function GetDataReader(ByVal strSQL As String, ByVal strConnection As String) As OleDb.OleDbDataReader
        Dim cnn As New OleDb.OleDbConnection(strConnection)
        Dim cmd As New OleDbCommand(strSQL, cnn)

        cnn.Open()

        GetDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

    End Function

當這個 Sub 啟動時，它會在不到 1 秒的時間內將一批行寫入文本檔案

07/12/2021 16:41:03: INFO: Finished Writing another 20000 row(s) to file in 0s for TAG_ATTRIBUTES
07/12/2021 16:41:03: INFO: Processing row 100000 0s since last

每個批次都比之前的批次略慢，并且每批次減少了 250 萬行，減慢到約 9 秒：

07/12/2021 16:51:47: INFO: Processing row 2560000 9s since last
07/12/2021 16:51:37: INFO: Finished Writing another 20000 row(s) to file in 0s for TAG_ATTRIBUTES

到 15,000,000：

08/12/2021 05:23:07: INFO: Processing row 15000000 145s since last
08/12/2021 05:20:42: INFO: Finished Writing another 20000 row(s) to file in 0s for TAG_ATTRIBUTES

在 Visual Studio 診斷工具中進行監控時，應用程式中的行程記憶體使用量始終保持在 100MB 以下。

這是 .Net Framework 4. AnyCPU

我想知道什么可能導致逐漸放緩？

我調查了在 StringBuilder 中構建輸出檔案的全部內容。發生相同的逐漸減慢，但記憶體使用量在 StringBuilder 填充時以 GB 為單位。

uj5u.com熱心網友回復：

為了證明我在評論中所說的話。Using塊關閉并處理物件。

Public Shared Function GetDataTable(ByVal strSQL As String, ByVal strConnection As String) As OleDb.OleDbDataTable
    Dim dt As New DataTable
    Using cnn As New OleDb.OleDbConnection(strConnection),
        cmd As New OleDbCommand(strSQL, cnn)
        cnn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

其中的部分GenerateTextSqlReportWithCurrent會發生變化。

    Dim ColumnNames As String() = From dc As DataColumn In dt.Columns
                                  Select dc.ColumnName
    Dim strNames = String.Join(", ", ColumnNames)
    WriteToFile(filename, strNames, False)

    Dim i As Integer
    For Each row As DataRow In dt.Rows
        For i = 0 To dt.Columns.Count - 1
            Dim output As String
            output = Replace(row(i).ToString, vbCr, String.Empty)
            output = Replace(output, vbLf, String.Empty)
            output = Replace(output, "|", String.Empty)

            batch.Append(output)
            If i < dt.Columns.Count - 1 Then
                batch.Append("|")
            End If
        Next
    Next

uj5u.com熱心網友回復：

實作 Mary 的建議，如下所示，需要將查詢中的所有資料加載到記憶體中（1500 萬個 DataRow 物件）。在創建了幾百萬個 DataRow 后，應用程式變慢了，1 小時后僅加載了 500 萬行，使用了 3 GB 的行程記憶體。應用程式正在執行 dt.Load(reader) 行。所以不幸的是，在處理如此大量的記錄時，這并不實用。

我將使用一個簡單的 SSIS 包來完成這項作業。我不明白如何，但它能夠在我的環境中在大約 10 分鐘內將 1500 萬行匯出到文本檔案。

Private Sub GenerateTextSqlReportWithCurrent(report As TblreportEntity, filename As String)

         Const batchSize = 20000

         Dim encryption As New ClassEncrypt
         LogEvent($"INFO:          Filling DataTable for report {report.ReportName}")
         Dim dt as DataTable = GetDataTable(report.Sql,IMOracle2.GetConnectString(My.Settings.DB_Instance, encryption.Decrypt(My.Settings.DB_UserID), encryption.Decrypt(My.Settings.DB_PWD)))
         LogEvent($"INFO: Finished Filling DataTable for report {report.ReportName}")

         Dim columnNames = From dc As DataColumn In dt.Columns
                 Select dc.ColumnName
         Dim strNames = String.Join("|", columnNames)

         WriteToFile(filename, strNames, False)
         
         Dim batch As New StringBuilder()

         Dim rowCount As Integer
         Dim i As Integer
         For Each row As DataRow In dt.Rows
             For i = 0 To dt.Columns.Count - 1
                 Dim output As String
                 output = Replace(row(i).ToString, vbCr, String.Empty)
                 output = Replace(output, vbLf, String.Empty)
                 output = Replace(output, "|", String.Empty)

                 batch.Append(output)
                 If i < dt.Columns.Count - 1 Then
                     batch.Append("|")
                 End If
             Next i
             
             batch.Append(vbCrLf)
             rowCount  = 1

             Dim lastReport As DateTime = DateTime.Now()

             If rowCount Mod batchSize = 0 Then
                 Dim now = Date.Now
                 Dim sinceLastSeconds = DateDiff(DateInterval.Second, lastReport, now)
                 lastReport = now
                 LogEvent($"INFO: Processing row {rowCount} {sinceLastSeconds}s since last")
                
                 Dim fileWriteStart = Date.Now
                 'LogEvent($"INFO: Starting Writing {rowCount} row(s) to file for {report.ReportName}. {sinceLastSeconds}s since last")
                 WriteToFile(filename, batch.ToString(), True)

                 Dim fileWriteSeconds = DateDiff(DateInterval.Second, fileWriteStart, Date.Now)
                 LogEvent($"INFO: Finished Writing another {batchSize} row(s) to file in {fileWriteSeconds}s for {report.ReportName}")

                 batch.Clear()

             End If

         Next row

        WriteToFile(filename, batch.ToString(), True)
        LogEvent($"INFO: Finished Writing last row(s) to {filename} for {report.ReportName}")

        End Sub

標籤：oracle vb.net oledbdatareader

上一篇：將字串中的時間和日期轉換為日期VB.net

下一篇：VB.Net合并資料表不遵守主鍵約束