The Gods Hate Me

[david.brent]

Hello all.
This is a cry for help.

Here's the scene. Our Agency has a very simple retrieval tool written in Access 2000. The client is on the local PC and the back end is on a server. The back end consists of 8 databases (one for each year) and the contain records of all transactions (around 3 million a year). When the client is opened the first thing it does is begger off to each of these databases and checks what tables are available. These databases range from 500mb to 800mb in size. Things have been going fine for the last 3 years until last Friday afternoon...

Strange things started happening around 1.30 on Friday. On starting the retrieval tool the whole thing hangs. After some digging and debugged I found out that the code was hanging when opening the backend databases??? It took ten minutes to get past the database open statement (times this by 8 = 80mins). Now the crazy thing is, that once the database has been opened in the code, it will open instantly all day long. It's as if it doesn't remember how to open the database. It gets a bit stranger..

It only seems to be large (in physical sense) Access databases that take a while to open - anything over 100mb. So what has happened. After interrogating our technical bods, they have admitted a bit of a screw up. They built an SMS package to deliver some security update (at Friday at 1 O'clock) but didn't build it on a clean machine (very bad). This meant all sorts of rubbish from the previous SMS build was included? The only way they have been able to fix the problem so far (and they have tried lots of things, which have created more problems) is to format the client hard drive and re-install everything. Just simply re-installing Office doesn't work. They like this method but with over 500 PCs to look at, it's going to take them quite a while.

Have any of you good people come accross this problem at all. A reply of yes would be great - at least I'll know I'm not alone. If someone knows how to fix this mess, I would be eternally greatful. It would make my day and would be even better than watching an episode of Little Britain!

Thank you if you have read this far.

Take Care

 

[yellow]

Have you tried compacting each of the backend databases?

 

[david.brent]

Thanks Yellow. Just tried compacting. No change. Then I tried creating a new database and importing all the tables. Still no joy. Then I tried converting to different versions of Access again no joy. I'm still stumped. I am now thinking it's either the combination of the client/server or the bloody network but it could be Access I suppose. Any thoughts anyone???

Thank You again Yellow for taking the time.

 

[The_Doc_Man]

I guess the next question is whether the Sys Admin on the server followed the rule about patching...

After patching the app, did he re-apply the O/S patches?

What COULD have happened is that a .DLL got changed out from under you. That .DLL could do something different than the MS or Office original.

This is a basic rule of system administration - never trust a third-party vendor install to do the right thing. Hell, there are times when Microsoft doesn't know the right thing. How could a vendor EVER get it right? In any case, it's a valid question.

Oh, another issue. You are in Ac2K. What version of Windows are we talking about, both server AND client? If it is WinXP and Service Pack 2 has been installed, you have a REAL issue because of your system's security settings. One biggie that comes to mind is the SP2 of XP resets some of your security settings. If they have added some network overhead in, say, message encryption and decryption by default, then you are working harder than you used to. I think it takes extra system resources to manage such special treatment, too.

 

[david.brent]

Hey The_Doc_Man, thanks for taking the time. My Sys Admin are currently running around their room in wizards outfits, waving their mistletoe staffs, chanting incantations at a server and therefore unavailable for comment.

You do raise some interesting points. This wasn't a clean SMS delivery (the SMS package was built on a machine that wasn't clean so this SMS package has picked up a pile of files it shouldn't have) so dlls may well have been replaced from under me. This seems likely, as I built a totally new client (installed everything from scratch) and the retrieval application runs just fine. I also built a client from an image and that too is fine. The tech guys turned a blind eye.

We have NT4 with service pack 6 I think. We are looking at switching to XP in the new year.

I'll check with my Tech guys and get back to you.

Thanks Again

 

[The_Doc_Man]

NT4? Ye gods and little fishes!

I liked NT4 but it is outdated by now. If your folks don't want to go all the way to XP, at least get to Win2K. Better security and you can still go to the Windows Update site for patches. With NT4, it is off the service list.

If the package for SMS wasn't clean, do they at least know why it was dirty?

I.e. old or mixed files? Viral exposure? Missing files? Why wasn't it clean. 'cause that stuff has a funny way of propagating.

 

[david.brent]

Thanks The_Doc_Man. Looks like it's going to be XP early next year.

Right. The SMS package wasn't clean because a package had been built on it, a very similar package, to update Mdacs? Then a second package was built on the same machine, without the machine being cleansed. The Tech Wizards say it is very possible that some of the dlls involved are the wrong version or currupt. So a friend of mine gave me a utility to check the MDac version and the dlls involved and they are all fine.

The technical wizards are non too corncerned by all of this. So I have requested the scripts of both SMS packages so I can check what is supposed to be on the machines (including version numbers) and take it from there. I'm just a programmer so I'm not sure if the will let me have the scripts because the Tech Guys can rebuild all of the machines by next February???

As a footnote, there is definately some link between the size of the MS Access Database and how long it takes to open. A 50mb database takes about 30 seconds to open, a 100mb takes about 1.5 minutes and a 500mb database takes around 4 minutes.

Thanks for all of you help, I really appreciate it.

 

[The_Doc_Man]

In re-reading this thread again, I noticed something that might be easy to check. Or maybe not...

When the client is opened the first thing it does is begger off to each of these databases and checks what tables are available.

there is definately some link between the size of the MS Access Database and how long it takes to open.

I glossed this over the first time, but let's talk about this step just a little. What does your app try to determine from these tables during the initial processing? Like, table size? Cardinality of the keys? Average record length? And if so, how does it do this?

My thought here is that perhaps the indexes on the back-end tables are not correct. With that many databases and that many records, what you have described is entirely possible if you are attempting to find the table size and the indexes are screwed up. Verify that the table structure has valid prime keys. I.e. check the keys defined for each table and repair each separate BE database. (Yeah, I know, this isn't simple. You might have to stage this repair to do one DB at a time over a period of days or even weeks.) If you do this, assure that you have a good backup copy of each repaired DB. If I read this correctly, your BE DBs are static once the transactions become a matter of history. So a static backup might be good enough to assure no data loss.

If any table does not have a good key, then your symptoms tell the story. Something you are doing at startup time is forcing a "relation scan" - i.e. reading every record from start to finish in order to count or otherwise evaluate all records. With a primary key, faster methods are possible for at least some table property queries - like number of valid records.

Also, if you don't really need to know the number of records right away, don't ask for this information. Use alternate methods of verifying table existence. Like, maybe, read the TableDefs collection for each DB in turn. Validate your tables based only on the content of the TableDefs. The Access Help Files are pretty good in describing table properties. This will require some VBA code, but it is possible to restrict the amount of data to be "touched" during a startup. I'm sure that some of your problem comes from the scope of your startup validation.

The client is on the local PC and the back end is on a server.

Is the server acting as a FILE server? or is it a DB server with the BE format being ORACLE, SQL Server, MySQL, etc.? Having a bad index on a backend DB server won't kill you with an Access FE, the backend will just do big-time relation scans and never complain. (Rather, its slowness is the voice in the wilderness crying for help.)

 

[david.brent]

Hello again The_Doc_Man. Thanks for persuing this.

The app uses the tabledef collection to determine what tables exist in the database. The tables themselves aren't opened until a retrieval is performed.

As far as I can tell, the problem is a database level. The BE databases are MS Access. I am moving over to Database Admin in a couple of weeks and I'm going to rewrite the front end and move the backend to SQL Server 2000. I have never liked the number of users (100s) using the BE databses but it has worked fine up until now. If I double click on any of the BE databases from explorer they take 10 minutes to open but only the first time in that day. Any subseqent 'openings' are instant and that's what I don't get. It's like the PC doesn't remember how to do it so tries everything. Then it cracks it and thinks 'best remeber how to do that next time'.

The tables are completely static, as you say they are historical. They seem to have good indexes and the retrieval run quickly. I have tried compacting and repairing, creating a new database and impoting all of the tables (i think this recreates the indexes). I tried moving to a new server but still no joy at all.

The relation scan thought is very interesting and might well explain why bigger databases take longer to open. I know that this problem is causing the server to be extremely active. Do you know what task sAccess performs during the opening of the database?

Thanks for your help, time and patience on this, I really appreciate it.

 

[The_Doc_Man]

I have tried compacting and repairing, creating a new database and impoting all of the tables (i think this recreates the indexes).

Yes, it does.

I have never liked the number of users (100s) using the BE databses but it has worked fine up until now.

HUNDREDS of Access users in the same DB? If you have even 10% of those hundreds logged in simultaneously, that should cause Access to have major conniption fits. The .LDB file gets AWFULLY complex when you have big bunches of users. I concur with the switch to SQL Server if that's what you have.

Do you know what task sAccess performs during the opening of the database?

Not specifically. However, if I had to guess, I would need to validate my internal pointers to assure that they didn't run off the edge of the DB file. Whether it also checks all pointers for actually pointing to a record header (internal Access data structure), I don't know for sure. For big enough tables, this would be a significant hit. But beyond that, I couldn't say.

However, there is another thing to consider, and it is a startup issue. A WINDOWS LAUNCH issue, not specific to Access: How big is the swap file on each system having a FE?

One of the things that every Office task tries to do is adjust its size to match the size of the file in which it has to muck about. This means allocation and potential shuffling of your swap files. Windows allows swap files to be dynamically allocated and non-contiguous. But if the drives for the FE systems are badly fragmented, it might take forever for the swap file allocation to occur, and it would be as badly fragmented as the disk on which it resided. How often do you defrag the disks on which the FE resides? OR on which the swap file resides, if different. I don't think Access has to allocate as much space as the total database size, but they probably DO have to allocate enough space to manage the pointers, which WOULD be proportional to DB size. Believe it or not, bigger records helps here because it affects pointer efficiency. And that could indirectly be a bigger hit on a badly fragged disk with lots of short records.

Try cleaning obsolete files and defragging one of the disks hosting an FE. See if that system runs significantly faster.