Horrible performance with additional users in 2007?

[WinDancer]

I finished my work [I thought] on my latest 2007 database. As soon as a second user fired it off response time went WAY beyond usable.

I split the database with the BE on the network and installed separate copies on three local desktops.

Performance didn't change one iota.

When a second user opens the program it basically quits responding.

The database is not usable the way it is.

Prior to 2007 splitting the dbase produced good response times.

Ideas, please?

Thanks,
Dave

 

[Galaxiom]

Check the quality of the network comunications. What is the response like using Windows to just browse folders on the back end host from the other computers.

Do all the machines have good hardware specification?

Keep a connection open to the BE machine.

Are your Record Source queries only retrieving the necessary records? This makes a big difference on a network when accessing large tables.

Index any fields used in queries.

Don't use Lookup fields in the tables.

 

[pavlos]

Another issue to look is to set the property "Subdatasheet Name" of tables to NONE, it will improve the speed.

 

[boblarson]

This might help too.

 

[gemma-the-husky]

just to shortcut this

in bob's above post (which seems extremely thorough), one of the links is to MS knowledge base article 889588

this has a registry entry change dealing with

"Sharing Violation Notification"


Almost certainly this is the fix you require in this instance.

 

[bulrush]

I would suggest at least a 100mbps network interface for Access2007. Who knows how much bloated overhead it uses. Talk to your IT person.

 

[WinDancer]

I am going through the suggestions offered-

When I create the form for a persistant connection I used the code:

DoCmd.OpenForm "frmPCBE", acNormal, , , acHidden

When opening the new form it is visible??

 

[WinDancer]

gemma-the-husky
Adding the sharing violation entry had no effect-

 

[Banana]

WinDancer -

What if you omit "acNormal" from the parameters?

Alternatively, try this, perhaps?

http://www.fmsinc.com/MicrosoftAccess/Performance/LinkedDatabase.html

 

[Banana]

I would suggest at least a 100mbps network interface for Access2007. Who knows how much bloated overhead it uses. Talk to your IT person.

While I don't deny 2007 has lot of bloat, it would have very little to do with networking anyway as it hasn't materially changed between versions. An exception is if one was opening the front-end off a network share drive, something which shouldn't be even done at all anyway! Galaxiom's advice is more relevant because it concentrates on reducing the records.

 

[WinDancer]

What if you omit "acNormal" from the parameters?
No Change

Still trying to get the persistant to work-

 

[WinDancer]

There are only a few hundred records in the db

 

[bulrush]

Index any fields used in queries.

Shouldn't the OP only index query fields that have criteria? I thought those were the only fields that needed indexing.

 

[WinDancer]

Got the persistant connection working and the form is now hidden [missed one comma].

 

[WinDancer]

Double-checked for indexed fields;
Changed all Subdatasheet Names to none;
Created a persistant form;
Created registry entry for "Sharing Violation Notification"

Tables are on the LAN- Front-end on individual PCs on their C:\Drives
After all the changes today it still runs much too slowly to be usable-
Any other ieas, please?

Thanks,
Dave

 

[Banana]

Is the backend located close to root? That is, it's not buried in folders in folders in folders?

Are you using UNC path or mapped drive path for the linking tables?


Also, you say it performance well for single user but degrades as 2nd user join in. Since there are 3 computers, does any one of those computer perform well when it's the only user? If so, test 3 different pairs and see if the performance is consistent. (this is to help rule out the possibility of a faulty NIC on the client machine... This may not rule out the faulty NIC on server machine, so if it's all same, move the backend somewhere else and see what happens there, too.)

 

[boblarson]

Just a shot in the dark, but I heard of someone else with this problem and it was solved when they found out the Server had a bad Network Interface Card (NIC). Once they replaced it, things went very well.

 

[Mike375]

I have not read the complete thread so this might not be relevant.

I made a simple little data base for tracking diet/calories for the day. Nothing very complicated although it wa fairly messy behind the scenes

On a network it seemed to take forever to open if it was open on the main computer. Once it opened then all was well.

So I rewrote it and all was then fine. The only changes I made was not including redundant queries and forms etc. Oh, and I tried importing to a blank data base but no different

On exactly the same computers a telemarketing data base runs like a charm at startup and startup opens about 12 forms and does quite a few things todo with setting call back times etc.

 

[dcb]

Just a shot in the dark, but I heard of someone else with this problem and it was solved when they found out the Server had a bad Network Interface Card (NIC). Once they replaced it, things went very well.

I've had this issue caused by cards/switch dropping down to 10BaseT on a postgres server - had to lock the NIC's on 1000

Also had issues where the managed networking was routing data the "long way" round - some settings the net-techs had to change

Other options - is your server running dual lan for speed? try unplugging one as a test! (might want to ask IT first.... this would normally start all their phones beeping)

 

[gemma-the-husky]

gemma-the-husky
Adding the sharing violation entry had no effect-

shame - I really thought that would help - you did reboot the server didnt you, to get the settings to take effect?

can you see process spikes etc on the server, when new users connect?