Very slow MDE & MDB response... suggestions?

[Gary]

Hello,

We have an Access application (written by someone else who is no longer
here) for our Purchase Orders. The file was custom written in Access 2000
(not a template) and generally network users use the MDE file, which is
periodically replaced to prevent getting too large, etc.

The program is fairly simple, but after the main menu loads and the user
presses a button to open the form with to enter a new PO, the process runs
EXTREMELY slow! Sometimes 30 to 40 seconds pass. There are about 500 or so
records in the database. Then the form opens, and data entry proceeds at a
fairly normal pace.

I pondered updating it to SQL, but a couple of things held me back. Why with
so few records is it still so slow? And we are going to be ugrading all our
accounting systems soon to a new system, which has a PO program running on
SQL already. It's just the actual timeframe of implementation is hazy and
uncertain.

My question really is: Can I do anything to speed the file up? I test fired
the MDB file and it is equally slow. I know Jet is a slow protocol- I did
some Net scouring and only one thing seemed to look promissing, changing the
MaxBufferSize in the MSACCESS.INI file. However, there is NO msaccess.ini
file on my computer. I think it's from olden days (perhaps Access 2.0?)

Has anyone come across this before and had a resolution? I had contemplated
building a new custom VB front end to it, but with the changeover to the new
system, it won't be ready in time, I don't think.

Any help or suggestions would be greatly appreciated!

Thanks,
Gary

 

[Albert D. Kallal]

I will say right off the bat that you should be able to get BLINDING
performance with such small data.

I have clients with applications that have 55+ heavily related tables, and
many of those tables in the relations are 40,000 records.

With 5 or 6 users on a typical cheap office network, response time is
ABSOLUTE INSTANT for the above application!

I should also not that his application is used all day, and is used to deal
with customers on the phone. I have no intensions of upgrading this
application to sql sever until the client outgrows this. I figure at last
another 5 to 8 years, and only when tables get up to the 300,000 range will
I even being to consider sql server. Actually, for web issues, I am going to
move this to sql server...but it is NOT for reasons of performance.

I mention the above, as then you can feel better about the possible
improvements.

However, you DO NEED to have a proper setup to get the above kinds of
performance. So, lets look at a few things:

We have an Access application (written by someone else who is no longer
here) for our Purchase Orders. The file was custom written in Access 2000
(not a template) and generally network users use the MDE file

Excellent, users should always get a MDE (good for you!).

, which is
periodically replaced to prevent getting too large, etc.

Hum, now, that is a bit strange. I assume that you give EACH USER their OWN
copy of the mde..right? That means that you have to install the mde
application part on EACH computer. You could setup compact on close to fix
any increase in file size, but with a mde, there should BE VERY LITTLE
growth in the front end.

Thus, this also means that you have a split database (I assume this is the
case, since you are using mde's here...but you also MUST install those mde
on EACH work station).

The program is fairly simple, but after the main menu loads and the user
presses a button to open the form with to enter a new PO, the process runs
EXTREMELY slow! Sometimes 30 to 40 seconds pass. There are about 500 or so
records in the database. Then the form opens, and data entry proceeds at a
fairly normal pace.

Ok, #1 trick here is to force the connection from the FE to the BE to STAY
open. That long delay is the network file system, and a bunch of security
stuff that occurs,a nd thing slow down to a snail pace. As you note, once
the file is open...it runs ok.

What you here is thus in the application startup, is simply open up a table
to the back end. (*any* table in the back end will do here!!). KEEP this
table open, and then the rest of the application will sing in terms of
performance. You can open a small form attached to some table, and miimze
it, or open a table to a recordset in code.

The above trick, and a list of other things to check can be found here:
http://www.granite.ab.ca/access/performancefaq.htm

Try the above trick..and post back how it works...

 

[Gary]

I will say right off the bat that you should be able to get BLINDING
performance with such small data.

I have clients with applications that have 55+ heavily related tables, and
many of those tables in the relations are 40,000 records.

With 5 or 6 users on a typical cheap office network, response time is
ABSOLUTE INSTANT for the above application!

I should also not that his application is used all day, and is used to deal
with customers on the phone. I have no intensions of upgrading this
application to sql sever until the client outgrows this. I figure at last
another 5 to 8 years, and only when tables get up to the 300,000 range will
I even being to consider sql server. Actually, for web issues, I am going to
move this to sql server...but it is NOT for reasons of performance.

I mention the above, as then you can feel better about the possible
improvements.

However, you DO NEED to have a proper setup to get the above kinds of
performance. So, lets look at a few things:


Excellent, users should always get a MDE (good for you!).


Hum, now, that is a bit strange. I assume that you give EACH USER their OWN
copy of the mde..right? That means that you have to install the mde
application part on EACH computer. You could setup compact on close to fix
any increase in file size, but with a mde, there should BE VERY LITTLE
growth in the front end.

Thus, this also means that you have a split database (I assume this is the
case, since you are using mde's here...but you also MUST install those mde
on EACH work station).

Firstly, no the users share the database (or I should say the MDE file).
Again, this is something I picked up from someone else who's gone. And I run
the MDB on my computer which No-one attaches to, and it is slow. I even
copied it to a different locale that only I can access and there is a slow
load when clicking 'new'. The database has been split though, that was done
before I got involved in it.

Ok, #1 trick here is to force the connection from the FE to the BE to STAY
open. That long delay is the network file system, and a bunch of security
stuff that occurs,a nd thing slow down to a snail pace. As you note, once
the file is open...it runs ok.

What you here is thus in the application startup, is simply open up a table
to the back end. (*any* table in the back end will do here!!). KEEP this
table open, and then the rest of the application will sing in terms of
performance. You can open a small form attached to some table, and miimze
it, or open a table to a recordset in code.

I did try this, opening up a non-related table (at least not related to the
actual workings of the PO system) and then clicked 'new'. No speed increase
could be noted. I see the table pop up, the menu appears, and then I click
the button.

The above trick, and a list of other things to check can be found here:
http://www.granite.ab.ca/access/performancefaq.htm

Try the above trick..and post back how it works...

It sounds great (and I printed out the link to review that). I can't really
reoptimize the code, because I don't know what code is for what. The person
who wrote it could be incredibly sloppy (misspellings galore and no
development notes). Now, I get left holding the bag until we upgrade. All
the new stuff will be SQL based, because the higher-ups finally decided we
needed to have a single, unified system. I say hear, hear! But I still have
to deal with this problem as best I can. Do you think that each user having
an MDE copy locally will improve the performance? I wonder... since I was
having trouble running the MDB by myself. I have compacted and repaired it a
couple of times, but it doesn't dramatically improve anything.

Thanks!

Gary