Query.CommandText

SVB Example

Optimizing spreadsheet queries using cached DB connections:

Option Explicit

'Just used for timing the queries
Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub Main

    Dim dtStart As Long
    Dim dtEnd As Long
    Dim result As Double
    Dim rptStr As String
    Dim rpt As New Report

    Dim ss As Spreadsheet

    dtStart = GetTickCount()
    RunQueries(ss) 'Run the queries for the first time
    'See how long the process took to run
    dtEnd = GetTickCount()
    result = (dtEnd - dtStart)/1000
    rptStr = "First set of queries: " & CStr(result) & " seconds."

    dtStart = GetTickCount()
    RunQueries(ss) 'Requery, using a cached DB connection
    dtEnd = GetTickCount()
    result = (dtEnd - dtStart)/1000
    rptStr = rptStr & vbCrLf & "Second set of queries (cached connection): " & CStr(result) & " seconds."

    dtStart = GetTickCount()
    RunQueries(ss) 'Requery, using a cached DB connection
    dtEnd = GetTickCount()
    result = (dtEnd - dtStart)/1000
    rptStr = rptStr & vbCrLf & "Third set of queries (cached connection): " & CStr(result) & " seconds."

    'Show a performance report
    rpt.SelectionText = rptStr
    rpt.Visible = True

    ss.Close(False)

End Sub

Sub RunQueries(ByRef ss As Spreadsheet)

    'A connection string to the example datasets folder, using the Statistica OLE DB provider
    Dim CONN_STR As String
    CONN_STR = "Provider=STATISTICA.StaOLEDB.1;Data Source=""" & Path & _
        "\Examples\Datasets\;"";Integrated Security=TRUE;User ID="""";" & _
        "Extended Properties="""";Strip Formatting=TRUE;Auto calculate spreadsheet=FALSE;" & _
        "Connection String="""";Excel Style Julian Dates=TRUE;"

    'Query three large datasets from the example folder.
    'Note that will we be placing all of these queries into a single spreadsheet.
    Dim sql(1 To 3) As String
    sql(1) = "SELECT * FROM CreditScoring3"
    sql(2) = "SELECT * FROM Census"
    sql(3) = "SELECT * FROM Marketing"

    Dim q As Query
    Dim i As Long

    If ss Is Nothing Then
        'first time, create the spreadsheet and define the queries
        Set ss = Spreadsheets.New
        Dim destVar As Long
        destVar = 1
        For i = LBound(sql) To UBound(sql)

            If destVar > ss.NumberOfVariables Then
                ss.AddVariables("x", ss.NumberOfVariables, 1)
            End If

            Set q = ss.Queries.Add("Q" & CStr(i), CONN_STR, 1, destVar, sql(i))
            q.FormatVariable = True
             q.ColumnDecreasedAction = scQuery_Delete
             q.ColumnIncreasedAction = scQuery_OverwriteAndInsert
             q.RowDecreasedAction = scQuery_Delete
             q.RowIncreasedAction = scQuery_OverwriteAndInsert
             q.MaxRow = 2147483647
            q.OleColumnMaxSize = -9999 'trick to turn on option to cache the connection to the DB

            q.Refresh(False)

            destVar = ss.NumberOfVariables + 1
        Next i
    'Otherwise, we are requerying from an existing spreadsheet, where the DB connection is cached
    'and the column formatting is already established.
    Else
        For i = 1 To ss.Queries.Count

            Set q = ss.Queries(i)

            q.CommandText = sql(i) 'set the query

            q.OleColumnMaxSize = -9999 'trick to turn on option to cache the connection to the DB

            'first time, format the variables by setting names and types and format strings and such.
            'thereafter, it is assumed the number and types of variables does not change and no need to do it again
            q.FormatVariable = False

            q.Refresh(False)
        Next
    End If
End Sub