Split DB Poor Performance?

[GUIDO22]

I have split my database application that was approaching the 20MB size. This I have split into a front end (approx. 8 Mb) with linked tables to a back end database (approx. 12MB).

Network is 100Mb Ethernet.

However, since doing this, end users have noticed that scrolling through records and especially running reports takes significantly longer sometimes 3x/ 4x longer. I understood that splitting the DB would have a beneficial effect from a development / application 'release' point of view and maybe if I were to create an MDE file of the front end, I could also benefit from reduced network traffic given that end users are using a compiled executable etc.

With the speed issues I have been experiencing I have had no choice but to roll back to the original application format with everything in the the one MDB file.

Has anyone else had to do the same - given similar speed degradation issues?

Thanks
Guido

 

[RuralGuy]

Here's a couple of links that are worth viewing:

Split your MDB file into data and application

Microsoft Access Performance FAQ

 

[GUIDO22]

Allen Brownes article is what encouraged me to split the DB in the first instance! As mentioned - network traffic seemed worse and report loading time etc. was significantly increased.

Will try the decompile option - fingers crossed...

 

[The_Doc_Man]

Question: Is the FE on the same machine as the BE?

Second question: Are the FE and BE files in folders with lots of other files - and by "lots" I specifically mean >250?

Next question: Are the FE and BE files in folders nested at least 5 layers deep for which each folder above them has many entries in the folder security list? (Access Control List, if you are familiar with NTFS terms.)

Next question: Can you run a performance monitor to see if you are thrashing your swap file, charge-commit numbers, or free memory? By thrashing, I mean "causing large swings in the particular numbers, >20% of the total from high to low."

All of these are bad things.

 

[GUIDO22]

Question: Is the FE on the same machine as the BE?

Yes - in the same folder.

Second question: Are the FE and BE files in folders with lots of other files - and by "lots" I specifically mean >250?

No

Next question: Are the FE and BE files in folders nested at least 5 layers deep for which each folder above them has many entries in the folder security list? (Access Control List, if you are familiar with NTFS terms.)

4 layers deep from the root directory - Access control is only specified on the 4 folder which is a share. Access control for 3 usergroups only

Next question: Can you run a performance monitor to see if you are thrashing your swap file, charge-commit numbers, or free memory? By thrashing, I mean "causing large swings in the particular numbers, >20% of the total from high to low."

Will give this a try.

 

[workmad3]

I just did a split on my access db(not rolled it out yet though), and I noticed poor performance if I used linked tables. As most of my forms were using ADODB connections, I merely changed them to link straight to the db file and got the same performance as before(a couple of forms rely on linked tables still )

Does anyone know how I can get access queries to rely on a connection to an external db rather than on linked tables though? This would then mean I can have my frontend relying entirely on ADODB and makes changing the backend a lot easier(if necesarry. Thinking of marketing the DB at the moment, and I think a higher spec backend wopuld be more useful if it wont take much effort to switch)