我有以下问题。通常,当我在VBA中使用SQL命令生成字符串时,检索结果的时间不超过几秒钟(一些运行的查询相当"繁重")。
然而,在一种"轻松"的情况下,从VBA发送到SQL需要几分钟的时间。但是,当我在SQL Server中单独运行SQL代码时,代码运行时间不到一秒钟。所以我想,我的VBA和SQL之间的连接函数有问题吗?我在下面列出了两个代码:
首先,我有以下代码来建立VBA脚本和SQL Server之间的连接(输入:SQL字符串;输出:SQL Server表结果)
Private Function GetSQLData(ByVal SQLString As String)
Dim sCon As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
sCon = "Provider = "MSDASQL.1;"
sCon = sCon & "driver={SQL Server};"
sCon = sCon & "Server=PCNAMESQLEXPRESS;"
sCon = sCon & "Database=HELLOFORUM;"
sCon = sCon & "uid="";"
sCon = sCon & "pwd:="";"
'/ I create the connection to SQL Server
Set cnn = New ADODB.Connection
With cnn
.Open sCon
.CursorLocation = adUseClient
.CommandTimeout = 0
End With
'/ Load results
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseServer
.Open Source:=SQLString, ActiveConnection:=cnn, CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, Options:=adCmdText
End With
If rst.State = 1 Then
If Not rst.EOF Then
GetSQLData = rst.GetRows()
End If
End If
End Function
我有一个数据库,看起来如下:
ID timetag val
ID01 1990-01-01 00:00:00 20
ID01 1990-01-01 00:01:00 23
[...]
产生问题的代码如下:
WITH rows AS (
SELECT *,Row_Number() Over (ORDER BY timetag) as RN FROM [Database.dbo]
where ID = 'ID01' AND timetag between '1990-01-01 00:00:00' AND '1990-01-20 00:00:00')
SELECT DATEDIFF(minute,mc.timetag,mp.timetag) as differ1, mc.timetag as timetag1 ,mp.timetag as timetag2
FROM rows mc JOIN rows mp On mc.rn = mp.rn - 1 where DATEDIFF(minute,mc.timetag,mp.timetag) > 1 order by timetag1
此查询的输出是一个表,其中后续时间标记之间的差不等于1(min)。例如:
differ1 timetag1 timetag2
2 1990-07-04 00:00:00 1990-07:04 00:02:00
正如我所说,它在SQL server中运行得很快,但当我尝试从VBA执行查询时运行得很慢。有什么建议吗?特别是让我按照"最佳/更好的做法"选项从VBA连接到SQL server?
更新:更奇怪的是,它从第7个月开始就很慢。
如果您只打算读取记录,可以尝试将LockType更改为adLockReadOnly。
您还可以将光标位置从客户端更改为服务器(AdUseServer)。