Appalling performance after splitting database

[JeremyI]

Hi all, working in Access 2003 (using the Access 2000 file format).

I've been developing a moderate-sized database and have recently secured write permission for the network folder that is to be its permanent home. So after moving it there, I went to split the front and back ends so that I could test the links, distribute the front end, and begin training the administrators.

But it hasn't gone well.

Previously, it only took at most a couple of seconds to load up the main form, whether I was running it off of the local hard drive or the network. But now any of the forms are taking a good 10-15 seconds, and at one point I timed the main one at 97 seconds. This is while there is still no data to speak of--only a description and one or two long integer fields for each of 59 records, with very little related data.

I thought splitting was supposed to help it run faster? That's what I confidently informed the client, anyway...

None of the Access fixes I can think of have done a thing when I've tried them:

- compact and repair back end and front end
- compile front end
- analyze performance of back end (no suggestions provided by Access)

The one remaining option I have come up with is to try seeing if I can type in the direct network address for the table links, rather than the drives that have been mapped for me, in case there is some sort of slowness resulting from the lookup--but I have my doubts. This process will require attempting to get that information out of the IT department.

Meanwhile, does anyone have other ideas of what I can do? And any projections about whether the re-linking will help?

Obviously I can't keep developing until this is dealt with, so for now I am going back to the pre-split backup. Look forward to trying any suggestions, thanks!

[NeoPa]

I'm surprised Jeremy. I've never had similar issues, and I still work mainly in 2003 and on various 2000 databases across the network. Is it possible that it's a one-off hit, and that subsequent opens of the database via the front-end are more timely?

To find the UNC path from a drive letter (without requiring IT assistance) go to a command prompt window and type NET USE <Enter>.

[JeremyI]

Thanks, NeoPa. Good to have confirmation at least that this is Not What's Supposed To Happen.

I've done some digging and learned with certainty that the client uses a WAN, which could account for the symptoms, according to this thread:

http://bytes.com/topic/access/answers/768428-sooooooooo-slooooooow

and this website by one of the thread contributors:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

I checked NET USE (thank you very much for that tip) and was then able to type the full application path in when linking a couple of sample tables to a clone of the database. The more linked tables, the worse the problem seems to get. There is one form remaining that doesn't query any information from the tables I have linked, and it still opens instantly. So I figure there's no need to keep trying the UNC path for all tables.

Although there are some solutions suggested in the links above, they would need the co-operation of IT, and it's an 'abnormal' enough request that I couldn't possibly get that working in the very short timescale I have been allowed. The department is notorious.

All of this is really annoying to realise, because the database will be mission critical once they start using it.

I think really the only thing to be done for the moment is keep it unsplit and put in a quick/simple backup on application close that preserves 5 copies as a safety net in case of disconnects that corrupt it. Will Access be happy enough to let me do that if I make sure no bound forms are open?

[NeoPa]

JeremyI:
I've done some digging and learned with certainty that the client uses a WAN

I'm very curious about this statement. Maybe I'm missing something but I can't imagine this is an accurate reflection of your issue.

Clients use networking. Whether that network communicates via a WAN or only a LAN is down to the configuration of the network itself. The client cannot possibly (except maybe in really weird and exceptional circumstances, unlikely to occur in any business environment) determine to use the WAN of it's own accord. It's possible the traffic is being detected to go that way, but that's quite different from what you posted (albeit possibly unintentional on your part).

If it simply that the server is not reachable across the office LAN, than a simpler, more reliable, solution would be to find somewhere that is LAN-accessible to all your potential users. If that's not possible, then you certainly have a problem. As Tony Toews said in the linked thread, Access doesn't handle WANs very well at all.

[JeremyI]

I was very confused when I first read the post above, but eventually I realised my mistake when writing #3: by the client, I actually meant the client I am working for. Sorry about the lack of clarity!

So anyway, yes, they seem to have implemented a WAN, and I don't even know where to begin to mitigate the impact this has on Access. It's no doubt apparent that I do not know anything about networks--no formal IT training, I just learn what I need as I go--but I do know just how slow and conservative this IT department is. I would not expect any help from them before the end of the month, when the project needs to be basically ready to go.

If I can't tweak anything as a normal user, then the database will have to remain unsplit, because the slow performance would actually constitute a greater 'risk' to the success of the overall project I'm part of.

Quite discouraging!

As always, thanks for making my picture of the world that little bit clearer.

[NeoPa]

Sorry Jeremy. It didn't even occur to me to think of the client that way in these circumstances, though when I read it again now I can see it reads sensibly.

Clearly, if they have nothing LAN based, then Access won't like it.

If they have a LAN that their PCs connect to, but a server only across a WAN, then you may be able to treat one of the PCs as a server yourself. Any one of them can behave as a small server to handle up to a maximum of ten clients.

Does this match your situation at all?

[JeremyI]

That sounds very promising... I know I've seen a couple of computers acting as print servers here, so there is a precedent. And there probably needs to be very little simultaneous access to this particular database.

I'll bring it up in the team meeting to see what the managers think of housing the back-end on a local hard drive. I'd then either train the administrators to back up to the server or program a straightforward file copy. Or both.

Just remains to figure out what to tell them to do in case they decide to move it to a different machine (could be a few years down the road).

Would that just be a matter of re-linking?

[NeoPa]

JeremyI:
Would that just be a matter of re-linking?

It could be. Alternatively there is code somewhere that allows the FE to detect the absence of the file on the server and relink to somewhere else if that destination is specified. You could set something like that up if you really want to impress.

[JeremyI]

Much appreciated. I'll let you know how it turns out.

[dsatino]

I didn't notice any mention of what the forms are doing, but if any of them are data entry forms you should change the form property to data entry.

This will definitely help with speed issues because forms that are data entry don't load the entire recordset that they are based on, they just open.

[NeoPa]

JeremyI:
I'll let you know how it turns out.

Please do :-)

If you need to post a question about the relinking part then post the link in here and I'll have a look at it and see if I can dig out what I was referring to.

Actually, I did a bit of searching anyway and came up with Relinking ODBC Tables using VBA. See if that's any use to you (assuming you want to go down that route of course). Post #2 is the main thing to look at, which is about linked Access tables as opposed to linked ODBC ones per the title.

[JeremyI]

@dsatino:
It's a good possibility to remember, but for this project there will not really be much strict data entry; everything needs to be dynamic and inter-linked.

@NeoPa:
Hoping there will be enough time allowed for me to implement a good solution! The code in the linked thread would be very helpful.

[NeoPa]

I hope it helps Jeremy.

It's not the sort of thing everybody knows about, so could make you stand out from the crowd if you sell it right.