Catastrophic Slow Down when more than ONE USER 1

[waldemar]

Well 20 users shouldn't be too much for a database...

But I have a *serious* performance problem as soon as MORE THAN ONE USER is inside the database.

This is a simple application/data-separated solution without sql server but own workgroup-mdw. As long as ONE USER is online, everthing works fine, but as soon as additional users come in, the database slows down to A FRACTION of the original speed, when:

- Calling Tabledef.RefreshLink:

Set db = CurrentDb
If doesTableExist("", tblTable) = False Then
' ----- TABLE DOESNT EXIST INTERNALLY YET, SO CREATE IT! -----
Set td = db.CreateTableDef(tblTable)
td.Connect = ";Database=" & strDBPath
td.SourceTableName = tblTable
db.TableDefs.Append td
Else
' ----- INTERNAL TABLE EXISTS, SO RE-CONNECT IT -----
Set td = db.TableDefs(tblTable)
td.Connect = ";Database=" & strDBPath
td.RefreshLink
End If

This one works fine.... except td.RefreshLink seems to be the KILLER.

- Running through 1000 to 2000 recordsets via a RecordSet Object

IT FEELS like the database kind of waits or locks (no lock errors, no exclusive stuff)... again: as soon as more than one person is online...

Any help is highly appreciated, this is killing me...

Regards
waldemar

 

[krsherm]

Waldemar,

This is just Access being Access. You aren't doing anything wrong. We have about a dozen users using our mdb BE/FE project, and there is no significant degredation of speed over the LAN regardless of the number of users connected.

That ALL changes when someone needs to relink their tables. At that point, I tell the person to go get a cup of coffee, check their email, wash their car, etc. Restablishing LINKS when there is even ONE other user connected to the BE is terribly slow (it probably has to do with the .ldb file on the back end.)

The best thing to do is plan around it. I would recommend using permanent temp tables instead of dynamically creating, linking to, populating, using, and destroying temp tables.

Other than that, the only reason you should have to reestablish links would be that there was a change to the table structure (or new tables were added), or you are working with identical back end structures (as I do) with different data in each. In that case, you may need to switch from BE_CompanyA to BE_CompanyB. If someone is currently in BE_CompnayB, either ask them to get out so you can connect in five seconds, or as is more polite, go get a cup of coffee and catch on your newsgroup posts.

Hope that helps... or at least lets you know that you're not doing anytyhing wrong.

Rock ON!

Kevin

 

[waldemar]

Hey Kevin, thanks a lot.... I was wondering since there is quite a lot of posts about this Subject... things about Email-Scanners (which seems to be an issue too) and network configuration ("directory tracking?&quot...

Alright, I will try to get around the table linking thing... no, we dont we have frequent changes in the table structure, this was purely for security/integrity reasons (while it was normal speed)...

Cheers Kevin

waldemar