Optimizing DLookup - Access 2.0 
Author Message
 Optimizing DLookup - Access 2.0

I'm running Access 2.0.

My application needs to do multiple DLookup calls on a large external
Btrieve database in order to get gata from multiple records to combine
onto a report.

The database is large and the DLookups take a while to locate the
records. In my Access Basic code, I can identify a subset of the large
database that all the DLookup functions will use to get their data
from. I thought that by doing a select query against the large
database to extract a smaller subset of records and then using that
query in the DLookup function instead of the table might speed things
up.

It appears that the query runs each time a DLookup is executed, which
is about 50 times for each report. Even though the query is returning
the same records each time, the DLookup has to reexecute it. This
actually increased the running time from 90 seconds to 120 seconds (on
a 586-200 Mhz processor.

My second thought was to change the select query into a make table
query. Then use that Access table, which is only about 12 records of a
100,000 record Btrieve table, to do the DLookup against. When I did
this, it worked a whole lot faster - <5 seconds.

My problem is that this is a multi-user application. I can't have each
simultaneous user running a make table query in the same database
without having some kind of conflict - can I?

Are there any suggestions on how to accomplish the same improvements I
saw with the extracted data in a separate table and not have the multi
user conflicts?

Thanks for any ideas.
------------------
Denny Pasternak
FDC - Teleservices
Omaha, NE



Sat, 22 Jul 2000 03:00:00 GMT  
 Optimizing DLookup - Access 2.0

I do the same thing for one of my apps.
The performance improvement with temporary tables is quite dramatic.
I have two suggestions:
#1 - If the data you are looking for is relatively "close together" and does
not change frequently (e.g. yesterday's clock-ins for a payroll system.)
then instead of downloading the 12 specific records that the current user
wants, download the whole day's worth of data once a day. This way any user
can query the temporary table and get the results instantly. You only
transfer data once.  You can even get a Scheduler like 16-Bit Skedezy to do
it for you automatically. (I highly recommend the 16 bit version for NT 4.0
as it works flawlessly with ODBC datasources etc.  All 32 bit schedulers
that I have tried bomb out when there are attached tables involved.)

#2 - Another idea is to limit the number of people who use the make table
feature to 1.
Trap their username and time, and display it on the Main Menu near the
button that opens the form that has the transfer feature on it. Set the
Ontimer event to display the trapped data every minute.  The close Event of
the data transfer form should re-set the trapped values to "No one" or
something like that.  Now all of your users will know who is using this
special feature.  This way they can run make table queries to their heart's
content without overwriting each other



Sat, 22 Jul 2000 03:00:00 GMT  
 Optimizing DLookup - Access 2.0

Joe:

Thanks for the reply. See my comments interspersed below.

Denny

On Tue, 3 Feb 1998 17:00:23 -0500, "Joe Fallon"

Quote:

>I do the same thing for one of my apps.
>The performance improvement with temporary tables is quite dramatic.
>I have two suggestions:
>#1 - If the data you are looking for is relatively "close together" and does
>not change frequently (e.g. yesterday's clock-ins for a payroll system.)
>then instead of downloading the 12 specific records that the current user
>wants, download the whole day's worth of data once a day. This way any user
>can query the temporary table and get the results instantly. You only
>transfer data once.  You can even get a Scheduler like 16-Bit Skedezy to do
>it for you automatically. (I highly recommend the 16 bit version for NT 4.0
>as it works flawlessly with ODBC datasources etc.  All 32 bit schedulers
>that I have tried bomb out when there are attached tables involved.)

Once written to the Btrieve table (updated monthly), the data doesn't
change. However, the specific group of records needed each time a user
has a request will change. I am thinking of scheduling an extraction
for just the records for the last 18 months (the table has 5 years of
data in it for use in other applications) and just the fields I need
and put them in an Access table.

Can you point me to the source of the scheduler you mention?

Quote:
>#2 - Another idea is to limit the number of people who use the make table
>feature to 1.
>Trap their username and time, and display it on the Main Menu near the
>button that opens the form that has the transfer feature on it. Set the
>Ontimer event to display the trapped data every minute.  The close Event of
>the data transfer form should re-set the trapped values to "No one" or
>something like that.  Now all of your users will know who is using this
>special feature.  This way they can run make table queries to their heart's
>content without overwriting each other

Interesting idea. I would also have to account for people who walk
away from their PC before exiting or who just shut off their PC
without exiting, etc. to make sure the lock doesn't stay active.

Thanks for the feedback.
------------------
Denny Pasternak
FDC - Teleservices
Omaha, NE



Sun, 23 Jul 2000 03:00:00 GMT  
 Optimizing DLookup - Access 2.0

Version 1.62 of 16-Bit Skedezy can be downloaded at this web site.
Registration is about $30.  I tested the 32 bit version once and it bombed
in NT4.0 with ODBC datasources.
http://www.coredcs.com/~TOPline/

Quote:

>Interesting idea. I would also have to account for people who walk
>away from their PC before exiting or who just shut off their PC
>without exiting, etc. to make sure the lock doesn't stay active.

True, but remember, their name and time they signed in are posted for all
the world to see.  If someone else wants to get in and this guy has been in
for 3 hours, I thin k a little peer pressure may be in order!

Also, this strategy is unnecessary if you do an extraction for the past 18
months of data.  Then you won't need make table queries at all.



Sun, 23 Jul 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Accessing Access 2.0 database with VB3, ODBC 2.0 and Microsotf Jet Engine Compatibility Layer

2. Install Access 2.0 Cmpt Layer w/o Access 2.0 for VB 3.0

3. VB 4.0, Access 2.0, or VC++ 2.0?

4. Can't Install 2.0 compatibility layer without Access 2.0

5. MS Access 2.0/MS Jet 2.0 Locks

6. VB 3.0, Access 2.0 and Crystal Reports 2.0

7. VB 4.0, Access 2.0, or VC++ 2.0?

8. VB 2.0 and Access 1.0 / 2.0 Problem.

9. Dlookup problem / Access 97

10. DLookup, remotely accessing

11. The Dlookup function without using Access

12. optimize data access using ADO

 

 
Powered by phpBB® Forum Software