VB.NET写入大TXT文件时出现OutOfMemory Exception问题求助

从数据库按日期范围查询数据（范围越小数据量越少），将数据拼接后写入文件。测试360天范围时数据行数超50万，执行报错；25-35万行时无异常。以下是修改后的代码（原代码同样报错）：

For Each dtrT As DataRow In dttTmp.Rows
    sbFile.Append(company & "§" & dtrT!type & "|" &
                  company & "|" &
                  strKeyNumReg & "|" &
                  ConvStr(dtrT!tm_numdoc) & "|" &
                  ConvStr(dtrT!xx_codart) & "|" &
                  ConvStr(dtrT!mm_row) & "|" &
                  (ConvStr(dtrT!mm_codart) & IIf(NTSCStr(dtrT!mm_fase) <> "0", "." & ConvStr(dtrT!mm_fase), "").ToString).Trim & "|" &
                  (ConvStr(dtrT!mm_descr) & " " & ConvStr(dtrT!mm_desint, True)).Trim & desc & "|" &
                  ConvStr(dtrT!mm_ump) & "|" &
                  ConvDec(dtrT!mm_quant).ToString("0.00000") & "|" &
                  "0" & "|" &
                  ConvDec(dtrT!xx_price).ToString("0.0000") & "|" &
                  ConvDec(dtrT!mm_value).ToString("0.00") & "|" &
                  ConvDec(dtrT!mm_disc1).ToString("0.00") & "|" &
                  ConvDec(dtrT!mm_disc2).ToString("0.00") & "|" &
                  IIf(ConvStr(dtrT!an_type) = "C", 0, 1).ToString & "|" &
                  ConvStr(dtrT!an_costumer) & "|" &
                  ConvStr(dtrT!tm_currency) & "|" &
                  ConvStr(dtrT!cod_dest) & "|" &
                  ConvData(dtrT!data_cons) & "|" &
                  ConvDec(dtrT!xx_cod_state_row) & "|" &
                  GetDescription(ConvDec(dtrT!xx_cod_state_row)) & "|" &
                  ConvData(dtrT!xx_lastupdate) & vbCrLf )
Next

If dttTmp.Rows.Count > 0 Then
    Using sw As StreamWriter = New StreamWriter(strFileOut, False, Encoding.UTF8)
        Const bufferSize As Integer = 10000
        Dim buffer As Char() = New Char(9999) {}
        Dim i As Integer = 0

        While i < sbFile.Length
            Dim count As Integer = Math.Min(bufferSize, sbFile.Length - i)
            sbFile.CopyTo(i, buffer, 0, count)
            sw.Write(buffer, 0, count)
            i += bufferSize
        End While
    End Using
End If

问题分析与优化方案 #

核心问题 #

当前代码先把所有数据一次性拼接到StringBuilder（sbFile），当数据量超50万行时，StringBuilder会占用大量内存，引发内存不足异常，这就是大数量级数据下报错的原因。

优化方案 #

不需要先把所有内容存在内存里，而是遍历一行就写入一行，直接减少内存占用，同时简化代码：

If dttTmp.Rows.Count > 0 Then
    Using sw As StreamWriter = New StreamWriter(strFileOut, False, Encoding.UTF8)
        For Each dtrT As DataRow In dttTmp.Rows
            Dim line As String = company & "§" & dtrT!type & "|" &
                                  company & "|" &
                                  strKeyNumReg & "|" &
                                  ConvStr(dtrT!tm_numdoc) & "|" &
                                  ConvStr(dtrT!xx_codart) & "|" &
                                  ConvStr(dtrT!mm_row) & "|" &
                                  (ConvStr(dtrT!mm_codart) & IIf(NTSCStr(dtrT!mm_fase) <> "0", "." & ConvStr(dtrT!mm_fase), "").ToString).Trim & "|" &
                                  (ConvStr(dtrT!mm_descr) & " " & ConvStr(dtrT!mm_desint, True)).Trim & desc & "|" &
                                  ConvStr(dtrT!mm_ump) & "|" &
                                  ConvDec(dtrT!mm_quant).ToString("0.00000") & "|" &
                                  "0" & "|" &
                                  ConvDec(dtrT!xx_price).ToString("0.0000") & "|" &
                                  ConvDec(dtrT!mm_value).ToString("0.00") & "|" &
                                  ConvDec(dtrT!mm_disc1).ToString("0.00") & "|" &
                                  ConvDec(dtrT!mm_disc2).ToString("0.00") & "|" &
                                  IIf(ConvStr(dtrT!an_type) = "C", 0, 1).ToString & "|" &
                                  ConvStr(dtrT!an_costumer) & "|" &
                                  ConvStr(dtrT!tm_currency) & "|" &
                                  ConvStr(dtrT!cod_dest) & "|" &
                                  ConvData(dtrT!data_cons) & "|" &
                                  ConvDec(dtrT!xx_cod_state_row) & "|" &
                                  GetDescription(ConvDec(dtrT!xx_cod_state_row)) & "|" &
                                  ConvData(dtrT!xx_lastupdate) & vbCrLf
            sw.WriteLine(line)
        Next
    End Using
End If

额外优化点 #

1. 数据库查询分页：如果数据库支持，将查询拆分为多个分页请求（比如每次查1万行），避免一次性加载50万行DataTable占用过多内存。
2. 批量拼接写入：若要保留批量写入逻辑，可设置StringBuilder初始容量，每拼接1000行就写入文件并清空StringBuilder，避免内存持续增长。

内容的提问来源于stack exchange，提问作者DevMD