Inner Join SQL Query in VBA is TOO SLOW (1 Viewer)

LambtonWorm · Jun 24, 2012

Hi all.

Intro:
I'm a DB/Access novice, doing an internship at a firm with no real in-house IT expertise, so I'd be very grateful for any advice on optimising this query. Am I doing something wrong, or is is this the best possible performance?

Background:
I've made an Excel Application for running backtests, using an Access DB for data. I'm using DAO as I thought it would be faster.
I have a query which I'm calling from excel VBA. It calculates the sum of profit of all records on the fly. It works fine. But it seems slow at 6 milliseconds or more on the test machine. It is called hundreds of thousands of times during a backtest.

Is the inner JOIN expensive? I established a 1->many relationship within Access, for all the relevant tables, enforcing referential integrity, which I assumed would index behind the scenes and speed things up. Seems no faster with or without this.

Code:
Here is a legible example of the query SQL string :

Code:

SELECT Sum([myTbl.Contracts]*(1234-[myTbl.TradedPrice])*tblStatic!PointValue-Abs([myTbl.Contracts])*tblStatic!ClearingCosts)
AS SumOfProfit, tblStatic.CCY
FROM tblStatic INNER JOIN myTbl ON tblStatic.Asset = myTbl.Security 
GROUP BY tblStatic.CCY, myTbl.Confirmed, myTbl.Security
HAVING (((myTbl.Confirmed)=Yes) AND ((myTbl.Security)='myAsset'));

Here is the query itself:

Code:

Public Function DBQ_cGetAllProfit(sTBL As String, sAsset As String, siSpot As Single) As Currency
    Dim sSQL As String
    Dim rst As DAO.Recordset
   
    '-- Get records
    sSQL = "SELECT Sum([" & sTBL & ".Contracts]*(" & CStr(siSpot) & "-[" & sTBL & ".TradedPrice])*tblStatic!PointValue-Abs([" & sTBL & ".Contracts])*tblStatic!ClearingCosts) " & _
           "AS SumOfProfit, tblStatic.CCY " & _
           "FROM tblStatic INNER JOIN " & sTBL & " ON tblStatic.Asset = " & sTBL & ".Security " & _
           "GROUP BY tblStatic.CCY, " & sTBL & ".Confirmed, " & sTBL & ".Security " & _
           "HAVING (((" & sTBL & ".Confirmed)=Yes) AND ((" & sTBL & ".Security)='" & sAsset & "'));"
   
    Set rst = g_dbFAT.OpenRecordset(sSQL)
       
    If Not rst.RecordCount = 0 Then
        If Not IsNull(rst.Fields("SumOfProfit").Value) Then
            DBQ_cGetAllProfit = rst.Fields("SumOfProfit").Value
        End If
    End If
   
    '-- Tidy up
    rst.Close
    Set rst = Nothing
End Function

,where g_dbFAT is a global variable representing the database itself, set by WorkSpace.OpenDatabase

Summary:
Can this (and other) queries, called from Excel VBA using DAO OpenRecordset method, be sped up in any way?
Is 6 milliseconds reasonable, am I expecting too much?
Is there any further way of indexing?
Is DAO appropriate and is MS Access the limiting factor? If so what should I use?

Many Thanks

boblarson · Jun 25, 2012

Change your query so that there is a WHERE Clause instead of Having Clause so it can bring back only the records it needs and THEN Group them.

Code:

SELECT Sum([myTbl.Contracts]*(1234-[myTbl.TradedPrice])*tblStatic!PointValue-Abs([myTbl.Contracts])*tblStatic!ClearingCosts) AS SumOfProfit, tblStatic.CCY
FROM tblStatic INNER JOIN myTbl ON tblStatic.Asset = myTbl.Security
WHERE (((myTbl.Confirmed)=Yes) AND ((myTbl.Security)='myAsset'))
GROUP BY tblStatic.CCY;

spikepl · Jun 25, 2012

1. You are selecting , then grouping, then "having" - which throws part of the selected and grouped stuff away, as far as I can tell. You should replace the HAVING with WHERE prior to the GROUP BY. In teh query designer, removed the criterion, drag the field onto the grid one more time and use the criterion field of that new field.

2. You should put index on the fields which form part of WHERE or GROUP BY. (Index will probably not do anything at all on yes/no fields.) This may yield a tremendous increase in speed, if you do not yet have indexes on those fields.

3. Access performs faster with DAO than ADO, so stick with it.

boblarson · Jun 25, 2012

It also looks like you could use this:

Set rst = g_dbFAT.OpenRecordset(sSQL, dbOpenForwardOnly)

Pat Hartman · Jun 25, 2012

It also looks like you've created tables that simulate spreadsheets. In a relational database, you would not have multiple identical tables. You would have a single table with a column that identifies the source of the data. That would allow you to use static SQL and saved querydefs rather than dynamic SQL in VBA. When you are running a query thousands of times, you can't afford the overhead of creating an execution plan for each execution.

LambtonWorm · Jul 29, 2012

Thanks for all the useful input guys.
Didn't make all that much difference in this case but will be useful to know all these tips in future when dealing with bigger DBs.

Pat: yes absolutely, you're totally right and I agree. The reason I've done this in this case was it was a quick and easy way of testing something out without having to amend dozens of SQL queries. Of course in a proper application, I would seek to use just one table as you suggest.

Inner Join SQL Query in VBA is TOO SLOW (1 Viewer)

LambtonWorm

Registered User.

boblarson

Smeghead

spikepl

Eledittingent Beliped

boblarson

Smeghead

Pat Hartman

Super Moderator

LambtonWorm

Registered User.

Similar threads

Users who are viewing this thread