VBA ADODB.Stream ADODB.Stream ReadTextには最適バイト数128KBが存在する #VBA

[再現失敗]VBA ADODB.Stream ADODB.Streamは長い1行では1行を読み込めず、正規表現のREPLACEは失敗することがある

ReadText メソッド(SQL Server 2022)

ReadText メソッドは、指定した文字数、行全体、またはストリーム全体を Stream オブジェクトから読み取り、結果の文字列を返します。解説
NumChar がストリームに残っている文字数を超える場合は、残りの文字のみが返されます。読み取られた文字列は、 NumChar で指定された長さと一致するように埋め込まれません。読み取る文字が残っていない場合は、値が null のバリアントが返されます。 ReadText を使用して逆方向に読み取ることはできません。

注意

ReadText メソッドは、テキストストリームで使用されます (Type は adTypeText)。バイナリストリーム (Type は adTypeBinary) の場合は、Read を使用します。

ActiveX データオブジェクト (ADO) Stream オブジェクトの ReadText メソッドを介して大量の XML データが返されるクエリは、実行にかなりの時間がかかる場合があります。ASP ページから呼び出される COM+ コンポーネントで実行すると、ユーザーのセッションがタイムアウトになる可能性があります。ADO は Stream オブジェクトデータを UTF-8 エンコードから Unicode に変換します。このような大量のデータを一度に変換する際に頻繁に発生するメモリ再割り当てには、非常に時間がかかります。解決するには、ADO コマンドオブジェクトの ReadText メソッドを繰り返し呼び出し、より少ない文字数を指定します。テストでは、128K (131,072) に相当する値が最適であることが示されています。応答時間は、この値が減少すると減少します。詳細については、サポート技術情報280067「PRB: ADO ストリームオブジェクトの ReadText メソッドを使用した SQL Server 2000 から非常に大きな XML ドキュメントの取得が遅くなる可能性があります」を参照してください。

サポート技術情報の発掘

しかし、現存していないのでミラーサイトから探し当てた。
https://mskb.pkisolutions.com/kb/280067

PRB：ADOストリームオブジェクトのReadTextメソッドを使用してSQLServer2000から非常に大きなXMLドキュメントを取得すると時間がかかる場合がある
症状

ActiveXデータオブジェクト（ADO）StreamオブジェクトのReadTextメソッドを介して大量のXMLデータが返される結果となるクエリは、実行にかなりの時間がかかる場合があります。これがASPページから呼び出されるCOM+コンポーネントで行われる場合、ユーザーのセッションがタイムアウトする可能性があります。

原因

ADOは、ストリームオブジェクトデータをUTF-8エンコーディングからUnicodeに変換します。このような大量のデータを一度に変換する際に頻繁にメモリを再割り当てするのは、非常に時間がかかります。

解決方法

ADOコマンドオブジェクトのReadTextメソッドを繰り返し呼び出し、指定する文字数を少なくします。テストでは、128K（131,072）に相当する値が最適であることが示されています。この値が減少すると、応答時間は減少します。

詳細

行動を再現する手順

VisualBasicで新しい標準EXEプロジェクトを開きます。 Form1はデフォルトで作成されます。

ActiveXデータオブジェクト2.6への参照を設定します。

Form1にCommandButtonを配置します。

CommandButtonのClickイベントに次のコードを配置します。
On Error GoTo ErrorHandler
   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim str As ADODB.Stream
   Dim strXMLOutput As String
   Dim varStart     As Variant
   Dim varEnd       As Variant

   Const ReadBytes = 131072

   Screen.MousePointer = vbHourglass

   Set cnn = New ADODB.Connection
    
   With cnn
      .CursorLocation = adUseClient
      .Open "provider=sqloledb;data source=MyServer\MyInstance;initial & _
      catalog=MyDatabase;user id=MyUserID;password=MyPassword;"
   End With

   Set str = New ADODB.Stream
    
   With str
      .Type = adTypeText
      .LineSeparator = adCRLF
      .Mode = adModeRead
      .Open
   End With

   Set cmd = New ADODB.Command
    
   With cmd
      Set .ActiveConnection = cnn
      .Properties("Output Stream").Value = str
      .CommandType = adCmdText
      .CommandText = "SELECT * FROM BigTable FOR XML AUTO"
      .Execute , , adExecuteStream
      Set .ActiveConnection = Nothing
   End With

   cnn.Close

   str.Position = 0
   
   varStart = Now
  
  'This technique results in a much faster assignment.
  'Uncomment this section for faster response.
'   With str
'      Do While Not .EOS
'         strXMLOutput = strXMLOutput & .ReadText(ReadBytes)
'      Loop
'   End With
   
  'Single call technique results in a much slower assignment.
  'Comment this out when uncommenting the code above.
   strXMLOutput = str.ReadText
   
   varEnd = Now

   MsgBox "ReadText completed:" & vbCrLf & "Start=" & varStart & ", End=" & _
          varEnd & vbCrLf & "Total bytes read: " & Len(strXMLOutput), vbOKOnly + vbInformation, "ReadText"

Bye:
   Set str = Nothing
   Set cmd = Nothing
   Set cnn = Nothing

   Screen.MousePointer = vbDefault

   Exit Sub
   
ErrorHandler:
   MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "Stream Error"
   GoTo Bye
Modify the SQL query as appropriate for your database.
Run the application with the ReadText loop commented out, as in the preceding code.
Comment out the single ReadText method call, and uncomment the ReadText loop. Note that there is a significant improvement in response time.

This has been tested with a 60,000 row recordset, returning a 12.8 MB XML document. Using a single call to the ReadText method, response time was over 10 minutes; using the loop, response time was reduced to less than thirty seconds.

MyServer、MyInstance、MyDatabase、MyUserID、およびMyPasswordをデータベースサーバーの適切な値に置き換えます。
データベースに応じてSQLクエリを変更します。
前のコードのように、ReadTextループをコメントアウトしてアプリケーションを実行します。
単一のReadTextメソッド呼び出しをコメント化し、ReadTextループのコメントを解除します。応答時間が大幅に改善されることに注意してください。

これは60,000行のレコードセットでテストされており、12.8MBのXMLドキュメントを返します。 ReadTextメソッドへの1回の呼び出しを使用すると、応答時間は10分を超えました。ループを使用すると、応答時間が30秒未満に短縮されました。

コードを見る限り、接続文字列が異なるだけで、adodbは同じである。
このため、現在のVBAでもADODBには同様の問題が存在する可能性がある。
12.8 Megabyte は　12800KBとなる。60,000で割ると2.13KBとなる。ということは改行は無視していると思われる。
この方法の難点は改行が無視されてしまうことである。しかもその場合、やり直しが効かない。
固定長であれば有効だが、いまどきそんなデータベースはないだろう。

ADODB.Streamでゴミデータ(Null文字)がある場合の挙動について
最初はこれなのかとも疑ったが、この場合はすべて読み込まれるので、途中までではない。
しかしこれは効果の有無に関わらず実行すべきかもしれない。

UTF-8BOMについて

今回のファイルはShift-Jis（ANSI）だが、UTF-8BOMは読み込みに失敗する場合がある。
UTF-8(BOM付き)のインポートについて
そのくせ出力するUTF-8はBOMアリになる。
エクセルVBAでBOM無しのUTF-8でCSVファイルなどを出力する方法 - いつも隣にITのお仕事
しかし、UTF-8Nにすると今度は文字コードの判定でバグる場合があるとされている。
したがって、UTF-8の場合、一度別にコピーして、BOMの除去ーNULL値の除去という流れにすべきだろう。