SQL Query using VBA and ADO record set - Code takes far less time to execute when run the second time

Question

I have a VBA Code that involves, among other things, looping of an SQL query written as a Function. When I run the code for the first time, it takes nearly 155 seconds(which is understandable given the complexity of the calculation), when I run it for the second time it takes about 19 seconds. I would like to know why? Also how can I make it run faster? The SQL server version is 2008.

FunctionX() below, is used subsequently in For and Do while Loops, so I understand that a new Connection is created every time it runs.

Option Explicit
Public
Public conn As Object 'connection
________________________________________
Sub CreateConnection ()

Set conn = CreateObject("ADODB.connection")
Dim connstring As String

connstring = "Driver={SQL Server};Server=172.20.1.172;Database=WindAccess_dat_MRK;Uid=MRK_user;Pwd=MRK010usr;"
conn.Open connstring
conn.CommandTimeout = 120

End Sub
____________________________________

FunctionX(ActiveConnection as Object, arg1,arg2,....)

Dim SQLqryfunc1 As String
Dim SQLqryfunc2 As String
Dim SQLqryfunc3 As String
Dim fValue As Variant
Set wb = ThisWorkbook
Dim recset As Object
Set recset = CreateObject("ADODB.Recordset")
Dim ItemString1 As String
Dim ItemString2 As String
Dim ItemString3 As String
Dim ItemString4 As String
Dim ItemString5 As String
Dim StartZeit As String
Dim EndZeit As String

Select Case ChooseZeit
    Case 1
    StartZeit = Format(InputDateErsterTag, "yyyy-mm-dd hh:mm:ss.000")
    EndZeit = Format(InputDateLetzterTag, "yyyy-mm-dd 23:50:ss.000")
    ItemString3 = " AND HAL150.WTUR_AVG.MaxPwrSetpoint >=" & MaxPwrSetpoint_AvgVal
    ItemString4 = " AND HAL150.WTUR_MIN.MaxPwrSetpoint >=" & MaxPwrSetpoint_MinVal
    ItemString5 = " AND HAL150.WTUR_AVG.W >=" & WAvg_Min
    Case 2
    StartZeit = Format(InputDateErsterTag, "yyyy-mm-dd hh:mm:ss.000")
    EndZeit = Format(InputDateLetzterTag, "yyyy-mm-dd hh:mm:ss.000")
    ItemString3 = ""
    ItemString4 = ""
    ItemString5 = ""
End Select

Select Case Item
    Case 1 ' 1= m
    ItemString1 = " count(case when WindSpeed_Avg"
    ItemString2 = " then 1 end)"
    Case 2 ' 2= p
    ItemString1 = " sum(case when WindSpeed_Avg"
    ItemString2 = " then Leistung_Avg end)"
End Select

SQLqryfunc1 = "SELECT" _
& ItemString1 _
& " >=" _
& myWindSpeedFrom _
& " and" _
& " WindSpeed_Avg" _
& " <" _
& myWindSpeedTo _
& ItemString2 _
& " as '13.48 bis 14'" _

SQLqryfunc2 = " from" _
& " (" _
& " SELECT Time_Stamp, LDName as MONr,HAL150.WTUR_AVG.W as Leistung_Avg," _
& " WdSpd as WindSpeed_Avg, HAL150.WTUR_AVG.MaxPwrSetpoint as MaxPwrSetpoint_Avg,HAL150.WTUR_MIN.MaxPwrSetpoint as MaxPwrSetpoint_Min" _
& " FROM WSKD.RegCtrPeriodBlock" _
& " INNER JOIN  HAL150.WNAC_AVG " _
& " ON WSKD.RegCtrPeriodBlock.idPBlock = HAL150.WNAC_AVG.idPBlock" _
& " INNER JOIN  HAL150.WTUR_AVG" _
& " ON WSKD.RegCtrPeriodBlock.idPBlock = HAL150.WTUR_AVG.idPBlock" _
& " INNER JOIN WSKD.Components" _
& " ON WSKD.RegCtrPeriodBlock.IdLD = WSKD.Components.IdLD" _
& " INNER JOIN HAL150.WTUR_MIN" _
& " ON WSKD.RegCtrPeriodBlock.idPBlock = HAL150.WTUR_MIN.idPBlock" _
& " WHERE Time_Stamp BETWEEN" _
& " '" & StartZeit & "'" _
& " AND" _
& " '" & EndZeit & "'" _
& " AND" _
& " LDName in" _
& " (" & "'" & MONr & "'" & ")" _
& ItemString3 _
& ItemString4 _
& ItemString5 _
& " )" _
& " tbl"

SQLqryfunc3 = SQLqryfunc1 & SQLqryfunc2
'Debug.Print SQLqryfunc3
recset.Open SQLqryfunc3, myActiveConnection
fValue = recset.Getrows
Functionx = fValue(0, 0)
'Debug.Print Functionx

End Function

score 1 · Accepted Answer · answered Feb 23 '21 at 17:39

The second time your queries are executed they are likely cached by SQL server.

Lowest hanging fruit to making your query faster would be to take a sample output from this function and run it through SQL Server Management Studio with execution plan enabled: https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver15 . It'll give you some tips on what indexes could be added to certain columns to make your query faster. If you have create privileges on database you can create some indexes to speed things up a bit. You'll also be able to see which parts of your query are the most expensive.

Also, dynamic SQL is hard on SQL server precisely because it has to come up with a new optimized execution plan each time and isn't able to keep it for very long. The reason it slows down again is because the optimized execution plan it creates the first time is ejected from the cache. SQL concatenation like this & " WHERE Time_Stamp BETWEEN" _ & " '" & StartZeit & "'" _ also opens you up to SQL injection attacks if the input is from the user. So if you can use parameters in your query instead using ADODB much like in this example answer here: https://stackoverflow.com/a/10353908/4641232 it would make your code more secure.

Another idea to optimize your code might be to move the processing of conditionals onto SQL server. For example, this case here:

Select Case ChooseZeit
    Case 1
    StartZeit = Format(InputDateErsterTag, "yyyy-mm-dd hh:mm:ss.000")
    EndZeit = Format(InputDateLetzterTag, "yyyy-mm-dd 23:50:ss.000")
    ItemString3 = " AND HAL150.WTUR_AVG.MaxPwrSetpoint >=" & MaxPwrSetpoint_AvgVal
    ItemString4 = " AND HAL150.WTUR_MIN.MaxPwrSetpoint >=" & MaxPwrSetpoint_MinVal
    ItemString5 = " AND HAL150.WTUR_AVG.W >=" & WAvg_Min
    Case 2
    StartZeit = Format(InputDateErsterTag, "yyyy-mm-dd hh:mm:ss.000")
    EndZeit = Format(InputDateLetzterTag, "yyyy-mm-dd hh:mm:ss.000")
    ItemString3 = ""
    ItemString4 = ""
    ItemString5 = ""
End Select

Instead of having two possible WHERE clauses, have your VBA create a SQL script that includes both and have SQL server make the distinction. For example, the WHERE clause in your output might look like this:

...
WHERE
...
AND
(
    (
        2 = 1 -- ChooseZeit == 2 in VB so that's where the 2 comes from.
        AND HAL150.WTUR_AVG.MaxPwrSetpoint >= 4 -- I just picked a random number here.
        AND HAL150.WTUR_MIN.MaxPwrSetpoint >= 2 -- I just picked a random number here.
        AND HAL150.WTUR_AVG.W >= 3 -- I just picked a random number here.
    )
    OR 
    (
        2 = 2 -- ChooseZeit == 2 in VB so that's where the 2 comes from.
    )
)
...

With your WHERE clause constructed this way you give SQL Server a constant query structure to build an optimized path. If you use parameters, you can also now more easily move the scripted query created by VB into a stored procedure that you can compile on SQL server to possibly give even more gains.

I do not have write/create privileges on the database. I'll give the other two a shot — OldNick, Feb 23 '21 at 18:22

SQL Query using VBA and ADO record set - Code takes far less time to execute when run the second time

1 Answers1