Speed optimization question

[NXMold]

Access 2000 MDB backend and MDE front end on network for 18 total users, 4-6 concurrent. My main interface is a complex form with about six sub forms, all based on queries. The main query joins four tables (~85 records), another one joins two tables (~50 records), the other two are a based on single tables (one has ~5500 records). These record counts are from the query, the underlying tables typically have 3,000 - 10,000 records.

At best, the database opens in just under 3 seconds when there are no other users in it. At worst, I'm recording times up to 62 seconds with 2-3 other users. I wrote a quick function to record this overall time and detailed intervals, the big slow downs are loading each form (recordsource) taking up to 17 seconds! At other times, its less than 1 second.

I want to keep the form structure, so that means taking the time penalty up front, rather than as the user navigates the app.

Is there anything I can do with the queries to increase load speed? I'm suspicious of the network speed too, but not sure what to look for.

 

[MajP]

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

http://www.blueclaw-db.com/access_consultant_rapid.htm

http://www.granite.ab.ca/access/performanceforms.htm

There are a lot of things that need to be considered in a split db. It is probably not your queries. With A2000 there are a few that can have a drastic difference. These are the top three in my opinion for A2k
1. Maintain a persistent connection to backend
2. Turn off the name autocorrect feature
3. Remove subdatasheets from tables

However, read these articles. Some suggestions may give substantial benefits others may not.

 

[GKChesterton]

Great resources MajP, thanks. The FAQs section of this forum has two relevant entries:
[link href=[URL unfurl="true"]http://www.tek-tips.com/faqs.cfm?fid=4401[/URL]]faq702-4401[/url]
[link href=[URL unfurl="true"]http://www.tek-tips.com/faqs.cfm?fid=3742[/URL]]faq702-3742[/url]
... but your citations are better for thoroughness. And my hard experience is that #2 and #3 help more than just speed.

 

[MajP]

Here is an even better resource. I could not find it when I originally posted.

http://www.granite.ab.ca/access/performancefaq.htm

Unfortunately, speeding up your database starts from the very beginning. It is much harder to do after the fact. It needs to be continous in every table, query, form, report, and procedure that you build as you build it.

 

[GKChesterton]

That's the special thing about MS Access. You can start as ignorant or as sloppy as you want, unlike some other packages out there!

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[NXMold]

I have seen some of those pages before, and incorporated a lot of the suggestions already.

Performance is good, its mostly the open speed that is objectionable. I reduced it to less than a second in some cases with no other users, but its still 12, 30, 40, or even 60 seconds when there are other current users. I have not picked up on a specific pattern yet, though some users typically see longer load times than others.

Is the wide variation in opening speed typical?

 

[GKChesterton]

Is the wide variation in opening speed typical? Well, it's not atypical, given the variety of factors that affect network throughput.

Congrats on having already worked through the referenced issues. It may depend a lot on which suggestions you have used. To put it another way, potentially the one you skipped may be the one you need.

For example, for myself, loading recordsets through code was a real chore and remains a chore to maintain; but it was worth the hero-points once my end-users had a fast-loading app. I'd have done it earlier if it hadn't seemed too good to be possible.

If your app opens a form on start-up, obviously that's a place to start picking.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[SeeThru]

One thing I use that may be of help is making forms invisible instead of closing them - obviously far faster, but you have to ensure you deal with any record locking issues.

SeeThru
Synergy Connections Ltd - Telemarketing Services