从VBA转到SQL Server 2008 R2时，如何提高数据库性能？

I am working on a financial application that performs CRUD operations from Excel to SQL Server 2008 R2. The application uses VBA and ADO. I am trying to optimize the performance of an operation that loops through thousands of rows and sends updates to the database. Currently, a stored procedure gets called once for each row.

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

When users have slow connections to the application the performance is slow too, enough that they have started complaining. To optimize the performance, I tried:

当用户与应用程序的连接速度较慢时,性能也很慢,足以让他们开始抱怨。为了优化性能,我试过:

Batching all of the operations into an XML file and sending them all at once. Unfortunately the performance got worse.

将所有操作批处理为XML文件并立即将它们全部发送。不幸的是,性能变差了。

Next I thought of batching all of the updates in a table-valued parameter. However, VBA and ADO can’t use table-valued parameters.

接下来我想到在表值参数中批量处理所有更新。但是,VBA和ADO不能使用表值参数。

I am running out of things to try. How do I improve the database performance when going from VBA to SQL Server 2008 R2?

我的事情已经不多了。从VBA转到SQL Server 2008 R2时,如何提高数据库性能?

2 个解决方案

#1

I figured it out. While there is no table-valued parameter (TVP) object available in ADO, you can execute raw SQL statements on a SQL Server connection. The solution is to:

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

Build a SQL statement that uses a TVP

构建使用TVP的SQL语句

Execute the SQL statement on the ADO connection.

在ADO连接上执行SQL语句。

For example:

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)

The solution is described in more detail in an article about calling TVPs from MS Access: Using SQL Server 2008 Table-valued Parameters in Access 2007

在有关从MS Access调用TVP的文章中更详细地描述了该解决方案:在Access 2007中使用SQL Server 2008表值参数

Using a TVP gave me a nice performance boost. Hope this helps someone else. Cheers.

使用TVP给我带来了不错的性能提升。希望这有助于其他人。干杯。

#2

To be honest VBA/ADO speed is pretty craptacular (in comparison to web sites) in my experience. There is arcane overhead in this method of database connection especially when executing SQL statements, and not a lot to help to troubleshoot it.

说实话,根据我的经验,VBA / ADO的速度非常棒(与网站相比)。这种数据库连接方法存在神秘的开销,尤其是在执行SQL语句时,并没有太多帮助对其进行故障排除。

One avenue to try is to update recordset objects rather than executing stored procedures, I have found this to help in some cases.

尝试的一个途径是更新记录集对象而不是执行存储过程,我发现这在某些情况下有所帮助。

That said depending on the complexity of your updates, that may just make things worse.

也就是说,根据您的更新的复杂性,这可能会使事情变得更糟。

#1