The Old "is Recordset faster than DLookup for SQL Linked Tables" question

[Rx_]

It has been discussed before. Just thought it was worth asking specifically for Linked Tables (to SQL Server).
Would a stored procedure or Pass-through to return a single record provide any improvements?

In my case, the Rule Engine takes a single ID_Wells (primary key) and returns many discrete values as True/False. The Historical column is in the Wells table (so it is 1 to 1 for the ID_Wells primary key in the same table).

The Linked Table (ODBC using DSN-Less with SQL Server Native Client 11.0) on the split database are called with the VBA shown below.

Multiple Records Observation:
Just for fun, put each function shown below into a query and called 20,000 rows.
They both completed very fast using the ID_Wells as the sort. Too fast to measure visually.
To throw a delay in the sorting (see attachment):
Then, sorted the Query Field with this function from greatest to lowest value. Each of these two functions shown below in a query using the sort on a non-indexed function took 1 minute 10 seconds. No difference.

Conclusion: the DLookup vs Recordset doesn't make much difference. The ODBC driver will translate both into TSQL across the request so a pass-through query probably wouldn't make any difference.

But, I am open to suggestions.

Public Function Is_Historic(ID_Wells As Integer) As Integer ' see if faster than below
' Enter ID_Well, the Status number is identified, the Rule for this status is returned
        Dim ReturnValue As Integer
        Dim MyTablename As String
        Dim MyFieldNameWellStatus As String
        Dim OverRideRuleCheck As Boolean ' dlookup detrmines if check bos is checked or not
        Dim LocalResult As String
10    On Error GoTo errTrap
11      Is_Historic = False ' default
60                MyTablename = "Wells"                 ' name of table to lookup the value for match
70                MyFieldNameWellStatus = "Historical"              ' name of field in table
80                LocalResult = DLookup(MyFieldNameWellStatus, MyTablename, "[ID_Wells] = " & ID_Wells)
                  If Trim(LocalResult) <> "Current" Then Is_Historic = True
100   Exit Function
errTrap:
110       Debug.Print "Error at R_Phase1EvaluationStatus" & ID_Wells
120       Is_Historic = 0
End Function
Public Function Is_HistoricOldCode(ID_Well) As Boolean ' Replaced with D lookup version on 6/9/2014 - Doesn't seem to make that much difference on 8,000 well sort in query.
      ' Historic - if CURRENT is in field - then it is not Historic
      ' Rule Well Status - NavHeader
      Dim rstMisc                         As DAO.Recordset
      Dim rstExclude                      As DAO.Recordset ' excluded states
      Dim SQLMisc         As String  ' NOTE Added IP Date afterwards
      Dim SQLExclude      As String  ' use to exclude states
10      Is_HistoricOldCode = False  ' false until proven true
90      SQLMisc = "SELECT Wells.ID_Wells, Wells.Historical, Wells.Historical_Date, Wells.UserName FROM Wells WHERE (((Wells.ID_Wells)=" & ID_Well & ") AND (Not (Wells.Historical)='Current'));"
100   Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenDynaset, dbSeeChanges)
110           On Error Resume Next
120           rstMisc.MoveLast    ' should only be one in this construct, just in case there are two
130             If rstMisc.RecordCount > 0 Then
140                 Is_HistoricOldCode = True
150             Else
160                 Is_HistoricOldCode = False
170             End If
180             If Err.Number <> 0 Then
190                 Err.Clear
200                 Exit Function
210             End If
End Function

 

[pbaldy]

Re: The Old "is Recordset faster than DLookup for SQL Linked Tables" question

I use SS as the back end most of the time, and I wouldn't disagree with you. If I need a single value I'm likely to use a DLookup(). If I need more than one, I start leaning towards a recordset.