none
Access 2007 with VBA running very slowly

    Question

  • I am invoking a function from within Access 2007 to perform a lookup on a table.  If the row is found then the row data is returned, if the row is not found then the first unused row is updated in the table.  Without the column Function call the query runs in about 4 mins.  With the column function call it take over 3 hours.  The VBA module does not do very much and is straight forward:

    Option Compare Database
    Option Explicit

    Public Function fGetProfileName(FormNum, JobFunction, Otran, CsiNum As String) As String

        Dim dbsMfes_Reconfig_Automation As DAO.Database
        Dim rstGetProfileName As DAO.Recordset
        Dim rstAddProfileName As DAO.Recordset
        Dim strGetProfileNameSql As String
        Dim strAddProfileNameSql As String
               
        Set dbsMfes_Reconfig_Automation = CurrentDb
       
        strGetProfileNameSql = _
             "SELECT [Profile Name], Form, [Job Function], [CSI Number] " & _
               "FROM [Profile Names] " & _
              "WHERE [Profile Names].[Form] = '" & FormNum & "' " & _
                "AND [Profile Names].[Job function] = '" & JobFunction & "' " & _
                "AND [Profile Names].[CSI Number] = '" & CsiNum & "'"
                   
        Set rstGetProfileName = _
              dbsMfes_Reconfig_Automation.OpenRecordset(strGetProfileNameSql, dbOpenSnapshot)
       
        If rstGetProfileName.EOF = False Then
            fGetProfileName = rstGetProfileName![Profile Name]
        Else
            strAddProfileNameSql = _
                 "SELECT Top 1 [Profile Name], Form, [Job Function], [CSI Number] " & _
                   "FROM [Profile Names] " & _
                  "WHERE [Profile Names].[Form] Is Null"
           
            Set rstAddProfileName = _
                  dbsMfes_Reconfig_Automation.OpenRecordset(strAddProfileNameSql)
                 
            If rstAddProfileName.EOF = True Then
                fGetProfileName = ""
                [TempVars]![ErrorCode] = 1
            Else
                rstAddProfileName.Edit
                rstAddProfileName![Form] = FormNum
                rstAddProfileName![Job Function] = JobFunction
                rstAddProfileName![CSI Number] = CsiNum
                rstAddProfileName.Update
                fGetProfileName = rstAddProfileName![Profile Name]
            End If
            rstAddProfileName.Close
        End If
       
        rstGetProfileName.Close
        dbsMfes_Reconfig_Automation.Close
       
        Set rstGetProfileName = Nothing
        Set rstAddProfileName = Nothing
       
    End Function

    The column function call:

    Profile Name: fGetProfileName([Otrans for eLabel Temp 2].[eLabel FormNum],[Otrans for eLabel Temp 2].[eLabel Job Function],[Otrans for eLabel Temp 2].[Otran],(Val(IIf(IsNull([Otran CSI by Otran].[csi_id]),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[csi Number]),IIf([Otran CSI by Otran].[csi_id] In ('000000','999999'),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[CSI NUMBER]),[Otran CSI by Otran].[csi_id])))))

    The table has and index on the approriate column.


    Tuesday, July 09, 2013

    Table: Profile Names                                                                                 Page: 1

     

    Properties

    DateCreated:              5/7/2013 1:51:33 PM         DefaultView:             2
    DisplayViewsOnShare 1                                         FilterOnLoad:           False
    GUID:                                                                     HideNewField:          False
    LastUpdated:              7/9/2013 1:45:24 PM         NameMap:                Long binary data
    OrderByOn:                 False                                  OrderByOnLoad:       True
    Orientation:                 Left-to-Right                      RecordCount:            456976

    TotalsRow:                   False                                 Updatable:                True

     

    Columns

             Name                                                  Type                        Size

             ID                                                    Long Integer                 4
             Form                                                  Text                         4
             Job function                                          Text                         3

             Profile Name Prefix                                   Text                         4
             Profile Name Seq                                      Text                         4
             Profile Name                                          Text                         8

             CSI Number                                            Text                         10
             Occurance                                             Integer                      2

     

    Table Indexes

             Name                                     Number of

             CSI Number                               1
                      Fields:
                      CSI Number                 Ascending

             Form                                     1
                      Fields:
                      Form                       Ascending

             ID                                       1
                      Fields:

                      ID                         Ascending
             PrimaryKey                               1

                      Fields:
                      ID                         Ascending
             Profile Name                             1

                      Fields:
                      Profile Name               Ascending

    Does anyone have any ideas what could be casuing the query, calling the VBA function that uses DAO, to run so slowly?

    Tuesday, July 09, 2013 6:10 PM

Answers

  • Am I right in understanding that your table [Profile Names] has a set of previously created records with Null in the [Form] field, so if you don't find a match you grab an unused record and fill it in?

    I see two things that would likely be slowing this down a lot more than it needs to be.  The first (and simplest) one is that you don't appear to have an index on [Job Function].  Since you're applying a criterion on that field, I would expect an index on the field to speed things up (though in fact it may not matter, depending on the query plan the database engine comes up with).

    Second, and more important, is that you are calling CurrentDb for each call to the function.  CurrentDb is very slow.  Better is to get a reference to CurrentDB once and save it in a static object variable, and then use that static reference wherever you would use CurrentDB.  Michael Kaplan wrote a fine method for doing this, which involves a public property procedure:

    Private m_db As DAO.Database
    
    Public Property Get CurrentDbC() As DAO.Database
    
        If (m_db Is Nothing) Then
            Set m_db = CurrentDb
        End If
    
        Set CurrentDbC = m_db
    
    End Property

    Then you use CurrentDbC where you would normally use CurrentDb.  Note that you must not close the database returned by CurrentDbC, or subsequent uses will fail. (You shouldn't be closing CurrentDb, either, but Access reopens it if you do.)

    With the above code in a public module, your code would be amended to use CurrentDbC instead of CurrentDb, also removing the line "dbsMfes_Reconfig_Automation.Close".  That will speed things up a lot.

    However, you are still opening and closing recordsets on queries inside the function.  So if you are using this function as a calculated field in some query, it's not going to be fast.  If there is no criterion on that calculated field, then probably Access will defer evaluating the function until the field's value is queried for display purposes, and so it won't be too bad even if you have a lot of records -- unless you do something in code to evaluate the field for every record.  But if you have a criterion on the calculated column, then the function will have to be evaluated for every record, and that could be very slow.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, July 09, 2013 6:36 PM