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.