Access 97 to a more powerfull platform!?

[Guest]

Can someone point me in the direction of a web site or news group that might
contain usefull information for someone who has several Access97 projects
that are growing beyong their capacity and need to be converted to SQL or
some version of Access beyond 97 that might improve the applications
performance?? I am especially interested in any tools that might assist in a
conversion and the benifits of subsequent versions of Access (2002??)

I am waist deep in this problem and am going to have to find a solution
rather quickly!

Thanks

Rick

 

[Rick Brandt]

Can someone point me in the direction of a web site or news group that might
contain usefull information for someone who has several Access97 projects
that are growing beyong their capacity and need to be converted to SQL or
some version of Access beyond 97 that might improve the applications
performance?? I am especially interested in any tools that might assist in a
conversion and the benifits of subsequent versions of Access (2002??)

I am waist deep in this problem and am going to have to find a solution
rather quickly!

It is very unlikely that moving to a different version of Access will
improve performance (more likely it will be worse). As with most software
the newer versions of Office consume more system resources and disk space
so when run on identical hardware typically perform worse than older
versions.

Moving to SQL Server (or similar) is common when a database "outgrows"
Access/Jet, but this usually gains advantages in concurrency, volume (size
capability), and security, not really performance per se.

Design problems are far and away the usual reason why a database
application performs poorly.

 

[Rick B]

You might want to share how large your file is, how many records, how many
users, if the users are all in one place, if you are using
front-end/back-end, etc.

 

[Guest]

The file is 118,000 KB, I think I mentioned but in case I did not I will
repeat that it is access 97, there are just over 100,000 records with over
200 fields. I know that this sounds like very poor design and due to
faactors out of my control there was little else that could have been done.

Currently all of the forms, reports, tables, queries, EVERYTHING is in one
..mdb . I have tried splitting front end to back end, running as an MDE and
other options all causing WORSE performance. There are maybe 6 users at any
given time tops.

Rick

 

[Rick B]

This is not terribly large for Access. Your design is the problem, not the
database engine. You need to normalize your data and split your database.

Rick B

 

[Rick Brandt]

The file is 118,000 KB, I think I mentioned but in case I did not I will
repeat that it is access 97, [snip]

And I will repeat that when run on the same hardware Access 97 is likely to
be faster than any of the newer versions running the same application.
Your Access version is NOT the reason you have poor performance. I have
97, 2000, 2002, and 2003 all on my machine and only use the newer ones for
conversions to support users who have those versions. There are likely
circumstances where a newer version solves a problem or has advantages, but
"speed" is not going to be one of them.

We also see posters frequently who complain about their application going
slower when they move the data to SQL Server. Again, there are advantages
to a server-driven database engine, but raw performance is seldom one of
them until you get into "really big" databases and yours is not even above
average.

 

[Immanuel Sibero]

Hi

In addition to Rick B's comment:

200 fields. I know that this sounds like very poor design and due to
faactors out of my control there was little else that could have been don

Good design and normalization are so crucial regardless of whether it's file
based (Access) or client server (SQL). Moving up to SQL server would
probably not solve performance problems caused by design flaws and/or lack
of normalization.

If you havent already, have a look at:
http://www.granite.ab.ca/access/performancefaq.htm

Great information on performance.

HTH,
Immanuel Sibero

 

[Guest]

Thanks! You have all been very helpfull. I will look at the normalization
stuff, this is a very "tough" application in that it is not a problem for
which a database can be a very elegent solution. It is simply a list of
documents that have to have a bunch of possible events that have to be
tracked, and any of them could have any or all possible fields used, while
few of them actually use many. My hands were tied in alot of it. There is
only one possible key and other than some look ups this 100,000 + record
table with 200+ fields doesnt have and relationships with any other tables.

I really really appreciate the feed back, at least itsaved me the
possibility of investing alot of time and effort converting it to something
that was no help at all. I appreciate the reference to the article, I will
read it and hopefully get more of an understanding of what I can do to speed
this thing up.

I guess I thought it was large because it is the largest I have ever worked
with, most have been significantly smaller.

Again, thanks for all of the help.

Rick

 

[John Vinson]

Thanks! You have all been very helpfull. I will look at the normalization
stuff, this is a very "tough" application in that it is not a problem for
which a database can be a very elegent solution. It is simply a list of
documents that have to have a bunch of possible events that have to be
tracked, and any of them could have any or all possible fields used, while
few of them actually use many. My hands were tied in alot of it. There is
only one possible key and other than some look ups this 100,000 + record
table with 200+ fields doesnt have and relationships with any other tables.

Just FWIW, your wide table is VERY MUCH part of the problem. A
normalized approach would have three tables not one: perhaps

Documents
DocumentID
DocumentTitle
<fields about the document as an entity, e.g. AuthorID>

Events
EventID
Description

DocumentEvents
DocumentID <<< which document
EventID <<< what type of event
EventDate
<any other information about this event for this document>

Rather than 100 fields being filled in in your spreadsheet, you would
have 100 RECORDS in the DocumentEvents table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Brendan Reynolds]

The bottom line, Rick, is that if the poor design is outside your control,
then the poor performance is also largely outside your control.

One thing that you can do is to check the indexes. Make sure that any fields
used for filtering or sorting are indexed. If you frequently filter or sort
on a combination of two or more fields, try creating compound indexes on
those fields.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

I have worked with Access 2000 databases containing well over a million rows
and the performance has been acceptable if not spectacular, so your volume is
not the problem.

 

[Guest]

This is interesting because EVERYONE involved is convinced that the end all
solution would be to move the thing to SQL, someone else suggested moving to
a newer version of Access might get us closer to a SQL like environment and
therefore speed things up. The only thing stopping them is of course the
cost and likely the learning curve of converting queryies to stored
procedures and so forth (I know nothing about doing this, I just have been
told that this is one of the things that would have to be done).

I know as a simple test an upsizing tool of some sort was at one point
applied to a copy of the database and the results were pretty much a wash
performance wise. There was something significant, I forget exactly which is
was but it was either faster loading the form connected to the big chunk of
data but slower moving from record to record or the reverse. I am looking
through the stuff recommended trying some things here and there. Being that
the data is pretty flat and queried primarily on a single key, a number that
references each document multiple keys are doing little good.

Every document has a document number. Certain document types have seperate
specific events associated with them. Important to nte I am note saving the
document itself but basically using the DB to track events specific to that
document.

It is a mess but the folks in this thread have given me an amazing amount of
leads to solutions and I really do appreciate that!

 

[Rick Brandt]

This is interesting because EVERYONE involved is convinced that the end all
solution would be to move the thing to SQL, [snip]

EVERYONE involved is wrong.

someone else suggested moving to
a newer version of Access might get us closer to a SQL like environment and
therefore speed things up. [snip]

The newer versions offer the chooice of using an ADP which is more tightly
integrated with SQL Server, but those *can only* be used with SQL Server
and appear likely to be dropped in future versions due to problems and lack
of interest.

The only thing stopping them is of course the
cost and likely the learning curve of converting queryies to stored
procedures and so forth (I know nothing about doing this, I just have been
told that this is one of the things that would have to be done).

There are (generally) redesigns that take place when moving to a server
back end hopefully involving people who are familiar enough with the server
engine chosen that result in significant performance benefits. However it
is the portion of the redesign that fixes everything that is WRONG with the
existing design that yield the vast majority of the improvement. Most of
those same improvements could have been applied to the Access/Jet
application as well.

If you went to SQL Server and hired a competent SQL Server admin to set
this up for you, he would most assuredly take one look at it and say that
the table structure needs to be redone. After having done that then
everyone would notice how much faster everything is after moving the data
to SQL Server when it was actually the table redesign that yielded the
benefits.