Slow Database

[Eddie Mason]

Hi All,
I left the following message on the 5 May, without success, can anyone suggest who I can contact to get an answer?

Regards

Eddie

Previous Message
"I have a major, major problem that I hope someone can provide an answer. I have an Access 2000 system where the tables are loaded onto a Novell 5.1 network with the front end loaded onto 5 computers each attaching to the tables.

As the records contained in the tables have grown so the system has become slower with it now taking minutes to load some of the forms, or get Combo boxes to drop down.

Whilst the system is loaded onto 5 computers, there are never more than two users accessing the system at the same time.

I have tried loading the tables onto one of the computers and attaching them locally to the front end, and once this is done, the forms load immediately, which makes me think that the problem has to be with the Novell network.

Has anyone else experienced this type of problem?

Hope someone can help I’m desperate."

 

[Fizzio]

Are your queries also on the front-end?

A 'cheat' is to pre-load your forms at startup so that they are just hidden, waiting to spring into action!
Make sure that your comboboxes do not take their rowsource from an SQL statement, but from a saved query.

Similarly for all your forms (unless they are supplied from tables.)

hth

Moving to a SQL backend will give you a performance boost but I know 'nowt' about this.

 

[Meltdown]

Check out this thread and the links in it.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47487

 

[pdx_man]

Moving to a SQL or Oracle backend will help tremendously, but you may want to start with checking your indexes. Do you have them on the fields you are sorting or querying by? Have you compacted your DB lately? Can you archive any records to another table?

 

[The_Doc_Man]

An issue that occurs to me is that if you have a startup form that has to scan a table, you could be asking for lots of trouble. We are in an Access/Novell environment too.

If you have a startup form (or ANY form) that has a domain aggregate function in it such as DCount, DSum, DMax, etc., your larger databases will make that take much longer.

Things to do to speed up those cases:

1. Repair and Compact your database regularly.

2. If you can do it, place indexes on combinations of fields used most often in any domain aggregates.

3. Look over the other suggestions made here.

4. Be aware that as Access databases get larger in a networked environment, they WILL chew up more of your bandwidth. It is a cold, hard fact of life with Access. This occurs because Novell is just a file handler. Access still has to run the query on each local machine, which often means feeding entire raw tables down the network pipe so your machine can do the query. Which is why you want to eventually reach a client/server environment if you can do so. Get the HOST to run the query in SQL for you, then return only the result set. LOTS cheaper that way.