sql - run - ¿El acceso aumenta la velocidad de DLookup al "caché" de tablas u otra estrategia?

run sql access vba

Question

Fondo

Tengo una forma separada de Access con múltiples DLookups . Hay aproximadamente 10 DLookups en el formulario y hay aproximadamente 25-50 registros visualizados en cualquier momento en la vista de Splitform.

La interfaz de acceso está vinculada a tablas SQL.

Cuando los valores DLookup se muestran en la vista Hoja de datos, se vuelve bastante lento para ver la información, porque hay frecuentes recálculos (cada vez que algo en el conjunto de datos cambia, aparece Acceso para recalcular todos los objetos DLook para toda la hoja de datos de Splitform). Esto fue muy notable e inaceptablemente lento cuando se conecta a través de VPN.

Investigación

Decidí investigar y escribí lo siguiente para determinar por qué las cosas eran tan lentas. También quería comprobar si DLookup era más lento que una consulta SQL por algún motivo.

sub testLotsofDlookups()

    Dim count As Integer
    Dim startTime As Date
    Dim endTime As Date
    Dim numbTries As Integer
    Dim t As String

    numbTries = 100
    startTime = Now
    count = 0

    Dim dbs As DAO.database
    Dim rsSQL As DAO.Recordset
    Dim strSQL As String

    Set dbs = CurrentDb

    ''Open a snapshot-type Recordset based on an SQL statement
    strSQL = "Select FullName from ToolDesigners Where ToolDesignersID=4;"

    startTime = Now
    For count = 1 To numbTries
        Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        t = rsSQL.Fields(0)
    Next count
    Dim mDiff As Double
    mDiff = DateDiff("s", startTime, Now)
    Debug.Print "SQL Total time:" & vbTab & DateDiff("s", startTime, Now)
    Debug.Print "SQL Average time:" & vbTab & mDiff / numbTries

    ''
    ''
    ''
    ''
    ''



    startTime = Now

    For count = 1 To numbTries
        t = DLookup("FullName", "ToolDesigners", "ToolDesignersID=4")
    Next count


    mDiff = DateDiff("s", startTime, Now)
    Debug.Print "DLookupUp Total time:" & vbTab & DateDiff("s", startTime, Now)
    Debug.Print "DLookupUp Average time:" & vbTab & mDiff / numbTries

end sub

(Entiendo que esto solo es preciso en segundos)

Curiosamente, encontré que, en promedio, cada consulta de DLookup y SQL tardaba casi 0,5 segundos. Mientras trabajo en la intranet de la compañía, todavía tengo tiempos de más de 0,10 segundos en promedio. Ambos son muy comparables en velocidad.

Esto provoca una actualización de forma muy lenta y una actualización MUY lenta de la hoja de datos.

Luego probé con una base de datos SQLExpress alojada en mi máquina: los tiempos cayeron a 0.0005 segundos en promedio.

Pregunta

Parece que las búsquedas son lentas en esta aplicación. Espero encontrar un enfoque alternativo y más rápido.

Lo que me gustaría poder hacer es hacer que el DBookup se ejecute en contra de las tablas locales. El acceso presuntamente lo mantiene en lugar de las tablas SQL en el servidor. Parece que podría crear tablas temporales cada vez que abro un formulario o la base de datos (no un fan). ¿Hay alguna forma mejor?

Parece que si me estuviera refiriendo a otra base de datos de Access, podría simplemente usar "opendatabase", que luego lo mantiene en la memoria. Esto luego aumenta la velocidad de las consultas en esa base de datos. El 100% de los ejemplos que encuentro se refieren a las bases de datos de Access, no a SQL.

Alternativamente, podría usar algo que no sea DBookup, que es lo que pensé cuando probé los comandos SQL, pero no estoy muy seguro de qué hacer porque SQL era una velocidad comparable.

Answer 1

Si solo se trata de valores únicos, me inclino a utilizar un simple caché en memoria:

Private mToolDesignerFullNameCache As New Scripting.Dictionary

Function GetToolDesignerFullName(Criteria As String)
  If mToolDesignerFullNameCache.Exists(Criteria) Then
    GetToolDesignerFullName = mToolDesignerFullNameCache(Criteria)
  Else
    Dim Name
    Name = DLookup("FullName", "ToolDesigners", Criteria)
    mToolDesignerFullNameCache.Add(Criteria, Name)
    GetToolDesignerFullName = Name
  End If
End Function

Sub ResetToolDesignerFullNameCache()
  mToolDesignerFullNameCache.RemoveAll
End Sub

Requiere agregar ''Microsoft Scripting Runtime'' como referencia de VBA para compilar. En el pasado, he encontrado que este tipo de cosas son útiles incluso cuando uso un servidor de acceso, dada la frecuencia con la que la IU de acceso buscará datos.