Data Retrieval Speed

[Guest]

Does anyone know why a first record query takes so long and subsequent
queries are seemingly immediate?

I split a database and stored a 15,000-record table on one PC and copies of
the forms on about six other PCs on the network. Whereas the first query
(after the form is opened) can take up to 45 seconds to pull relevant records
across the network, subsequent queries are immediate, regardless of search
criteria or number of records found.

Here's how I'm querying the table:

dim MyDatabase as database
dim MyRecordset as recordset
dim strSQL as string
dim strCriterion as string

set db = currentdb
strCriterion = 12345
strSQL = "Select * From MyTable Where PartID = " & strCriterion
set rs = db.opendrecordset(strSQL)

 

[Guest]

Glen,

Is your [PartID] field indexed?

Dale

 

[Guest]

Dale,

That's a good point. Usually I use indexes for my searches. However, in this
case I'm using "like" in my SQL statement -- I'm searching for all matches
within the character string of the field, such as

strSQL = "Select * from MyFile Where PartID Like " & strCriterion

Because of this, I assumed that indexing wouldn't be helpful. Also, after
the first query all of the following searches execute very quickly,
regardless of the records involved, as long as I keep the form open.

I'm confused as to what Access is doing the first time that takes so long.

Glen

Glen,

Is your [PartID] field indexed?

Dale

Does anyone know why a first record query takes so long and subsequent
queries are seemingly immediate?

I split a database and stored a 15,000-record table on one PC and copies of
the forms on about six other PCs on the network. Whereas the first query
(after the form is opened) can take up to 45 seconds to pull relevant records
across the network, subsequent queries are immediate, regardless of search
criteria or number of records found.

Here's how I'm querying the table:

dim MyDatabase as database
dim MyRecordset as recordset
dim strSQL as string
dim strCriterion as string

set db = currentdb
strCriterion = 12345
strSQL = "Select * From MyTable Where PartID = " & strCriterion
set rs = db.opendrecordset(strSQL)