DAO efficiency question

[Aaron Hartley]

Hello,

Assuming that I am only interested in one record within a table, which
is more efficient (Account Number is unique)?:

1.
Set D = CurrentDb
Set rsCust = D.OpenRecordset("SELECT [Group Number] FROM Customers
WHERE [Account Number] = '" & [Account Number] & "'", DB_OPEN_DYNASET)
2.
Set D = CurrentDb
Set rsCust = D.OpenRecordset("Customers", DB_OPEN_DYNASET)

Criteria = "[Account Number] = '" & [Account Number] & "'"
rsCust.FindFirst Criteria

Thank you.

[Peter Miller]

Aaron,

On 10 Dec 2003 08:21:55 -0800, ha***********@hotmail.com (Aaron
Hartley) wrote in comp.databases.ms-access:

Assuming that I am only interested in one record within a table, which
is more efficient (Account Number is unique)?:

1.
Set D = CurrentDb
Set rsCust = D.OpenRecordset("SELECT [Group Number] FROM Customers
WHERE [Account Number] = '" & [Account Number] & "'", DB_OPEN_DYNASET)
2.
Set D = CurrentDb
Set rsCust = D.OpenRecordset("Customers", DB_OPEN_DYNASET)

Criteria = "[Account Number] = '" & [Account Number] & "'"
rsCust.FindFirst Criteria

You haven't mentioned whether the tables are stored in the same file
as the one containing this code, or in a separate backend, or what
type of storage is used for the tables.

In general, (1) is the recommend approach because it is more flexible,
and requires fewer locks. There may well be simple scenarios where
(2) is faster, but (1) is better in most cases.

Briefly, this is because (1) allows for the returned recordset to only
contain the desired record, while (2) returns a recordset (editable,
in this case) of all records in the underlying table, resulting in a
greater number of locks. Also, if the backend were in some other
rdbms (like SQL Server) and the tables were linked to the Access
front-end, (1) allows for server side processing, while (2) does not,
resulting in a lot more network i/o for (2). You may not currently
use sql server on the backend, but (1) makes any such placement issues
irrelevant (in this particular sense) requiring no code change here
for improved performance.

In general, you always want to design any app so that the fewest
possible records are returned for the task at hand, and that minimal
locks are placed on those records sufficient for current needs. If
you need to edit one record, getting an editable recordset of a wide
range of records is a bad idea.
Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051