Which is faster?

[P.S.W.]

Which of these 2 methods is faster/more efficient for displaying a specific record from a recordset using date criteria?

(Assuming Adodc1 is pointing to "Table1")

Adodc1.RecordSource = "SELECT * FROM Table1 WHERE Date = #10/31/00#;

OR

Adodc1.Recordset.Find ("Date = #10/31/00#"), 0, adSearchForward, 1

?

[parksie]

I thought that Find simply created its own SQL statement...

Either way, I would expect SQL to be faster, because it's specific to the server, so you can specify any extra features as you need them.

[Coder Guy]

The fastest way to execute a query for say an Access database is going to be to use an Index (such as a primary key)

Use indexes whenever possible.

Don't know what method does this in ADO but it's
the Seek method in DAO...

[jmcswain]

I actually experimented with this to find out for an optimization I needed to do a couple weeks ago.

The fastest of the fast is the Seek method. I never learned it in DAO, but in ADO it's:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim varSeek(1) As Variant

Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cnn.ConnectionString = "Data Source=C:\MyDatabase.mdb"
cnn.Open

rst.CursorLocation = adUseServer
rst.Open "MyTable", cnn, adOpenStatic, adLockReadOnly, adCmdTableDirect
rst.Index = "NameOfIndexInAccess" ' You can see the actual index name by viewing Indexes in Design View of Table

varSeek(1) = "Search Text"
rst.Seek varSeek(1), adSeekFirstEQ


The next fastest is to select the whole table in a SQL statement, ORDERing by the field you are searching on, and calling a manual binary search using the following function:

Private Function FindValue(ByRef prst As Recordset, ByVal pstrField As String, ByVal pvarFind As Variant)
Dim lngFirst As Long
Dim lngLast As Long
Dim lngPos As Long

prst.MoveFirst
lngFirst = 1
lngLast = prst.RecordCount
Do
lngPos = (lngFirst + lngLast) / 2
prst.Move lngPos - lngFirst, adBookmarkCurrent
If pvarFind = prst(pstrField) Then Exit Function
If pvarFind < prst(pstrField) Then
prst.Move lngFirst - lngPos
lngLast = lngPos - 1
Else
prst.Move 1, adBookmarkCurrent
lngFirst = lngPos + 1
End If
Loop Until lngFirst > lngLast
' Not found - move to EOF
prst.MoveLast
prst.MoveNext
End Function

The Find method is noticeably slower than the above manual binary search.

The slowest of the slow is to open a whole new recordset WHEREing the criteria you are searching for.

(Argh! Why is my indenting getting removed?!)

[paulw]

The speed of a recorsdset return is not a simple question - Seek is definitely quicker than Find in all but the most unusual circumstances, but it depends on the underlying Database Engine and the quality of the code that brokers the return calls.

The DBE in SQL Server can be blindingly quick at returning data but you can cripple it by using Cursors, wrong Indexing methods etc.

Returning records with SELECT [Field] WHERE need not be the slowest method, I have been in situations where that is as fast as Seek so the answer to the question is: All depends...

Hehehe

Paul.