sql - Powershell 的 StringBuilder 性能不佳？

Question

我正在尝试构建查询，以便可以将大量数据插入到 sqlite3 表中。我已经尝试了几种方法来执行此操作，包括PSSQLite，它应该能够获取 DataTable 并轻松插入它。即使只有 10,000 条记录，它也需要将近 40 分钟才能运行，我不知道为什么。

我的另一个选择是构建一个查询并使用另一种方法（例如 Invoke-Sqlcmd）执行它。我试过用 StringBuilder 来做这件事，只是构建字符串需要 2 多分钟。就像我说的，它只有 10,000 条记录，所以根据我准备的内容，TOPS 应该只需要 10-15 秒。考虑到我至少有几百万条记录要导入，我真的需要这个更快。

这是我正在使用的代码。我只是在这里遗漏了什么吗？

$sb = [System.Text.StringBuilder]::new()
$sb.AppendLine("BEGIN TRANSACTION")
foreach ($document in $documents) {
   
  $null = $sb.AppendLine("INSERT or IGNORE into documents(DocId,Submid,docno,Tray,Pieceno,CreateDate,Account,AccName,AccAddr1,AccAddr2,AccAddr3,AccAddr4,AccCity,AccState,AccZip,BCDP,BarcodeID,ServTypeID,Mailerid,SerialNo,Sys_Name,Sys_Addr1,Sys_Addr2,Sys_Addr3,Sys_Addr4,Sys_City,Sys_State,Sys_Zip)");
  $null = $sb.AppendLine("VALUES('$($document.DocId)','$($document.Submid)','$($document.docno)','$($document.Tray)','$($document.Pieceno)','$($document.CreateDate)','$($document.Account)','$($document.AccName)','$($document.AccAddr1)','$($document.AccAddr2)','$($document.AccAddr3)','$($document.AccAddr4)','$($document.AccCity)','$($document.AccState)','$($document.AccZip)','$($document.BCDP)','$($document.BarcodeID)','$($document.ServTypeID)','$($document.Mailerid)','$($document.SerialNo)','$($document.Sys_Name)','$($document.Sys_Addr1)','$($document.Sys_Addr2)','$($document.Sys_Addr3)','$($document.Sys_Addr4)','$($document.Sys_City)','$($document.Sys_State)','$($document.Sys_Zip)')")
    
}
    
$sb.AppendLine("COMMIT")
$query = $sb.ToString();
#Invoke-SqliteQuery $ref_db $query #commenting this out, because I haven't even attempted the insert because StringBuilder is not optimized enough yet.

在这种情况下，$documents 是一个通用对象，包含 INSERT 语句中的每个字段。大多数字段都填充了一个字符串，其中一些是空白的。

#EDIT：我在设置了断点的 Powershell ISE 中运行它，这会导致性能问题吗？

标签： sqlstringpowershellsqlitestringbuilder

Answer 1

在StringBuilder讨论您的问题之前，让我们看一下 SQLite：

如果您将 SQL 更改为提交单个多行INSERT语句，而不是像您现在所做的那样提交 10000 个单独的语句，我希望您会看到处理时间的差异INSERT- 换句话说：

$null = $sb.AppendLine("INSERT or IGNORE into documents(DocId,Submid,docno,Tray,Pieceno,CreateDate,Account,AccName,AccAddr1,AccAddr2,AccAddr3,AccAddr4,AccCity,AccState,AccZip,BCDP,BarcodeID,ServTypeID,Mailerid,SerialNo,Sys_Name,Sys_Addr1,Sys_Addr2,Sys_Addr3,Sys_Addr4,Sys_City,Sys_State,Sys_Zip)")
$null = $sb.AppendLine("VALUES")

foreach ($document in $documents) {
  # Add separate value tuple for each document, add trailing `,`
  $null = $sb.AppendLine("('$($document.DocId)','$($document.Submid)','$($document.docno)','$($document.Tray)','$($document.Pieceno)','$($document.CreateDate)','$($document.Account)','$($document.AccName)','$($document.AccAddr1)','$($document.AccAddr2)','$($document.AccAddr3)','$($document.AccAddr4)','$($document.AccCity)','$($document.AccState)','$($document.AccZip)','$($document.BCDP)','$($document.BarcodeID)','$($document.ServTypeID)','$($document.Mailerid)','$($document.SerialNo)','$($document.Sys_Name)','$($document.Sys_Addr1)','$($document.Sys_Addr2)','$($document.Sys_Addr3)','$($document.Sys_Addr4)','$($document.Sys_City)','$($document.Sys_State)','$($document.Sys_Zip)'),")
}
    
# trim trailing newline + comma on last insert value before adding COMMIT statement
$query = $sb.ToString().TrimEnd("`r`n,") + "`r`nCOMMIT"
Invoke-SqliteQuery $ref_db $query

---

通过避免可扩展的字符串而使用 , 可以进行微小的优化$sb.AppendFormat()，即：

$sb = [System.Text.StringBuilder]::new()
$sb.AppendLine("BEGIN TRANSACTION")
$null = $sb.AppendFormat('VALUES ({0}, {2}, ...)', $doc.DocId, $doc.SubmId, ...).AppendLine()

...但这可能不是问题。

在Windows PowerShell 中，一旦大小超过 .NET Framework 中大型对象堆缓存 (85Kb) 的阈值，字符串操作（无论是通过直接连接还是使用字符串构建）都会具有一些非常时髦的性能特征。

这似乎不会在 .NET Core 中发生，因此升级到更新版本的 PowerShell（例如 PowerShell 7）可能会证明可以完全消除此问题。

如果您需要以 Windows PowerShell 为目标，您可能只想将 SQL 脚本直接写入磁盘并使用以下命令读回Invoke-SqliteQuery -InputFile：

$scriptFile = New-Item import.sql

try{
  $fileWriter = $scriptFile.CreateText()
  $fileWriter.WriteLine("INSERT or IGNORE into documents(DocId,Submid,docno,Tray,Pieceno,CreateDate,Account,AccName,AccAddr1,AccAddr2,AccAddr3,AccAddr4,AccCity,AccState,AccZip,BCDP,BarcodeID,ServTypeID,Mailerid,SerialNo,Sys_Name,Sys_Addr1,Sys_Addr2,Sys_Addr3,Sys_Addr4,Sys_City,Sys_State,Sys_Zip)")

  foreach ($document in $documents) {
    $fileWriter.WriteLine("VALUES")
    $fileWriter.WriteLine("('$($document.DocId)','$($document.Submid)','$($document.docno)','$($document.Tray)','$($document.Pieceno)','$($document.CreateDate)','$($document.Account)','$($document.AccName)','$($document.AccAddr1)','$($document.AccAddr2)','$($document.AccAddr3)','$($document.AccAddr4)','$($document.AccCity)','$($document.AccState)','$($document.AccZip)','$($document.BCDP)','$($document.BarcodeID)','$($document.ServTypeID)','$($document.Mailerid)','$($document.SerialNo)','$($document.Sys_Name)','$($document.Sys_Addr1)','$($document.Sys_Addr2)','$($document.Sys_Addr3)','$($document.Sys_Addr4)','$($document.Sys_City)','$($document.Sys_State)','$($document.Sys_Zip)')")
    $fileWriter.WriteLine("")
  }
  $fileWriter.WriteLine("COMMIT")
}
finally{
  $fileWriter.Close()
}    

Invoke-SqliteQuery $ref_db -InputFile import.sql