Load Time: Database speed over network

[Kodama76]

I have a database that a company has designed for me. It is split into front ends (mde databases of forms and reports, one for each user) on the server and a single "data" database.

The problem is that every time one of my users tries to open a certain form in the database from their local computer it takes almost 5 minutes to load. If I open that form while I'm logged into the server itself it takes me almost no time at all.

Each desktop has Access loaded on it and all of the databases themselves are on the server. I've checked out the network and the users PC's and everything looks good speedwise and all the software is up to date. The server itself is barely using 10% of its resources. No matter who is logged in and no matter which computer they are logging in from we get the same thing. The front ends are each around 40 mb and the "data" database is around 200 mb.

The designer keeps telling me that it has to be a network problem, or that the server has bad sectors on one of the drives, or some other "not my database" reason. Today he said that I should change the Windows Temp settings on each of the users' machines to point to new temp folders on the server....does that sound crazy to anyone else?!?!? Anyone have any suggestions on how to make this sitation better.

 

[Kodo]

The only thing that comes to mind is the complexity of the front end, the fact that it's 40MB!! and having to populate a recordset of some 200MB (or less depending on the queries etc). So not only are you yanking 40MB across the network, but you're loading controls as well and then the data. I'm not surprised that it's taking a few minutes to load up. I haven't been working with Access long enough to see a 40MB front end, but DAMN! that sounds awful big to me.. Escpecially for an MDE.

Maybe someone with more experience can offer some more insight, but my thoughts are strictly data transfer related. Can I ask what this database does for you? Is it a canned or custom app?

 

[WayneRyan]

Kodama,

40 Mb is way too large for a front-end! There had better
be some very nice embedded graphics, or something is very wrong.

As a front-end, it shouldn't have it's forms based on tables.
Selective queries should be used to restrict the amount of data
accross the net.

The .MDEs are on your machine, but they were derived from a .MDB
file that you could actually examine. You probably can't ascertain
much by looking at the .MDE, but either of the two topics above
produce the symptoms you describe.

hth,
Wayne

 

[ChrisO]

Well I agree entirely with Kodo. (Edit…and with Wayne. Very slow of me again. )

If the FE’s were local to the workstations then that’s 40Meg less to pull down the wire. Also a 40Meg MDE FE is a bit large, have the designers tried a /decompile and compact of the MDB?

I’ll stick my neck out a bit further and agree with the designers when they say, “it has to be a network problem”.

You have stated that when logged on to the server there is little delay, all other times there is a delay. What is the difference?… the network!

Now the network problem encompasses many things: -
1. The FE’s not being on the workstation.
2. Intermittent hardware failures that cause a retry.
3. Network overloading. (Which should not apply when testing with only one workstation on-line if the FE is on the workstation, and…)
4. A Form or Report bound directly to a Table of considerable size.
5. Please add to the list.

It may be viewed that point 4 is a database design problem and it would be if it was designed for that purpose.

Did the designers really design a 40Meg FE looking at a 200Meg BE or did it grow that way?

Regards,
Chris.

 

[Kodama76]

Thanks for the help everyone. I was thinking that was a little large for a FE. They've always been that large and there aren't any graphics short of the few really small ones they are using for special buttons and two backgrounds with our logo on them. Sadly I can't get them to give me the mdb version to look at since we are still paying for this "software". I'm starting to really mistrust their work.

I'm going to try moving the FE's to the workstations to see if that improves things but I think you guys hit the nail on the head with choice number 4.Form bound directly to a really large table. The FE itself and all the other forms take almost no time to open no matter what machine opens them but this one form is majorly slow to load. I think we are going to call in another designer that we know and ask to have him take a look at that. Uggggh this has really been a thorn in my side.

Thanks again

 

[giedrius]

My experience is that FE might increase very significantly in size during the design process. In fact I have a very strong feeling that Access does keep copies of deleted controls or even controls that have been moved from one place to another. Therefore it is essential to compact your FE before compilation. Use /decompile option as ChrisO advices. Also it is common practice to place a copy of your FE on each user's machine.

regards,
giedrius

 

[Pat Hartman]

I have a very strong feeling that Access does keep copies of deleted controls or even controls that have been moved from one place to another

- it does.

When the .mde is built, all objects are compiled and only the compiled code goes into the .mde so it should start out small. Of course there may just be a lot of objects.

To find out how much of an impact the network has on the db, copy BOTH the fe and be to the c: drive of a typical workstation. Time the form opening in that scenerio. If it still seems sluggish, it is the form. If it is fast, it is the network.

If the tables are Jet, it doesn't matter whether the forms are bound to a table or a query except that using a query with a select clause is better practice. If the tables are ODBC, it is CRITICAL that the forms/reports be bound to queries with selection criteria.

Access doesn't wait until all records are retrieved before displaying a form. All it takes is a single record. Access displays the form/report as quickly as possible to hide the actual time it takes to bring down the entire recordset.

 

[pono1]

The problem is that every time one of my users tries to open a certain form in the database from their local computer it takes almost 5 minutes to load.

So only one form opens slowly for one user on one computer? I'm assuming this is not the case -- that, instead, all users on all computers (other than the server) experience slow performance with this one form.

Does your developer have other customers using this product? If so and he says that you are the only customer having load problems with this form, then that might merit consideration.

Also, the front-end may be large because it's caching data (zip codes? area codes, etc -- who knows?). But I don't think the size of the front-end is relevant to your problem, esp since it's just one form that's loading slowly and the front-end, as you described, loads fine initially.

Is the problem form more complex than all other forms? Does it have a tab control? What version or versions of Access are you running?

A pretty good page that you might want to look over and perhaps share with the developer. Good luck.

Regards,
Tim