Slow multi-user application, 1 user is fine...

#1

Hi,

I am facing difficulties with an (old) app of mine.
It's an Access 2.0 application which has been running for years now.
I got complaints from a client (one of the 'heavy' users with lots of records) about multi-user performance.

I converted to Access 2003, and now I want to distribute this app with the runtime of Access 2007.
So far so good, conversion was not at all easy, but the app is working fine now,

BUT as soon as the app is used by more than 1 person the performance is turning to horrible.
With one user the performance is very fast, but as soon as the second user logs on, the app seems to 'bump' ... but against what ??

Of course I checked Tony's performance faq and I was already using lots of tips mentioned there.
e.g. persistent connection and loading recordsource on 'heavy' forms at runtime.

I just got a report back from a testscenario:
-- Two XP machines using a local workgroup (no domain involved here)
Both machines on a separate switch. isolated from the corporate network
-- Performance is fine (very speedy) with one user.
As soon as the second user starts the app (even doing nothing...) it seems to freeze...

Even when the second user logs out the performance stays bad...
The initial user has to log out and log in again to get the initial performance back...

FYI: Also with the Access 2.0 app it's is the same problem. (I just did not know that...)
In fact the 'old' app even runs faster....

Any ideas?

Regards, Arno R

#2

On Thu, 4 Nov 2010 10:36:41 +0100, "Arno R"
<arracomn_o_s_p_a_m (AT) planet (DOT) nl> wrote:

Quote:

BUT as soon as the app is used by more than 1 person the performance is turning to horrible.
With one user the performance is very fast, but as soon as the second user logs on, the app seems to 'bump' ... but against what ??

Of course I checked Tony's performance faq and I was already using lots of tips mentioned there.
e.g. persistent connection and loading recordsource on 'heavy' forms at runtime.

Well, it still sounds like the persistent connectioh problem. Just
how are you doing that? For example do you have a form level
database/recordset variable or a module (which would then be global)
variable? If the former as soon as you close the form the variable
would go away.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

#3

Hi Tony,

I am using a bound main-menu Form which is *always* open.
This form is bound to a one-record table (Application-settings) from the backend.

Arno R

"Tony Toews" <ttoews (AT) telusplanet (DOT) net> schreef in bericht news:ui56d6l8el3b2pgo5mg62sd9vhc8iu1293 (AT) 4ax (DOT) com...

Quote:

On Thu, 4 Nov 2010 10:36:41 +0100, "Arno R"
arracomn_o_s_p_a_m (AT) planet (DOT) nl> wrote:

BUT as soon as the app is used by more than 1 person the performance is turning to horrible.
With one user the performance is very fast, but as soon as the second user logs on, the app seems to 'bump' ... but against what
??

Of course I checked Tony's performance faq and I was already using lots of tips mentioned there.
e.g. persistent connection and loading recordsource on 'heavy' forms at runtime.

Well, it still sounds like the persistent connectioh problem. Just
how are you doing that? For example do you have a form level
database/recordset variable or a module (which would then be global)
variable? If the former as soon as you close the form the variable
would go away.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

#4

On Fri, 5 Nov 2010 00:16:30 +0100, "Arno R"
<arracomn_o_s_p_a_m (AT) planet (DOT) nl> wrote:

Quote:

I am using a bound main-menu Form which is *always* open.
This form is bound to a one-record table (Application-settings) from the backend.

Yup, that's the standard foolproof method. Then I'm not sure what
is going on.

BTW you have to watch for users hitting the mouse wheel on such one
record forms. If they do so and somehow manage to enter a seconad
blank record interesting things can happen in your Access database.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

#5

Thanks Tony,

I am aware of the mouseweel isue.
AllowAdditions = False solves this...
And... I have indeed no need for more 'interesting' things... <grin>

BTW: This 'problem-app' is not slow *all* the time.
Retrieving records is no problem. The problem is adding records as I see now...

I will try to explain:
There is a form that is opened several times every day with actual records of the choosen week.
Before the form opens, new records are added by means of a 'FillWeek' procedure.
This procedure gathers the needed records (in a temptable) and simply tries to add them to the backend-table (whether they exist or
not)
Records that exist (same secondary index) are discarded by Access.
This procedure seems to be causing the slowness...
When I choose a 'closed' week (no records can be added, so the procedure 'FillWeek' is not used) the app is responding fine.

So, as I see now, the problem is adding records (append query) while the app is opened by more than one user.
Any more or other ideas ??

Regards,
Arno R

"Tony Toews" <ttoews (AT) telusplanet (DOT) net> schreef in bericht news:0km6d61s25dv8mffc2gldvq0ufngjsjlge (AT) 4ax (DOT) com...

Quote:

BTW you have to watch for users hitting the mouse wheel on such one
record forms. If they do so and somehow manage to enter a seconad
blank record interesting things can happen in your Access database.

Tony

#6

On Fri, 5 Nov 2010 13:43:23 +0100, "Arno R"
<arracomn_o_s_p_a_m (AT) planet (DOT) nl> wrote:

Quote:

BTW: This 'problem-app' is not slow *all* the time.
Retrieving records is no problem. The problem is adding records as I see now...

Ahh, ok, now things are making a bit more sense.

Quote:

I will try to explain:
There is a form that is opened several times every day with actual records of the choosen week.
Before the form opens, new records are added by means of a 'FillWeek' procedure.
This procedure gathers the needed records (in a temptable) and simply tries to add them to the backend-table (whether they exist or
not)
Records that exist (same secondary index) are discarded by Access.
This procedure seems to be causing the slowness...
When I choose a 'closed' week (no records can be added, so the procedure 'FillWeek' is not used) the app is responding fine.

I've noticed when attempting to append records which are "dupes" of
existing records that autonumbers are consumed. That is if you
attempt to add 100 records but only 20 are succesfully added that 80
autonumbers are "used up". Thus I'm assuming Access/Jet is allocating
that space in pages.

Now what I've also surmised over the years is that Jet 4.0, when the
backend database is opened by multiple users that records are added
one per 4k page. The compact and repair will put multiple records in
the 4k page.

So I'm thinking that if you can change that process to not add those
records by doing some querying you may find this to be a lot faster as
those extra pages don't need to be created. And this shouldn't be too
difficult to do either.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

#7

Arno R wrote:

Quote:

BTW: This 'problem-app' is not slow *all* the time.
Retrieving records is no problem. The problem is adding records as I see now...

I will try to explain:
There is a form that is opened several times every day with actual records of the choosen week.
Before the form opens, new records are added by means of a 'FillWeek' procedure.
This procedure gathers the needed records (in a temptable) and simply tries to add them to the backend-table (whether they exist or
not)
Records that exist (same secondary index) are discarded by Access.
This procedure seems to be causing the slowness...
When I choose a 'closed' week (no records can be added, so the procedure 'FillWeek' is not used) the app is responding fine.

So, as I see now, the problem is adding records (append query) while the app is opened by more than one user.
Any more or other ideas ??

Use an unmatched query in the append:

INSERT INTO table
SELECT T.f1, T.f2, ...
FROM temp As T LEFT JOIN table
ON T.id= table.id And T.sod = table.sid
WHERE table.sid Is Null

OTOH, using a temp table may account for part of the
slowness.

--
Marsh

#8

On Nov 4, 5:36*am, "Arno R" <arracomn_o_s_p_... (AT) planet (DOT) nl> wrote:

Quote:

Hi,

I am facing difficulties with an (old) app of mine.
It's an Access 2.0 application which has been running for years now.
I got complaints from a client (one of the 'heavy' users with lots of records) about multi-user performance.

I converted to Access 2003, and now I want to distribute this app with the runtime of Access 2007.
So far so good, conversion was not at all easy, but the app is working fine now,

BUT as soon as the app is used by more than 1 person the performance is turning to horrible.
With one user the performance is very fast, but as soon as the second user logs on, the app seems to 'bump' ... but against what ??

Of course I checked Tony's performance faq and I was already using lots of tips mentioned there.
e.g. persistent connection and loading recordsource on 'heavy' forms at runtime.

I just got a report back from a testscenario:
-- Two XP machines using a local workgroup (no domain involved here)
* * Both machines on a separate switch. isolated from the corporate network
-- Performance is fine (very speedy) with one user.
* *As soon as the second user starts the app (even doing nothing...) it seems to freeze...

Even when the second user logs out the performance stays bad...
The initial user has to log out and log in again to get the initial performance back...

FYI: Also with the Access 2.0 app it's is the same problem. (I just did not know that...)
In fact the 'old' app even runs faster....

Any ideas?

Regards, Arno R

Have you looked at/tried some of the more simpler things like;
- from a code page making sure all your references are correct
- did a de-compile and then compile
- did a refresh table links

just a thought....

#9

"Marshall Barton" <marshbarton (AT) wowway (DOT) com> schreef in bericht news:7ck8d6pb7m1lv1ov0ss71e7k1bj17s2l2d (AT) 4ax (DOT) com...

Hi Marshall (and Tony again)

I just discovered: When I delete the secondary key (unique key on three fields) the speed is fine...
Could there be a problem with indexes because of the conversion ??
==> I am considering to recreate the relations and indexes again...

But without the sec key the speed is fine... (PK is just an autonumber.)
So now I have to make *sure* that records in the temptable to be appended are not yet in the backend-table...
Well.. I have to be *sure*...!
When I keep the secondary key and only append recs that don't exist, the append is still painfully slow...

BTW: Using a temptable is very fast... No problem at all with that.

Regards, Arno R

Quote:

Use an unmatched query in the append:

INSERT INTO table
SELECT T.f1, T.f2, ...
FROM temp As T LEFT JOIN table
ON T.id= table.id And T.sod = table.sid
WHERE table.sid Is Null

OTOH, using a temp table may account for part of the
slowness.

--
Marsh

#10

"bobh" <vulcaned (AT) yahoo (DOT) com> schreef in bericht news:6b404a4b-89d7-4670-b632-df457efcd056 (AT) m20g2000prc (DOT) googlegroups.com...

Thanks for your reply,

Yes I did the obvious indeed...
Even created a blank db and imported everything.

But I still may overlook something....

Arno R

Have you looked at/tried some of the more simpler things like;
- from a code page making sure all your references are correct
- did a de-compile and then compile
- did a refresh table links

just a thought....