Redesign slow database or archive records ?

[mscertified]

I'm maintaining an database that I did not create. It basically tracks
issues. Periodically it gets slow and the users ask me to compress it.
Compressing now does not help much and they are asking for 2000 old issues to
be archived (but they want possibility to retrieve them if needed). I
examined the database and about half of the storage is taken up by various
comments held in memo columns.
This is Access 2003, split database and there are currently 6310 Issues with
26332 comments. The Issue table has 11 related tables which also contain
1000's of records including more comments. Total database size is 85 meg with
the comments alone taking up about 35 meg.
Any ideas on how to redesign this database regarding the comments or to
provide a convenient archiving facility.

Thanks for any suggestions.

 

[Jeff Boyce]

I don't really feel like I understand enough about your current
design/structure to offer suggestions on changing it.

Have you checked in the Relationships window to see if the tables have been
related to each other?

Do you have indexes on any fields that are being used for selection
criteria, sorting or grouping?

How many folks are (simultaneously) using the db? For data-entry? For
"lookup"?

Is the network a LAN or a WAN? What's the network OS?

How much "horsepower" do your PCs have?

Is the front-end copied to each desktop PC, or are folks sharing a single
copy on the network?

This is a start of places to look... (there's more!)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dorian]

I've tried to answer your questions below.

I don't really feel like I understand enough about your current
design/structure to offer suggestions on changing it.

I'm not sure I do either!

Have you checked in the Relationships window to see if the tables have been
related to each other?

All are related but not all related columns are indexed. Seems like the one
side of one-to-many relationships are not indexed for the most part.

Do you have indexes on any fields that are being used for selection
criteria, sorting or grouping?

Some but probably not all.

How many folks are (simultaneously) using the db? For data-entry? For
"lookup"?
Three

Is the network a LAN or a WAN? What's the network OS?

Wan (Terminal server)

How much "horsepower" do your PCs have?

Good question, no idea. All users are Virtual Office and have 'black boxes'.

Is the front-end copied to each desktop PC, or are folks sharing a single
copy on the network?

Not sure about that. Does it make a big difference?

This is a start of places to look... (there's more!)

Users say slowness is most apparent on:
Initial database entry.
Searching for text (this app allows text search in currently selected record
and related records in other tables)
Opening an individual record (from the search screen)

The initial screen is a search screen that lists records in a listbox, there
is a default criteria so all records in db are not shown.
Query that loads listbox has eight joins in it but seems to run pretty quick
when run alone.
Search screen contains about a dozen combo boxes for setting search criteria.
A lot of work has been done on this app already, for instance all rowsources
are set on load rather than in the form definition.

 

[Jeff Boyce]

Check on-line for Albert Kallal's discussion of why you DO NOT want to use a
Wide Area Network to run an Access application.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dorian]

Have already read that in the past - but there is no alternative. These
systems were built eons ago when people worked in the office - now many
people work at home full-time. To rebuld this as a web application would take
at least 6 months and we already have an endless queue of work backed up for
our attention. I guess we'll end up archiving some records.

 

[Jeff Boyce]

Dorian

I don't know if you've already considered this approach, so I'll offer it
....

In some instances, the notion of "archiving" means physically-removing
records and putting them somewhere else. Naturally, this causes problems
when the record you want to look over has been archived. This is true for
both physical and database implementations.

As an alternate approach, consider adding a field to the record for
[DateArchived]. Then modify the query that returns records for the form to
only show records without a value in that field. The data is still in the
same table, but you have to create other queries to retrieve the archived
data.

Another speed-up technique might be to only return a SINGLE record by having
the user first select which record (via an unbound combobox), then having
Access return only that record to the form.

Finally, check on-line for suggestions made by Arvin Meyer (and others) for
using Citrix or Terminal Services or some other variation.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Armen Stein]

It seems like part of the answer was that it was Terminal Server
running on a WAN. If that's true, it isn't really running on a WAN.

Then the performance issues go back to the horsepower of the Terminal
Server box, how many people are sharing the application, how well the
tables are indexed, how efficiently the forms are built, etc.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com