[RESOLVED] Fastest Access

Pardon the pun but I would like advice on how get at data in an Access database in the fasted possible manner. The database has one table and that table consists of a primary key string and a Description memo field. Each record in the table will be unique and I just need to be able to read a record, possibly update it or at other times add a new record.

How large is the table? If table structure is that simple then typical SQL with WHERE clause is already fast... other optimization techniques would be around idea of minimizing network round trips such as by maintaining copy of data at client or mid-tier.

The table could be 2 or 3 thousand records long.

I thought about reading all the data up front into a collection or dictionary in my program but if there were a very fast ADO "Get/Put" I think I'd prefer that.

Which occurs most often? SELECT, INSERT or UPDATE? You can improve select by maintaining local db (or other variations such as binary file if applicable) but other two will suffer.

Select, by far.

In this case, with single table on local and single user, I think use DAO is faster and simpler:

Table1 has 2 fields:
ID : AutoNumber
Description: Memo

Code:

---

'-- Reference: Microsoft DAO x.x Object Library
Option Explicit

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Sub Form_Load()
  Set dbs = DAO.OpenDatabase("C:\MyFolder\db01.mdb")
  Set rst = dbs.OpenRecordset("Table1", dbOpenDynaset)
End Sub

Sub Form_Unload()
    rst.Close: Set rst = Nothing
    dbs.Close: Set dbs = Nothing
End Sub

---

Code:

---

'-- SELECT ------------------------------------------
Sub GetDescription(ID As Long, Description As String)
    rst.FindFirst "ID=" & ID
    If Not rst.NoMatch Then
        Description = "" & rst!Description
    Else
        MsgBox "ID Not Found: " & ID
    End If
End Sub

---

Code:

---

'-- UPDATE ------------------------------------------
Sub UpdateDescription(ID As Long, Description As String)
    rst.FindFirst "ID=" & ID
    If Not rst.NoMatch Then
        rst.Edit
        If Description = "" Then
            rst!Description = Null
        Else
            rst!Description = Description
        End If
        rst.Update
    Else
        MsgBox "ID Not Found: " & ID
    End If
End Sub

---

Code:

---

'-- INSERT ------------------------------------------
Function AddNewDescription(Description As String) As Long
    '-- return ID of new added record
    rst.AddNew
    rst!Description = Description
    AddNewDescription = rst!ID
    rst.Update
End Function

---

If you're going to use DAO, Seek is a bajillion times faster. Assuming you have an index on the ID field, make the following changes to anhn's code:

Code:

---

Set rst = dbs.OpenRecordset("Table1", dbOpenTable)

---

Code:

---

Sub GetDescription(ID As Long, Description As String)
    rst.Index = "Name of the index goes here"
    rst.Seek "=", ID
    If Not rst.NoMatch Then
        Description = "" & rst!Description
    Else
        MsgBox "ID Not Found: " & ID
    End If
End Sub

---

etc...

The DAO Seek method is the greatest thing since sliced bread. It is faster than anything ADO ever dreamed of being on an Access database. It also supports directional searching, as well as ">=" style searching. It even handles multiple-field indexes with ease; simply tack on each field in the multi-field index as parameters:

rst.Seek "=", Key1, Key2, Key3, etc...

For those familiar with FoxPro, the DAO Seek method is equivalent to FoxPro's Seek command. Seek is the shiznit.

EDIT: anhn, is there a particular reason you fill Description by reference instead of setting it as a return value?

Quote:

---

Originally Posted by MartinLiss

The table could be 2 or 3 thousand records long.

I thought about reading all the data up front into a collection or dictionary in my program but if there were a very fast ADO "Get/Put" I think I'd prefer that.

---

If you keep the table open as a table-type recordset and use the DAO Seek method, the data access will be approximately as fast as doing a binary search on a sorted array. Very fast. The great thing about it is that because it's a database, you don't even have to worry about wasting memory if the table is very large.

Quote:

---

Originally Posted by Ellis Dee

anhn, is there a particular reason you fill Description by reference instead of setting it as a return value?

---

Thanks for update on DAO Seek.
At first that was a function but later I changed it to a Sub to make it similar as Sub UpdateDescription(), there is no other reason.

You can bypass the relational engine in Jet and go to a table in ISAM mode using ADO as well.

The table must have a key (at least a PK) and it must be a table (i.e. not a query, view, stored procedure, querydef, etc.). You must open this table with adCmdTableDirect and you can use the ADO Recordset's Seek method or Find method. As noted for DAO, using Seek is quicker because it works through the ISAM index table to locate the desired record.


However you said fast without qualifying whether the database is local or remote. The very fastest way to get access to remote Jet databases is via RDS, which requires the use of ADO instead of DAO. TableDirect and Seek are still available.

RDS runs Jet on the machine where the database lives. The result is a type of client-server database similar to using SQL Server in "disconnected" mode with client-side Recordsets. Since Jet runs on the database server, there is no need to pull pages of index and data across the network... only result and update rowsets move over the wire. Coupling RDS with COM+ object pooling increases the scalability to thousands of users.

To use RDS you need a machine to act as a database server, not just a file server. However you need no special software besides what comes with Windows (including IIS if you want to use HTTP as your transport).

You have the option of coupling clients to the server using either DCOM or HTTP/HTTPS (or both if you support both LAN and WAN clients). RDS is the precursor of what became Web Services and while officially deprecated has had continual improvements through MDAC 2.8 and is still supported in Vista and Windows Server 2008.

Thanks guys I have what I need.