Split Database Slow Performance Issues 1

[dataside]

I have a development database that is installed on a network drive. It functions correctly and works fine (from the end user's point of view).

It has recently been split into a front end and a back end.

It all works fine except that it now runs like a dog.

It runs so slow that I get friustrated while waiting for a response and the users who it is being developed for keep pointing this out during demonstrations.

The database has been compacted with no change. The network is not percieveably slow (it ran fast before the split).

Unsplitting is not really an option as the data will, at sometine in the future be ported to SQL so I need to get as much work done now.

We can't put the front end on a local desktop PC. We have to work is a very srtict and highly reglulated environment that the work involved is beyond the scope of the project as agreement would take about 6 months (at least).

Any suggestions to speed up the database?

Thanks

Peter

 

[Geee]

What technologies are you using in the front end? How big is the Access database and how are you connecting to it?

Gary -GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[Aivars]

Hi, Peter!

This situation is known for me, but I'm not sure that somebody can give you strict instruction what and how must you do.
1. In cases like this I check out my codes (procedures On Load, On Open, On Current with code debugging. Some times one and the same operation (function) is running many times one by one. At most it happen when procedure On Current is running.
2. Maybe your queries are too complicate?

Good luck!
Aivars

 

[dataside]

Thanks guys...

The front end is MS Access 97 and the back end is also MS Access 97. They are in the same network directory.

The database has a total (after comapct) of 1.9 MB. The main table has 2,500 records. There are other tables used for lookups but most contain just a handfull of data.

The main form runs over a table and not a query.

The only event procedures are "on delete", "after update" and "before delete confirmation". I have copied the form with none of these and still get the problems.

To be more specific, the form opens up slowly. Any combo boxes (most just lookup a handfull of records) take ages to populate and dropdown.

The whole form has a sluggish feel that is not there before the split.

Peter

 

[Guest_imported]

Peter,

I can't imagine what the problem would be. 2500 records is nothing. This should be flying, especially if the main form is based on a table and not a possibly slow running query. You might talk to your Network Administrator and see if file buffering is set too low and causing the overhead although I would be surprised.

I might try creating a new, test front end quickly. Just link to the main table and use the form wizard to create a quick form. How does this run - fast or slow?

I have seen combo boxes slow up a form load. Are the tables behind the combo boxes really large or have complex queries behind them? Create a copy of the form and remove the combo boxes - does this help?

Good luck,
Brooks

 

[GBall]

I might be missing something here, but you said that the front-end and back-end were in the same network directory ?
The idea of splitting a database is to put the front-end on local machine(s) and leave the back-end on a server.
By running the FE over the network which then fetches its data over the network, it seems to me that your traffic throughput is pretty considerable.
Try putting the FE on a local machine and connecting to the BE on the server - might help.

Graham

 

[dataside]

They are both on the server as they are in development. The server is backed up each night and is more secure than a PC.

It is also a multi use database and will be reqired to be used by different people at different sites using different PCs.

We need the flexibility for someone to access the data where ever they happen to be working that day rather than from just their own PC or a sepcific PC.

Copying down the database to the temporary PC is not an option as each PC is managed for regulatory purposes and such a database would need to be approved and installed through a script run by IT technicians. (We have the US Federal Drugs Administration rules to comply with)

However, I have tried running this from my own PC and still find it slow. My logic is -

Data and Forms in one file on server = Fast

Data and Forms split on SAME server = Slow

Data on server and Forms on local PC = Slow

I still can't owrk out why a split database should run dramaticaly slower when it is run in the same environment.

It's not just a little bit slower. It's a long wait even to build a combo box listing from a table with only 12 records.

Any Suggenstions?

Peter

 

[GBall]

Given the restrictions that you have, it begs the question as to why you want to split the database anyway.
In your situation, I can't see any advantage.
It's still bizarre why it runs so slow thiugh !

Graham

 

[NoteSmith]

Splitting the database with both FE and BE on the network is not so outrageous. I have made tweaks and changes to a similar system for 2.5 years. I would have to recopy the FE to 75 machines in 3 buildings over 2 miles every time I made a change to the front end.

Any network/access experts? It sounds like the front end may be making unnecessary calls to the server looking for what is right under its nose.

 

[Rhonin]

When running a split system over a network, dependent upon the network speed and whether it is tracking recordlocks or not it will get REAL slow. As a test I have a database (front end 14meg) and back (356meg 164000 records in main)and if I place both of them on the same server in a shared envirement it will actually time out. I have done this with both 97 and 2K. If I split them they run fine. To see if the network is the issue, drop both on your PC and see if the same issue exists. If it does there is a conflict in the database you will have to find.

If you split the FE to your PC, you can do a quick script to backup to the network on demand.
As for updating multiple PC's, set it up on the network logon to auto update if date of creation on pc is < network or email a batch file that will perform the update. I am sure there are many more fairly simple ways to do this depending on envvirement.

Anyway, good luck with your db.

 

[ahmun]

Here is a discussion on why Access (Any Version?) runs so slow if you put Front end on local, and backend on server... or just if you put anything on the server...

thread181-72518 Earnie Eng - Newbie Access Programmer/DBA

---------------------------------

If you are born once, you will die twice.
If you are born twice, you will die once.

 

[Newposter]

Just a thought, but have you indexed one or more fields? This affects the speed dramatically.
Also, when splitting the database, did you do it manually by exporting objects, or with the Access utility? Perhaps one file has remnants of the other in there.
Finally, your form design can slow things down if you have a lot of embedded or linked graphics, or if any calculations are performed upon opening.

 

[dschomburg]

You said you have 1 main table with other lookup tables.
How many fields are in the main table for these 2500 records? And Where is your server located? In the same building as you?

 

[dkaplan]

I see this link has been going for some time, and I'm curious as to whether you've ever gotten it resolved, as I've had similiar problems. In my case the slowness was the result of subforms. Removing them increased the speed dramatically.

Dennis

 

[leobaby]

Here is a suggestion that helped me tremendously. I had a form on a split database that was running quite slowly; It had many subforms. I made each of those subforms Unbound and populated the fields myself with code. This was just about as fast as removing the subforms. If you need code examples, just reply.

Matthew

 

[Junior1544]

well, i had similare problems... but mine was fixed with network upgrades... i have a closed lab that has a 10 base t closed network using hub's... that's where i develope, and they wont upgrade it because they say you can only use one pc at a time and it's a waste to upgrade it... by my deployment network is 100 base with switches... so i deal with it

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.