Access 2002 vs Access 97 Performance

[Rob]

Scenario:

O/S: Win XP Professional
Back-end: Access 2002 on network server

I have an Access 97 application, in production on our network, that
takes appoximately 5 minutes to process monthly data. I find this
tolerable.

The Access 2002 test version, on the network, takes approximately 50
minutes to accomplish the same thing. I find this intolerable.

I copied the Access 2002 version's front and back-ends to my C: drive
and it took approximately 4 minutes to process the data.

Why would the network version of Access 97 take 5 minutes versus 50
minutes for Access 2002? Why would the network version of Access 2002
take 50 minutes versus 4 minutes for the local version of Access 2002?

There appears to be two issues here.
1. Access 97 performs a lot better over my existing network than
Access 2002 for some unknown reason.
2. There is a network issue with Access 2002 compared to the local
version of Access 2002, although I don't know what the root cause
might be.

We are converting all of our existing Access 97 databases to Access
2002. I've just started to accomplish this and I am very concerned
about the poor performance issues that I am seeing. Am I going to
always see a performance hit when converting databases to Access 2002?

I've tried compacting the database, with no improvement. I've looked
at other postings regarding performance issues and most of the things
mentioned I've looked into or don't pertain to this application.

Any ideas would certainly be appreciated.

Thanks!

[Albert D. Kallal]

I would have loved to see the numbers for access 97 local also?

That way, you could eliminate the network...and see if a2002 can perform ok?
By not testing access97 locally, then you don't know if a97 is slower, or
faster then a2002 on your local system? You see, if a2002 performs the same
as a97 local.....then likely you can return a2002 performance back to a97
levels. Perhaps a97 takes only 1 minute locally?

There are few things about a2002 that will run slower (on the other hand, I
been playing with computers for 20 years. Even when I had my first appleII,
I can't EVER remember purchasing new software that did not take more disk
space, more memory, simply required more resources? (can you think of ANY
software you EVER purchased that ran better, and faster then the previous
version..and used less of anything? I can't think of anything in the last 20
years that has broke this experience of mine!). Perhaps you are new to
computers, or never purchased or used new software?

However, there are a few things to check. (in fact, quite a few..since a2002
is 2 versions later then a97).

First, queries with User defined functions can run a LOT slower. Try and
avoid any VB function in a query. (this is a change in a2000 and later).

The world is now a big place..and acess2000 and later uses uni-code to
support international languages (text). That means for each character stored
in the database...two characters are stored. All of office when to uni-code
in office 2000, with the exception of Outlook..which finally made the change
for a2003.

This means that as a general rule...files are larger in a2000 and later
(larger files = larger load on system).

The other thing to check/change is things link track name auto correct (you
REALLY want to turn this feature off!), and also try the trick of keeping
the back end opened during this processing loop. So, when you load the front
end...simply open a connection to some table in the back end..and KEEP this
connection open. I would also test as a mde....

Now run your stuff..it should run on par with a97.

The list of performance got-ya's..and stuff like the "keep connection" open
trick and how to get your performance back can be found here:

http://www.granite.ab.ca/access/performancefaq.htm
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[David W. Fenton]

ro***********@mpls.frb.org (Rob) wrote in
news:9b**************************@posting.google.c om:

Why would the network version of Access 97 take 5 minutes versus
50 minutes for Access 2002? Why would the network version of
Access 2002 take 50 minutes versus 4 minutes for the local version
of Access 2002?

Did you delete the table links in the A2K2 front end and recreate
them afresh?

Were you running the A2K2 version against an A2K2/A2K back end, or
the same A97 back end?

Did you set the SubDatasheet property of your tables to [NONE]?

When you converted, did you make sure that no excess references were
added?

Was your conversion a conversion, or did you import the A97 objects
into a newly-created A2K2 MDB?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Albert D. Kallal" <Pl*******************@msn.com> wrote in
news:05Nqc.506825$Pk3.289512@pd7tw1no:

also try the trick of keeping
the back end opened during this processing loop. So, when you load
the front end...simply open a connection to some table in the back
end..and KEEP this connection open.

That makes absolutely no sense for a process that's running, unless
that process is repeatedly resetting the db variable and recreating
it.

Actually, now that you mention it, perhaps that's what's happening.

And, BTW, given that the slowdown that this trick is supposed to fix
is caused by the creation/deletion/creation of the LDB file, all you
really have to do is initialize a reference to the back end database
-- you don't actually have to open a recordset.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tony Toews]

"David W. Fenton" <dX********@bway.net.invalid> wrote:

Did you set the SubDatasheet property of your tables to [NONE]?

I suspect this would make the biggest difference.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Rob]

"Albert D. Kallal" <Pl*******************@msn.com> wrote in message news:<05Nqc.506825$Pk3.289512@pd7tw1no>...

I would have loved to see the numbers for access 97 local also?

That way, you could eliminate the network...and see if a2002 can perform ok?
By not testing access97 locally, then you don't know if a97 is slower, or
faster then a2002 on your local system? You see, if a2002 performs the same
as a97 local.....then likely you can return a2002 performance back to a97
levels. Perhaps a97 takes only 1 minute locally?

There are few things about a2002 that will run slower (on the other hand, I
been playing with computers for 20 years. Even when I had my first appleII,
I can't EVER remember purchasing new software that did not take more disk
space, more memory, simply required more resources? (can you think of ANY
software you EVER purchased that ran better, and faster then the previous
version..and used less of anything? I can't think of anything in the last 20
years that has broke this experience of mine!). Perhaps you are new to
computers, or never purchased or used new software?

However, there are a few things to check. (in fact, quite a few..since a2002
is 2 versions later then a97).

First, queries with User defined functions can run a LOT slower. Try and
avoid any VB function in a query. (this is a change in a2000 and later).

The world is now a big place..and acess2000 and later uses uni-code to
support international languages (text). That means for each character stored
in the database...two characters are stored. All of office when to uni-code
in office 2000, with the exception of Outlook..which finally made the change
for a2003.

This means that as a general rule...files are larger in a2000 and later
(larger files = larger load on system).

The other thing to check/change is things link track name auto correct (you
REALLY want to turn this feature off!), and also try the trick of keeping
the back end opened during this processing loop. So, when you load the front
end...simply open a connection to some table in the back end..and KEEP this
connection open. I would also test as a mde....

Now run your stuff..it should run on par with a97.

The list of performance got-ya's..and stuff like the "keep connection" open
trick and how to get your performance back can be found here:

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

Albert,

Thanks for the tips.

On your link, you mention: 'sub datasheet Name property set to [Auto]
should be [None]' I'm new to Access 2002 and not familiar with how to
set this. Can you point me in the correct direction?

Thanks again.

[Tony Toews]

ro***********@mpls.frb.org (Rob) wrote:

On your link, you mention: 'sub datasheet Name property set to [Auto]
should be [None]' I'm new to Access 2002 and not familiar with how to
set this. Can you point me in the correct direction?

If you go down that page a bit you'll see a section titled "Sub Datasheet Name
property set to [Auto]" with a link to a Microsoft KB article with the necessary
code.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[david epsom dot com dot au]

> And, BTW, given that the slowdown that this trick is supposed to fix

is caused by the creation/deletion/creation of the LDB file, all you
FWIW, there are two separate problems that some times occur:
1) LDB file problem (common)
2) Network speed problem. (uncommon)

LDB file create/deletion problem is 'solved' by keeping a db connection
open.

However, on slow networks (or of course on very badly designed code)
you can sometimes see the overhead of just loading/locking tables,
and if this is the case, opening all of your tables can help.

Of course it also adds lot's of locks, which can cause other problems.

(david)
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.78... "Albert D. Kallal" <Pl*******************@msn.com> wrote in
news:05Nqc.506825$Pk3.289512@pd7tw1no:

also try the trick of keeping
the back end opened during this processing loop. So, when you load
the front end...simply open a connection to some table in the back
end..and KEEP this connection open.

That makes absolutely no sense for a process that's running, unless
that process is repeatedly resetting the db variable and recreating
it.

Actually, now that you mention it, perhaps that's what's happening.

And, BTW, given that the slowdown that this trick is supposed to fix
is caused by the creation/deletion/creation of the LDB file, all you
really have to do is initialize a reference to the back end database
-- you don't actually have to open a recordset.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Rob]

One other thing you might try, that I have found EXTREMELY relevant
when moving from 97 to either 2000 or 2002.

2000 and 2002 by default have the option "Track Name autocorrect info"
set to ON. (Under Tools/Options/General)

Access 97 did not have this option. It COULD be that your 2002
databases have this set to ON which CAN degrade performance
significantly, depending on how much manipulation your app is doing to
queries and form recordsets.

A particular application I had went from a 10 second opening running
Access 97 on a pentium II 266MHz to a 1-2 minute opening running
Access 2000 on a Pentium III when all we did was convert the Access97
application to Access 2002.

Try it and see if it impacts.
Good luck,
Rob

[David W. Fenton]

si****@ensco.com (Rob) wrote in
news:bd*************************@posting.google.co m:

A particular application I had went from a 10 second opening
running Access 97 on a pentium II 266MHz to a 1-2 minute opening
running Access 2000 on a Pentium III when all we did was convert
the Access97 application to Access 2002.

My first A2K app did this when moved from the development to the
production environment. Michael Kaplan suggested deleting and
recreating the table links in the production environment (instead of
updating the CONNECT string on the table links), and this corrected
the problem. Michael explained that A2K caches more information
about the back end in table links than previous versions of Access,
and this data is not updated when the CONNECT string is changed,
resulting in non-optimal performance.

So, your situation might have been related.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc