If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Redesign slow database or archive records ?
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. |
#2
|
|||
|
|||
Redesign slow database or archive records ?
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 "mscertified" wrote in message ... 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. |
#3
|
|||
|
|||
Redesign slow database or archive records ?
I've tried to answer your questions below.
"Jeff Boyce" wrote: 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. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "mscertified" wrote in message ... 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. |
#4
|
|||
|
|||
Redesign slow database or archive records ?
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" wrote in message ... I've tried to answer your questions below. "Jeff Boyce" wrote: 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. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "mscertified" wrote in message ... 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. |
#5
|
|||
|
|||
Redesign slow database or archive records ?
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" wrote: 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" wrote in message ... I've tried to answer your questions below. "Jeff Boyce" wrote: 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. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "mscertified" wrote in message ... 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. |
#6
|
|||
|
|||
Redesign slow database or archive records ?
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 "Dorian" wrote in message ... 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" wrote: 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" wrote in message ... I've tried to answer your questions below. "Jeff Boyce" wrote: 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. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "mscertified" wrote in message ... 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. |
#7
|
|||
|
|||
Redesign slow database or archive records ?
On Thu, 20 Nov 2008 10:51:45 -0800, "Jeff Boyce"
wrote: Is the network a LAN or a WAN? What's the network OS? Wan (Terminal server) 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 |
Thread Tools | |
Display Modes | |
|
|