Powershell SQL Server插入 - 最佳实践

Question

我有一个脚本，每天会遍历一个目录中的几千个文件，并希望在foreach循环中处理每个文件时更新SQL Server表中的详细信息。

我已经使用以下内容使其正常工作：

Invoke-Sqlcmd -Query "INSERT INTO $dbTable (name, date, worknum, identifier) VALUES ('$name','$date','$worknum','$identifier')" 
              -ServerInstance $dbHost -Database $dbName -ErrorAction Stop

虽然这样做可以正常工作，但我想知道将此方法更改为在文件处理开始之前建立SQL Server连接并在脚本结束时关闭是否有任何好处？类似于这样的操作。

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$dbHost;Database=$dbName;Integrated Security=True;"

<foreach loop>

$SqlConnection.Close()

我不关心脚本的运行速度，因为它已经相当快了，我更担心的是对数据库性能的影响。

- Rob Berry

---

如果脚本运行速度很快，那可能没有问题，但是知道你预计要插入的行数大致数量会有所帮助。如果行数很多，使用一个大事务来工作可以减小日志大小。 - Neil P

我非常确定，因为SQL-Server/ADO.NET使用连接池，所以不会有任何明显的差异。但是要想知道它，您必须进行测试。;-) - MatSnow

感谢各位的评论。每处理一个文件，它就会插入一行数据，大约每天处理2千个文件。我想我会保留脚本不变。谢谢，Rob。 - Rob Berry

Answer 1

正如评论中所述，您需要根据实例配置和现有工作负载进行测试，以确定解决方案是否具有良好的性能。

我曾经有过一个类似的经历，使用PowerShell“应用程序”将一系列账户标识符插入到表格中以供我们进一步处理。该应用程序最初对每个ID进行插入并逐个迭代。这对于大多数用户来说都可以接受，但偶尔会有人放入100k+的ID，而应用程序的性能就会变得非常糟糕！（但SQL服务器仍然按预期执行）使用SqlBulkCopy可以极大地加速客户端的处理速度，而SQL服务器几乎没有可感知的影响。（只有那些有大量记录的人才会受益。但是少于100条记录的情况下没有真正的变化。）

Write-DataTable和Out-DataTable是很方便的函数，可以使此过程更加容易。

我的感受放在一边，最佳实践是...。

Eugene Philipov有一篇很好的文章，介绍了他们对单值插入、多值插入和BulkCopy之间数据加载方法性能方面的测试。他们发现，你要插入的列数对操作速度有很大影响。插入的列数越多，使用多个值进行插入或使用批量复制所获得的好处就越小。然而，每条记录使用单个插入始终比较慢（执行时间更长）。

更快的执行==更少的阻塞/消耗需要其他工作流程的资源的机会。

- Brandon McClure