program too slow. Users complaining! 6

[pronate]

Hi,

In a file/server setting, mulit-user, about 27 ppl. Sometime it takes rather long for a form to load. almost 20 to 30 seconds.

I tried to index the common sorting and query fields, and setting sub-datasheet name to [none] in table properties, use single forms as much as possible instead of continous forms...trying to make it run faster.

Are there any other ways to speed up the program? Upgrading hardware is out of the question at this monment.

Cheers
Sam

 

[nickjar2]

I have found using unbound forms, the forms load really quickly. I did some work with Acc 2000 and an Oracle backend, using ADO and OLEDB, with all forms being unbound. The forms loaded quicker with the Oracle backend (unbound), then they did with a normal Access db (bound forms). Obviously it takes a lot longer to develop, but there are a lot of advantages to using unbound forms. Nick (Everton Rool OK!)

 

[thornmastr]

Sam,

There are over 160 very good tips on speeding up Access at

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

while these are written for A97 they are pretty generic for Access across the board. There are also specific networking tips as well.


Also, If you look in the Googles archive, you will find heaps of parameters that can have an impact on your applications performance in a network. Here are some of them: Have you compacted the backend? If not, first take a back up copy and then compact it. If you have got some code in the BeforeUpdate or AfterUpdate events of the controls or form where you add your new records, take a look at that code and see if it can be the reason for the slow adding of new records. If you find the code can be more effective, rewrite it. In your database tables, set the Subdatasheet Name property (in the tables property sheet) to None. You mention the use of indexes, so I guess you know how to use them effectively. If not, examine that issue closer by reading the Help files and spending some time with Google. Build your forms with recordsources that returns as few records as possible. A form that is only used for adding new records can have a recordset that returns no records at all, e.g. by setting 0 as a criteria for the primary key. Remove fields from the recordsource that is not used in the report / form, and for fields that are only used as criteria for the recordsource, uncheck the Show checkbox (in the query builder). Prevent Access from trying to delete / rebuild the locking file (ldb), by creating a persistent connection to the backend from each of the frontends. You can achieve this by creating a table with one field in the backend, and then build a hidden form in the frontend that opens in the Startup and stays open. This form should have the new table as its recordsource. It is often said that "Autocorrect" should be turned off to improve speed. There are two autocorrect functions in Access 2000, and I am not sure that both are meant. One corrects typing, which you find in the menu Tools in the database window. The other one you can find in the Visual Basic Editor, under Options, General. It is three options dealing with the changing of names of database objects. This one seems to affect speed, and it can also destroy the page setup for your reports, so I would definitely uncheck these options.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[SgtJarrow]

Robert,

That's an awsome reference! (as I hit the print key). Thanks! Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[JeremyNYC]

Pronate,

I'd love to hear back on what you do to speed up the database and how effective any of it is. Certainly all of the concepts that Robert mentioned will help, if you've not already been using those strategies, and I'm sure there are other things on the FMS site that will help, too. But this looks like a good opportunity to hear a real-world example of taking a slow application and making it speedier.

And definitely post back with specific questions about any of the strategies you're considering.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[thornmastr]

Jeremy,

I have a suspicion that Pronate is simply under the gun and has been told to solve the problem very quickly with no extra time and not too many resources. In which case, having been in that situation, the real advise is very simple:

1) Move the BE as far up toward the root directory as his network administrator will allow.
2) Replace all the NICS on users PC’s with top of the line 3-COMS.
3) Max memory on the users PC’s.

At some point after that he will be able to take the time to really improve the application design, optimize his tables and his queries, etc. but right now, those 3 simple steps are usually enough to get management off your neck.

I think it’s Mike’s sig line that comments on there is always enough time to redo a project and never enough time to design it right the first time.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[JeremyNYC]

Robert,

Yeah, you may be right. I guess I just got excited to watch someone go through this process and see which of these tips works for him.

Funny thing. I went back to that link and printed out the doc, too, though I read through it a few years ago. Still some things that I don't do every time and still some things I've never really bothered to do. Little by little I'll be tweaking my big app with these things. If I find any breakthroughs I'll post back here, too.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[pronate]

Thanks for all the great tips....

I forgot the obvious....compact and repair...well, that certainly help. My 25MB BE shrink to a mere 3 MB...i guess i have to do this more often as there are a lot of editing and adding of records daily.

Next thing i will try it to move it higher up to the root directory...

Under Tools/Option/Advance on DDE operations. Should i uncheck all?? I remember reading something about this setting, can't recall what.

I wonder if my picture property of my forms which is embedded is also slowing things down. This i can do without.

Will post back for any improvements.

 

[JeremyNYC]

Pronate,

Yeah, compacting is very important. More than editing and adding records, what's going to cause file bloat is creation and deletion of temporary records and other objects and the editing of objects such as forms. Compacting should happen once a week or so regardless of bloat, and more often if it's severe.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[neillovell]

In that case, can you automate compact and repair using an IIF statement?

 

[pronate]

hi,

How? how can i automate this compact and repair procedure?? I have trying to do it...can it be a "Onclose" event? if there is one.

Help appreciated.

Thanks
Sam

 

[neillovell]

Maybe something like
if (date = end of the week)
{
compact_and_repair();
}

forgive the non-vba style

 

[thornmastr]

Assuming you are broken apart into FE and BE.
in your front-end program just do the DBEngine.CompactDatabase
when everyone exits and use error handling to trap the error 3356 (I think,
test this yourself) that occurs if the database is still in use,
and just Resume Next or something. The error will occur when everyone
except the last person exits and it tries to compact, so the effect will be
that it will only compact when the last person exits.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com