Rx_
Nothing In Moderation
- Local time
- Today, 13:34
- Joined
- Oct 22, 2009
- Messages
- 2,803
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.
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.
Code:
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 FunctionAttachments
-
dlookup vs recordset.png
