Linked tables performance question

[ctsang]

Hi guys,

I am managing a backend and clientend Access 97 system using linked tables. However, on the client end I have noticed that there are some serious performance problems on the forms when it comes to accessing the records. Is there anyway to improve on the performance of the forms when interacting with the linked tables?

Thanks in advance for the help!

Charles

 

[cmmrfrds]

Use pass-through queries to perform the processing on the server and avoid joining local tables to the remote linked tables.

 

[ctsang]

Sorry, I forgot to mention that the backend database is also an Access 97 database.

Is it possible to use pass-through queries for another access 97 database?

 

[cmmrfrds]

In the case of a linked access db, it is probably best to stay with the linked tables. Do you have indexes on the fields you are searching on. Do you have 100 megabit ehternet on the network. Do the PC's have sufficient ram and speed.

 

[ctsang]

Hi,

In regards to your questions...

1) All the tables I am reading from are indexed, yes.

2) I don't know the specifics about our network set-up, but all the computers are connected using a T-1 connection.

3) The default configuration for all computers in the company is to have a minimum of 733 Mhz and 512 Ram.

I did try to combine the forms with the tables instead of splitting the databases and the performance difference was significantly better. However I would like to stick with the split databases if possible.

Any other advice?

Thanks!

 

[cmmrfrds]

Can you quantify "performance problems"?

Are all queries a problem?
How many records do you have in the tables?

Can you show an example of a query that is a problem including the number of records in each table that are joined. Paste in the SQL.

 

[ctsang]

Sure.

Performance problems = Slow load times for intially loading the form and refreshing subforms using the OnCurrent Event. This results in about 5 seconds to process the initial form load (compared to the instant loading when there are no linked tables) and another 5 seconds each time the OnCurrent Event is run to refresh the subforms.

I don't have any complicated queries in the form. The main parent form simply selects from a single base table. This base table refreshes one of the child subforms each time a new record in the main table is selected.

There are 200 records in the main base table (say table a). There are 710 records in the child table. (table b) (1-many relationship between the two.)

However this child subform/table has two children as well. So after a selection is made to the base table, anotehr selection can be made to the child form where two other subforms are refreshed. These two other child subform tables contain 9 (Table c) and 767 (table d)records respectively.

So there isn't really a specific SQL statement I can show you, it's simply selecting from any of the records from table a where it filters to the related records from the table b. Selecting from a record in table b refreshes the other two subforms and displays values from table c and d respectively.

I hope my explanation is okay. Let me know if you need any other details.

 

[cmmrfrds]

Very good explanation. The amount of data in the tables is trival. What happens if you go through the wizard and build a new form from one of the tables. All the data should be displayed very fast. Is this the case? If not, then put the table local and try. Is this fast?

 

[JeremyNYC]

Try this:

http://www.fmsinc.com/tpapers/faster/index.html

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Coxylaad]

cmmrfrds,
Sorry to high jack this post but I have the same problem of slow performance with my access 2000 front end accessing an SQL server backend.
Were you going to suggest something?

Thanks

Ian

 

[cmmrfrds]

Can you give some details. First, are you linking the tables in an MDB or is this an Access Project (ADP)? Are you familiar with Query Analyzer? If so, have you run the same query in Query Analyzer. Please isolate to 1 query and give the respective volumes in all the tables joined.