Performace Enhancements For Networked .MDB

Have you tried scheduled epair and compacts? I had a problem similar to this. I took some unnecessary queries and converted them into recordsets (they perform faster on large tables), I also do regular repair and compacts on the backend.

I assume you have the users front ends in their hard drives, if not, I highly recommend it.

HTH
berg

kkirt1:

Be careful not to over-indx your tables.  This can slow performance to a crawl.

What are your users doing with the linked tables?  If they are using poorly-constructed queries to gather data from the linked tables, it isn't the fault of the tables themselves.

How is your network performance overall?

Wes

Here are some links to some helpful hints with regards to Access databases

http://ourworld.compuserve.com/homepages/attac-cg/AtblTip.htm#GEN
http://ourworld.compuserve.com/homepages/attac-cg/AQryTip.htm#UPDATE
http://ourworld.compuserve.com/homepages/attac-cg/ACodeTip.htm#MEM

bERG1375:  I do regular compacts and repairs and the users have their applications loaded locally.  
I don't see any queries that can be dumped into a dataset but that is a good idea.

wesleystewart:  The tables do have several indexes but nothing that I would consider excessive.  
The users are doing very basic queries, nothing real complex so they probably can't screw them up.  
The users link to a table that is batch updated from the main-frame every week.  

Tmess:  Good links, but I didn't see anyhting I haven't already tried.  Thanks anyway!

Also, I had to replace the links from UNC to straight drive links. That upped the time of one of my db's. I don't know if that applies to you, but it's a fix I had.

berg

Another tip: Access is not a true client server even if you have a front end with linked tables and a backend with tables on a server. When a request is sent out (sql statement or through linked tables) to the backend on the server, Access returns all the tables to the client and then processes the SQL. This is where access is slow.To make Access a better client server you need to create an "SQL middleman". This middle man would be an ActiveX exe (you can make one in VB)whih would reside on the server in the same folder as the mdb. What you would do is send your sql from the client to the middleman. It would then send the sql statement to the mdb on the server. Here's where the speed factor increases. The mdb wouldn't have to copy itself to the client. Instead it would copy itself on the server and send the result set to the axtiveX exe. Then the middle man would send the results back to the Client.

jkpcs:  When I wrote "applications" I meant that they have Access running on thier desktop machines. This is not a run-time Access issue.  The users have to have the flexibility to create their own queries and reports.

berg1375:  I don't understand!  I didn't know there were different ways to link tables.  Please explain.

Tmess: Yes, unfortunately that is the limitation of Access.  I would like to explore this ActiveX "middleware" you are talking about.  Could it be invisible to the users?  That is, could they link to a table just like they do today and run queries from those tables? Do you know where I could get an example of the ActiveX control?

Another thought I had, and I'm not sure it is applicable here, is changing the way access caches data.  Can designers instruct Access to bring these tables over from the network and keep them cached?  This would make multiple queries against the same tables much faster.

What about Access 2000 with its dual database engines.  Would that be a solution for this problem?

kkirt1:

I think there has to be a tweak somewhere that we're missing.  I have several databases over 40 MB and a few into the 250-300 MB range that work fine as back ends.  A few more things you might want to check:

Are your users using the LIKE operator on text or memo fields?

Is anyone using the IIf() function in a query?  That will kill performance.

Are the fields most often used as criteria indexed?

1. The activeX exe you could create with VB. However, I didn't know users could create queries and manipulate the objects. It would be very difficult to apply an AxtiveX exe because there would be too many variables involved.

2. Caches recrods - Actually Kkirt, a designer could do that however it would be very difficult. I know this can be done in with ADO. However, user won't be able to see added, deleted, or updated records. But again, the biggest problem is that the user is allowed to create tables and the like.

What you could actually do is that when the user open their database, you could copy the entire db from the network and put it on the client. Your frontened could then link to those tables from the mdb that was created on the client. This would create substantial overhead when the user open the mdb. This would allow user to create queries and reports. The only problem with this is that if the user updates, Adds or delete records. How would add, update, delete these records to the netwrok mdb when the user closes out of the mdb. Every record would have to be compared to the one of the records on the network to determine what was changed. It would be very difficult but it could be done.

There seems to be a lot of possiblites

If these are static data dumps from the mainframe.  Write a batch file to 're-fresh' the data locally to the hard drive ( 40 mb is not much these days ), each time the user uses the app.  You then, cut out the network data requests completely.

If these are static data dumps from the mainframe.  Write a batch file to 're-fresh' the data locally to the hard drive ( 40 mb is not much these days ), each time the user uses the app.  You then, cut out the network data requests completely.

I just spent a bunch of time writing this comment only to have my system crash.  So I won't write so much this time.

The key is how you handle your queries.  Take advantage of the optimization built into the Jet engine by making all your queries stored.  Unless I have to change parameters on the fly in code, I never use SQL statements either in modules or as properties for forms, controls, etc.

Teach you users the proper way to use the query builder to make queries.  After they have the query running the way they want, save the query and immediately run it.  As a matter of fact before you deploy an application, go through every query, open in design mode, save, then run.  This will force Jet to reoptimize (compile) the query.

As your data gets larger, you need recompile all the queries so that they can remain optimized.  For the local Access program, since the users are making their own queries and reports, how are you handling keeping those separate from the non-date portions of your application?  If their queries and reports are integrated in with the forms and code, then you need to write a routine which will go through the querydefs, open each in design mode, save, and run to force recompiling.

After this the local database must be repaired and compacted.  Are you doing this to the local databases as well as the server data databases?  This is also an overlooked area of slow down.  The local databases can get quite blooted and take up precious resources.

Jim

When I say UNC names I mean something like this:

\\JCKFRH01\Data\Shared\Apps\File1\Sample.mdb

This would go through the network neighborhood each time.

I changed it to x:\Apps\File1\Sample.mdb

After mapping their drives to what I wanted. Like I said it may not be an issue for you, but it is something to be aware of.

berg

wesleystewart:  I agree.  Our network isn't the most efficient but it seems to run pretty good.  Define "work fine."  On a simple select query I run from my system that is linked to tables on the network it can take 20 - 30 seconds to run.  Is that "work fine?"

As far as putting the .mdb on the clients, we tried that before and it was dragging the network down and users were forgetting to update it so at this point I don't think that is an option.

Jim:  Sorry about the system crash - I hate when that happens.  I am a little confused about optimization.  Doesn't access optimize the SQL statement everytime it is ran?  What are "stored" queries?  None of the sql statements I am using reside in VBA code.  The users that access these tables only use the most basic sql statments.  Also, I repair and compact the .mdb every week after the download.

berg1375:  All of the users have the network drive where the .mdb is located permanently mapped.


 

How many records are you returning, and how many records are in the table you're querying?

There are about 22,000 records each in two tables.  The returned records vary but almost never are they more than a few hundred.  

What surprises me was that I just went to the .mdb on the network, clicked on the tables and I got a record count within a second or two.  Why is it taking so much longer to query those records.  I'm not that knowledgeable about networks but it would seem that that few seconds was the table coming across the network.  After that transfer, It should run pretty quickly because resident databases I run just smoke!  Am I thinking about this right?

Just a comment here. We had a similar problem, with an app that used a java frontend on an access db. It ran good elsewhere, but in our facility it was slow as dirt. It turns out one of the routers was pointed wrong. They fixed that router, and now it flies.

Just posting an experience, probably doesn't apply to you :)

What is a stored query?  This is faster to answer.  A stored query is a query which is stored in the database file folder under Queries.  You don't have to use VBA code to have what I call and unstored query.  For example, you add the row source for a combo box.  If you click the ellipse for the row source property, you are put into the query builder.  After you have built your query (SQL statement) to your satisfaction and close the builder, Access asks if you want to save the changes and update the property.  Choosing Yes does not create a stored query under the Queries tab of the database.  According to some, this is a good as a stored query but it is very difficult to go through all of your forms and controls searching for an imbedded SQL statement vs looking under the Queries tab.

Supposedly, Jet works the same way for either query but until you actually run the query for the first time, it is not optimized.

What is optimization?  Do you want the short version or the long version? :-)

Jet looks at all the parameters for the query and tries to use a technology called "Rushmore" to preset up the query parameters (internal to Access and Jet).  Without going into a detailed explanation about Rushmore, it is a data-access technology that can help improve processing queries.  However, a query must be constructed in a certain way for the query to benefit from Rushmore.

In the process of optimization, Jet looks at the sizes of the tables involved in the joins and the number of fields that are extracted.  (For complete and highly technical explanation see "Jet Database Engine Programmer's Guide" from Microsoft Press.)  It then arranges internally which table is going to pull first and how the comparison will work against other tables.  As an example, if you have three tables: one with 100,000 records, two with 1000 records and the third with 50 records, it doesn't make much sense to take the 100,000 records and compare each against the product of the 1000 record and the 50 record table.  If the 1000 and 50 record tables are evaluated first, there may be a small number of matches or no matches.  The product of those tables are then compared against the 100,000 record table.  However, the query as written, if taken at face value, might ask for the 100,000 table to be the table to match the other.  Jet uses enough intelligence to do it the other way.

Lets say that the 1000 and 50 record table are system tables and will not grow much.  The 100,000 record table are transactions.  When the application is first run, the transaction table will have no or few records and be the logical one to start with.  So intitially that query is optimized that way.  But as the transactions grow, this is not the best way.  So you have to force Jet to recompile the queries so it can take into consideration the increased records in the transaction table.

Imbedded or non-stored queries, especially those in code, will be seen by Jet as a uncompiled query and everytime that they run, Jet will attempt to go through the optimization process.  This takes time and resources.  Considering how ofter queries are used, this could slow an application down to a factor of 2000 or 3000 times slower.

Let me give you a brief primer on how to improve queries performance:

Index fields on both sides of a join.

Add only the fields you need in the query results.

Add as many indexes as possible, which will slow down inserting, updating, and deleting records but sometimes the tradeoff is worth it.  (I know.  Too many indexes can really slow down the system.  You have to experiment here.)

Always index on fields used in the criteria of the query.

Compact the database often.  During compacting, Access tries to reorganize the table records to they are in contiguous spaces, ordered by the primary key.

When running a multitable query, test to see whether the query runs faster with the criteria placed on the "one" side or the "many" side of the join.

Avoid adding criteria to calculated or nonindexed fields.

Select the smallest field types for the primary key.  Don't use the company name instead of a long integer CustID.

Avoid calculated fields in nested queries.  If possible add the calculations to the highest level query.

If expressions can be handled in form or report controls, do them there rather than in the query.

When using LIKE in a query criteria, placing the asterisk at the end of the string so the index can be used.

Use Count(*) rather than Count([FieldName]).

User Group By as little as possible.  If possible, use First instead.

Use Rushmore whenever possible.  The following help in setting a query that can use Rushmore:

   Queries with a NOT operator can't be optimized.

   The Count(*) function is highly optimized by Rushmore.

   Descending indexes can not be used by Rushmore unless the expression is =.

   Queries on ODBC data sources can't use Rushmore.

   Multifield indexes can be used by Rushmore only when the criteria is in the order of the index.  For example if an index is LastName - FirstName, an expression based on FirstName only can't use Rushmore.

There are a lot more things that you can do and it takes a lot of research, reading, and playing with various combinations to just optimize queries.  I really feel that most of your problems are associated with queries.

Also, using Access with no code modules will work and is supposed to run faster, however there are some things that can be done in code which will almost always make the application run faster.  Code is sort of like fine tuning.

Jim

Hi kkirt1,

Any update on this question?

in an effort to clean up old open questions

your options are:

1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the
participants why you wish to do this, and allow for Expert response.  This choice will include a refund
to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information
outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with
details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
 --> Post comments for expert of your intention to delete and why
 --> You cannot delete a question with comments, special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below, include the question

QID/link. http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process:
Click you Member Profile to view your question history and keep them all current with updates as the
collaboration effort continues. http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

------------>  EXPERTS:    Please leave any comments regarding this question here on closing recommendations
if this item remains inactive another seven (7) days.

:O)Bruintje

All were good answers but... I have to pick one!  Thanks to all who responded!