Profiling the "Calculating ..." message.

[revver]

PRELIM INFO
ACC2003. The setup is typical FrontEnd / BackEnd both in the same folder.

I have a form with 2 subforms, one each on page2 and page3 of a tab control. There isn't much data in the main table, a little in one linked table and almost none in the other linked table. There are two DLookup text boxes on the form to display sensible data rather than a code which is stored in the main table.

This should not cause a slowness problem and doesn't on my development machine. In production, however, it can be frustratingly slow moving from one record to the next (ie populating the form).

There is some minor code in the OnCurrent event. It synchronizes 2 combo boxes with the current record and checks if the item is inactive and, if so, changes some font colours and labels.

THE PROBLEM
When moving to the next (or prev) record OR using one of the find combo boxes to display a different record, there is a delay of many seconds, sometimes close to a minute, before the form is populated. Neither subform is visible at this stage. The message "Calculating ..." appears on the status bar during this delay.

Is there any way I can find out where the problem is?

 

[HiTechCoach]

My first thought is is the DLookups are the issue. I rarely use except on very small tables. Less that 50-100 records

As a test, on a copy of the front end try deleting teh controls with the DLookup.

Is the front end shared? Each user really should have a copy of the front end on their local hard drive.

 

[revver]

Thanks Coach,
I'll try that. The two lookups are for Name and Address from a customer code. That table has about 300 records.

The front end is shared. 3 users. I've never known an occasion where more than one user is using it.

 

[HiTechCoach]

Thanks Coach,
I'll try that. The two lookups are for Name and Address from a customer code. That table has about 300 records.

The front end is shared. 3 users. I've never known an occasion where more than one user is using it.

I find a sub form is much better at getting the customer details than the DLookups.


Loading objects in a a Front end over the network is always slower than if you have it local.

 

[revver]

Sorry Coach,
I don't understand.

I think you are suggesting that, say another page on the tab control with a subform where the customer details are displayed, derived from the customer code on the main form. I don't see how that helps. How does that avoid a lookup? I must be missing something.

Aaah. I think I get you. If I replace the present name address area with a subform ...
The relationship between the main table and customer table is many-many which I think makes it a non-viable choice in this case.
Or am I still missing your point?

FYI, the main table is Equipment. The two related tables are Movements and Maintenance. The customer code is used as a Location Code because the item of equipment is at the customers premises. The FE and BE live in the same folder on the file server. The terminal server is in the same rack and they connect using a gigabit switch. Not quite local I agree but I wouldn't envisage much difference if I moved FE/BE from FS to TS. Would you?

In a couple of hours when the system will be in peak use, I'll copy the front end and do as you suggest. That should shed some light.

 

[revver]

I have just copied and renamed the front end and removed the lookup field and some combo boxes (and their code). Now the response is erratic.

At form launch it took 30+ sec for Calculating... to go away. I'm not sure how much of that is due to On-Load and OnActivate rather than OnCurrent so I'll ignore that for the time being.

Then I clicked 'next' several times and got the following response times in secs.
0 0 5 1 0 0 0 0 6 20 0 0 0 0 0 0 0 0 9

My suspicion now is that other users on the same TS are using access with other MDBs. Could it be anything else?

 

[Norie]

revver

What code/lookups do you have in the form(s)?

Couldn't you just create a query that returns all the fields you need for the main form and use that as the Record Source?

Then you shouldn't need any lookups.

 

[HiTechCoach]

I have just copied and renamed the front end and removed the lookup field and some combo boxes (and their code). Now the response is erratic.

At form launch it took 30+ sec for Calculating... to go away. I'm not sure how much of that is due to On-Load and OnActivate rather than OnCurrent so I'll ignore that for the time being.

Then I clicked 'next' several times and got the following response times in secs.
0 0 5 1 0 0 0 0 6 20 0 0 0 0 0 0 0 0 9

My suspicion now is that other users on the same TS are using access with other MDBs. Could it be anything else?

The performance difference may also be due to the amount of related data getting load for different records in the parent form.

I have 400+ users on different terminal servers. Some terminal server have 40+ users. I find that you still do NOT want to share the front end. Every user has a copy of the front end in their own profile. A database should never be open by multiple copies of Access. It is OK to have multiple front ends linked to the same back end.

 

[revver]

The performance difference may also be due to the amount of related data getting load for different records in the parent form.

Once again I may be missing your point. Do you mean the amount of data in the 2 subforms? The RecordSource itself is the table of 920 records. The major related table has 1145 records and the minor has no records yet.

I have 400+ users on different terminal servers. Some terminal server have 40+ users. I find that you still do NOT want to share the front end. Every user has a copy of the front end in their own profile. A database should never be open by multiple copies of Access. It is OK to have multiple front ends linked to the same back end.

As far as I can tell, two users have never had the same FE open at the same time. Nevertheless, it is a small matter to give all three their own copy. This will be an interesting experiment because it means that the FE and Access will be on TS and data on the FS rather than Access only on TS and the rest on FS as it is now.
BTW, this FE links to 2 BEs, the main BE and the Customer BE (which is used for the lookup).


Norie, I could have created a query as you say but I didn't because the domain of the dlookup is a union query and a compound one at that. What I mean by compound is that one field of the union query is a concatenation of three fields and static data (punctuation) from each table in the union. I mistakenly said at the outset that there were 2 dlookups - no, there's just one but a compound one.

Boyd, did you have a comment on my post #5 in this thread?
Also, it is nearing time to walk away from 2003 and I am considering SQL server or 2010. Any pointers about the conversion process to either of these?

 

[Norie]

You are using a UNION query for the domain for the lookup and the data is in a separate database.

Perhaps that could be somethng to do with the performance?

By the way, is it a UNION ALL query?