excel - 从 VBA 迁移到 SQL Server 2008 R2 时如何提高数据库性能？

Question

我正在开发一个财务应用程序，该应用程序执行从 Excel 到 SQL Server 2008 R2 的 CRUD 操作。该应用程序使用 VBA 和 ADO。我正在尝试优化循环数千行并向数据库发送更新的操作的性能。目前，每行都会调用一次存储过程。

当用户与应用程序的连接速度很慢时，性能也会很慢，足以让他们开始提示。为了优化性能，我尝试了:

1. 将所有操作批处理到一个 XML 文件中并一次性发送。不幸的是，表现变得更糟。
2. 接下来我考虑将所有更新分批放入表值参数中。但是，VBA 和 ADO 不能使用表值参数。

我已经没有什么可以尝试的了。从 VBA 迁移到 SQL Server 2008 R2 时如何提高数据库性能？

Answer 1

我明白了。虽然 ADO 中没有可用的表值参数 (TVP) 对象，但您可以在 SQL Server 连接上执行原始 SQL 语句。解决办法是:

1. 构建使用 TVP 的 SQL 语句
2. 在 ADO 连接上执行 SQL 语句。

例如:

Dim cnSQLServer As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

Set cnSQLServer = New ADODB.Connection
cnSQLServer.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security = SSPI"

' Build a SQL statement that uses a TVP
strSQL = "DECLARE @ReturnMessage nvarchar(255) " + _
    "DECLARE @catType CategoryTableType " + _
    "INSERT INTO @catType" + _
    "EXEC ('" + _
    "SELECT ''Organic'', ''Organically grown produce and grains'' " + _
    "SELECT ''Conventional'', ''Non-organically grown produce and grains'' " + _
    "SELECT ''Irish'', ''Mrs. O''''Leary''''s creamery products'' " + _
    "') " + _
    "EXEC dbo.usp_InsertCategory @catType, @ReturnMessage OUTPUT; " + _
    "SELECT @ReturnMessage as ReturnMessage'"

' Execute the SQL statement on the ADO connection.
Set rst = cnSQLServer.Execute(strSQL)

有关从 MS Access 调用 TVP 的文章中更详细地描述了该解决方案:Using SQL Server 2008 Table-valued Parameters in Access 2007

使用 TVP 给我带来了很好的性能提升。希望这对其他人有帮助。干杯。