Question Split Database sometimes VERY slow (more than 1 user?)

[InstructionWhich7142]

I have a mostly typical FE/BE split, however: My FE opens with no tables linked by default and links are made on the fly depending which BE/ODBC datasource will be needed,

I always start with links to one primary BE "Admin" table where odbc info, user info, version info etc is listed (I link to up to 4 different ACCDB backends and a couple of ODBC sources so prefer the "on the fly")



It seems that if one user has had their FE open for a while this primary BE gets a bit slow/stuck and any other user that tries to open the FE has to wait ~30 seconds for the BE to sort itself out??,

I've also had one occurrence (while trying to trace this problem) where a Dlookup on a table in this primary ACCDB gave the: "The database has been placed in a state by user ‘Admin’ on machine… that prevents it from being opened or locked" error, why on earth would a Dlookup give this?

 

[GinaWhipp]

First issue would probably recreating those links everytime the database is opened. I would also suggest taking a look at...

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

Speed up Database…
http://www.blueclaw-db.com/access_consultant_rapid.htm

 

[InstructionWhich7142]

those are all rather generic suggestions of rather obvious stuff. I've had to restructure a few queries to avoid the "Having" bit of SQL but overall it works very quickly.

Apart from certain times when multiple users are in, I've read some comments about this being related to the Locks temporary file, but I've not found any workarounds?

 

[GinaWhipp]

True, but sometimes the obvious is overlooked.

The best way I have found is to keep a persistent connection open, however, in your case, with the relinking on open, that won't work as excpected. What is your reasoning for relinking? Why not just check the links on open, then it will only relink if a broken link is found.

 

[InstructionWhich7142]

I've just disabled the deleting of links, so the 3 initial tables "stay" linked when the program is closed and dont get relinked on open as they are already linked,

The same behavior occurs, opening takes a very long time

 

[GinaWhipp]

Hmm, okay, please post what code you have in the On_Open, On_Current and On-Load of the Form.

 

[InstructionWhich7142]

There's nothing occurring before the form loads that takes any time at all when a single user opens the FE (I've just inserted some timers and a message box - the MSACCESS application takes longer to load than the form [and code before it])

however as soon as one user has it open on another computer it takes ~5 seconds (time drags when you're waiting for stuff that used to be instant, i'd have sworn it took forever! lol) to do the same open,

the code that runs before includes a 2 dlookups on a table with less than 100 records and an SQL Update - this is all normally instant

 

[InstructionWhich7142]

P.S. I think "slow" is a fair bit faster on my PC than others,

Anyway, a bit of commenting out shows its mostly the two Dlookups that are slow when a second user is in, why is this?

To clarify, over the network, single user, the dlookups are instant (as the tables searched are tiny [sub 100 records]) why does having a second user in change this?

Thanks,

edit: also I've noticed saving changes to forms takes forever if the FE is open on other computers?

 

[bob fitz]

Does each user have a copy of the FE on their own machine, or are all users using a single FE.

 

[InstructionWhich7142]

each user has their own local FE (accde)

I publish updates to a server location from my development accdb (the FE checks for updates and fires another accde to overwrite their local copy with a new one if applicable)

 

[InstructionWhich7142]

Making the links seems to take about 20 seconds before the dlookups add on - (this is why I said 30 seconds in my first post)

I dont really see why making the links after the DB opens would take longer than the DB having to make the links as it loads?

Edit: for comparison, with no other users in: a full relink, dlookup and FormOpen - reports as 1 second

 

[GinaWhipp]

Hmm, maybe this will help...

Everytime I want to go Home, I have to go to the Locksmith first and have a key made

OR

I can just go Home with my existing key and open the door

Which one takes longer? So, following that train of thought, the database has to open, relink and then process the information OR just open make sure the links are open and process the information. Did that help?

 

[InstructionWhich7142]

It would if it always took 20 seconds to walk to the Locksmith, but it doesn't!

Why's there such a performance hit with more than 1 user in? (an LCK file present)

Edit: And if once you had made a 20 second trip to the locksmith on a "bad" day, that if you had to go back it still took 20 seconds!!
(all subsequent relinks, to other secondary BEs are instant)

 

[GinaWhipp]

Hmm, okay well, I'm stumped. Without posting the code, I can't think of what is happening.

Oh, a thought, what is your setting for *Record Locks* under Options?

 

[InstructionWhich7142]

sorry, where in options? (that does sound like the kind of thing it might be)

also FE or BE? or both?

 

[GinaWhipp]

Front end only...
http://www.access-diva.com/d2.html

 

[InstructionWhich7142]

Sorry I meant which bit of options

Curret database doesn't seem to have much about locking?

Client settings is "Default Record Locking" - "No Locks"?

Most of my users are on access runtime (the PC i've been holding it open with to test is on Runtime)

 

[GinaWhipp]

Ah, well, you found it... Client Settings was it and *No Locks* is what you want. I truely can't think of what else is causing the problem. You can look at Method 2 here...

http://support.microsoft.com/kb/815281/en-us

If that helps may have run that line before UPDATE runs but after relinking.

 

[Poppa Smurf]

How big is the front end? Maybe a compact and repair is required.

 

[InstructionWhich7142]

It's set to compact on close,

I just don't understand what changes when other users already have the BE open