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
|
|||
|
|||
do I need more indexes?
After reading several posts here I have changed all of the lookup fields to
text fields in my table and deleted the relationships to the lookup tables. I came to this area looking for a solution to slow response time. Again, after reading several posts, I suspect it is due to the database design, but it is what I have to work with. Other than the lookup tables, there are only two related tables. If it would be helpful, I can remove the lookup tables. tSample (with the major fields listed below) Key (primary key) CNTYNAME ID Strata Sample tReview ReviewKey (PK) Key (FK) A1 A2. . . B1 B2. . .(in total there are over 60 number fields and 50 memo fields). I read that creating indexes will increase response time. Will creating a unique index from two fields in addition to the PK increase response time (e.g., in tSample, a unique key comprised of CNTYNAME and ID)? All was going well for the individual county reports, but I had problems when an aggregate report was needed at year end. I was able to solve the problem with advice obtained through this site. First, I created a union query (qUnion); a crosstab query based on the union query (qUnionCrosstab); and combined qUnionCrosstab and qReviewCountCrosstab into one query (qCrosstabsCombined) for the aggregate report. The aggregate report is extremely slow to open and painfully slow in design view. Will it help to create additional indexes? If not, I can live with this report as it is since I only need it once a year. I don’t know how to change the design and transfer the data. Thanks for any tips for improvement. I am very grateful for the great information I have found here. |
#2
|
|||
|
|||
do I need more indexes?
I doubt that indexes will help much. How many records are we talking about?
If less than a thousand, indexes usually don't help anyway. In fact you might have too many indexes already. Access automatically creates indexes for fields with ID; key; code; num as part of the name. I noticed that you have ReviewKey (PK) and Key (PK). If you check you might find that there are at least 2 indexes on these fields: one for the PK and the other by just having Key in the field name. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jmoore" wrote: After reading several posts here I have changed all of the lookup fields to text fields in my table and deleted the relationships to the lookup tables. I came to this area looking for a solution to slow response time. Again, after reading several posts, I suspect it is due to the database design, but it is what I have to work with. Other than the lookup tables, there are only two related tables. If it would be helpful, I can remove the lookup tables. tSample (with the major fields listed below) Key (primary key) CNTYNAME ID Strata Sample tReview ReviewKey (PK) Key (FK) A1 A2. . . B1 B2. . .(in total there are over 60 number fields and 50 memo fields). I read that creating indexes will increase response time. Will creating a unique index from two fields in addition to the PK increase response time (e.g., in tSample, a unique key comprised of CNTYNAME and ID)? All was going well for the individual county reports, but I had problems when an aggregate report was needed at year end. I was able to solve the problem with advice obtained through this site. First, I created a union query (qUnion); a crosstab query based on the union query (qUnionCrosstab); and combined qUnionCrosstab and qReviewCountCrosstab into one query (qCrosstabsCombined) for the aggregate report. The aggregate report is extremely slow to open and painfully slow in design view. Will it help to create additional indexes? If not, I can live with this report as it is since I only need it once a year. I don’t know how to change the design and transfer the data. Thanks for any tips for improvement. I am very grateful for the great information I have found here. |
#3
|
|||
|
|||
do I need more indexes?
Define "response time" ...
If you mean "how long it takes to add a new record in", then yes, when Access has to add the new data AND an index, it takes longer. If you don't have a high-volume data-entry business, you won't notice how much longer. If you mean "how long it takes to retrieve data via a query", then no, a properly-indexed set of tables will generate results faster. A general rule of thumb (i.e., not set in stone) is to index any field that is used as a selection criterion, as a join, or as a sort. Of course, if you only have two possible values (e.g., Male, Female), the index really won't help. Slow to open is one thing ... slow to open in design view may be something else. Do you have a printer "assigned" to the report? Regards Jeff Boyce Microsoft Office/Access MVP "jmoore" wrote in message ... After reading several posts here I have changed all of the lookup fields to text fields in my table and deleted the relationships to the lookup tables. I came to this area looking for a solution to slow response time. Again, after reading several posts, I suspect it is due to the database design, but it is what I have to work with. Other than the lookup tables, there are only two related tables. If it would be helpful, I can remove the lookup tables. tSample (with the major fields listed below) Key (primary key) CNTYNAME ID Strata Sample tReview ReviewKey (PK) Key (FK) A1 A2. . . B1 B2. . .(in total there are over 60 number fields and 50 memo fields). I read that creating indexes will increase response time. Will creating a unique index from two fields in addition to the PK increase response time (e.g., in tSample, a unique key comprised of CNTYNAME and ID)? All was going well for the individual county reports, but I had problems when an aggregate report was needed at year end. I was able to solve the problem with advice obtained through this site. First, I created a union query (qUnion); a crosstab query based on the union query (qUnionCrosstab); and combined qUnionCrosstab and qReviewCountCrosstab into one query (qCrosstabsCombined) for the aggregate report. The aggregate report is extremely slow to open and painfully slow in design view. Will it help to create additional indexes? If not, I can live with this report as it is since I only need it once a year. I don't know how to change the design and transfer the data. Thanks for any tips for improvement. I am very grateful for the great information I have found here. |
#4
|
|||
|
|||
do I need more indexes?
There are currently 1246 records. And it will be double that within a year.
tSample had an index only for Key - the PK. I added a muli-field index using CNTYNAMe and ID. tReview has an index for ReviewKey - the PK and for Key - the FK. I added the multi-field key because I read that any field that is used to sort should have an index. Should I remove it? Thanks much. "Jerry Whittle" wrote: I doubt that indexes will help much. How many records are we talking about? If less than a thousand, indexes usually don't help anyway. In fact you might have too many indexes already. Access automatically creates indexes for fields with ID; key; code; num as part of the name. I noticed that you have ReviewKey (PK) and Key (PK). If you check you might find that there are at least 2 indexes on these fields: one for the PK and the other by just having Key in the field name. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jmoore" wrote: After reading several posts here I have changed all of the lookup fields to text fields in my table and deleted the relationships to the lookup tables. I came to this area looking for a solution to slow response time. Again, after reading several posts, I suspect it is due to the database design, but it is what I have to work with. Other than the lookup tables, there are only two related tables. If it would be helpful, I can remove the lookup tables. tSample (with the major fields listed below) Key (primary key) CNTYNAME ID Strata Sample tReview ReviewKey (PK) Key (FK) A1 A2. . . B1 B2. . .(in total there are over 60 number fields and 50 memo fields). I read that creating indexes will increase response time. Will creating a unique index from two fields in addition to the PK increase response time (e.g., in tSample, a unique key comprised of CNTYNAME and ID)? All was going well for the individual county reports, but I had problems when an aggregate report was needed at year end. I was able to solve the problem with advice obtained through this site. First, I created a union query (qUnion); a crosstab query based on the union query (qUnionCrosstab); and combined qUnionCrosstab and qReviewCountCrosstab into one query (qCrosstabsCombined) for the aggregate report. The aggregate report is extremely slow to open and painfully slow in design view. Will it help to create additional indexes? If not, I can live with this report as it is since I only need it once a year. I don’t know how to change the design and transfer the data. Thanks for any tips for improvement. I am very grateful for the great information I have found here. |
#5
|
|||
|
|||
do I need more indexes?
Some of my queries are slow to open. The object that takes the longest time
just to open is the aggregate report. And when trying to make changes in design view, there is a long delay after clicking on a control and when it becomes active. The same with trying to move a control - very, very slow. I don't know about adding a new record yet as I have to make some changes before the next batch of information comes through and I thought I would try to address this problem at the same time. your general rule of thumb about indexes is what I read (sorry, don't remember which post) and is why I added another index. However, it does not appear to help. The printer is set to my default printer. Thanks again for all help. "Jeff Boyce" wrote: Define "response time" ... If you mean "how long it takes to add a new record in", then yes, when Access has to add the new data AND an index, it takes longer. If you don't have a high-volume data-entry business, you won't notice how much longer. If you mean "how long it takes to retrieve data via a query", then no, a properly-indexed set of tables will generate results faster. A general rule of thumb (i.e., not set in stone) is to index any field that is used as a selection criterion, as a join, or as a sort. Of course, if you only have two possible values (e.g., Male, Female), the index really won't help. Slow to open is one thing ... slow to open in design view may be something else. Do you have a printer "assigned" to the report? Regards Jeff Boyce Microsoft Office/Access MVP "jmoore" wrote in message ... After reading several posts here I have changed all of the lookup fields to text fields in my table and deleted the relationships to the lookup tables. I came to this area looking for a solution to slow response time. Again, after reading several posts, I suspect it is due to the database design, but it is what I have to work with. Other than the lookup tables, there are only two related tables. If it would be helpful, I can remove the lookup tables. tSample (with the major fields listed below) Key (primary key) CNTYNAME ID Strata Sample tReview ReviewKey (PK) Key (FK) A1 A2. . . B1 B2. . .(in total there are over 60 number fields and 50 memo fields). I read that creating indexes will increase response time. Will creating a unique index from two fields in addition to the PK increase response time (e.g., in tSample, a unique key comprised of CNTYNAME and ID)? All was going well for the individual county reports, but I had problems when an aggregate report was needed at year end. I was able to solve the problem with advice obtained through this site. First, I created a union query (qUnion); a crosstab query based on the union query (qUnionCrosstab); and combined qUnionCrosstab and qReviewCountCrosstab into one query (qCrosstabsCombined) for the aggregate report. The aggregate report is extremely slow to open and painfully slow in design view. Will it help to create additional indexes? If not, I can live with this report as it is since I only need it once a year. I don't know how to change the design and transfer the data. Thanks for any tips for improvement. I am very grateful for the great information I have found here. |
#6
|
|||
|
|||
do I need more indexes?
Consider posting the SQL statement of your slow query(ies). Post them to
the .queries newsgroup for maximum exposure. Regards Jeff Boyce Microsoft Office/Access MVP "jmoore" wrote in message ... Some of my queries are slow to open. The object that takes the longest time just to open is the aggregate report. And when trying to make changes in design view, there is a long delay after clicking on a control and when it becomes active. The same with trying to move a control - very, very slow. I don't know about adding a new record yet as I have to make some changes before the next batch of information comes through and I thought I would try to address this problem at the same time. your general rule of thumb about indexes is what I read (sorry, don't remember which post) and is why I added another index. However, it does not appear to help. The printer is set to my default printer. Thanks again for all help. "Jeff Boyce" wrote: Define "response time" ... If you mean "how long it takes to add a new record in", then yes, when Access has to add the new data AND an index, it takes longer. If you don't have a high-volume data-entry business, you won't notice how much longer. If you mean "how long it takes to retrieve data via a query", then no, a properly-indexed set of tables will generate results faster. A general rule of thumb (i.e., not set in stone) is to index any field that is used as a selection criterion, as a join, or as a sort. Of course, if you only have two possible values (e.g., Male, Female), the index really won't help. Slow to open is one thing ... slow to open in design view may be something else. Do you have a printer "assigned" to the report? Regards Jeff Boyce Microsoft Office/Access MVP "jmoore" wrote in message ... After reading several posts here I have changed all of the lookup fields to text fields in my table and deleted the relationships to the lookup tables. I came to this area looking for a solution to slow response time. Again, after reading several posts, I suspect it is due to the database design, but it is what I have to work with. Other than the lookup tables, there are only two related tables. If it would be helpful, I can remove the lookup tables. tSample (with the major fields listed below) Key (primary key) CNTYNAME ID Strata Sample tReview ReviewKey (PK) Key (FK) A1 A2. . . B1 B2. . .(in total there are over 60 number fields and 50 memo fields). I read that creating indexes will increase response time. Will creating a unique index from two fields in addition to the PK increase response time (e.g., in tSample, a unique key comprised of CNTYNAME and ID)? All was going well for the individual county reports, but I had problems when an aggregate report was needed at year end. I was able to solve the problem with advice obtained through this site. First, I created a union query (qUnion); a crosstab query based on the union query (qUnionCrosstab); and combined qUnionCrosstab and qReviewCountCrosstab into one query (qCrosstabsCombined) for the aggregate report. The aggregate report is extremely slow to open and painfully slow in design view. Will it help to create additional indexes? If not, I can live with this report as it is since I only need it once a year. I don't know how to change the design and transfer the data. Thanks for any tips for improvement. I am very grateful for the great information I have found here. |
#8
|
|||
|
|||
do I need more indexes?
Thanks Jeff. I've posted the SQL for all of the queries.
"Jeff Boyce" wrote: Consider posting the SQL statement of your slow query(ies). Post them to the .queries newsgroup for maximum exposure. Regards Jeff Boyce Microsoft Office/Access MVP "jmoore" wrote in message ... Some of my queries are slow to open. The object that takes the longest time just to open is the aggregate report. And when trying to make changes in design view, there is a long delay after clicking on a control and when it becomes active. The same with trying to move a control - very, very slow. I don't know about adding a new record yet as I have to make some changes before the next batch of information comes through and I thought I would try to address this problem at the same time. your general rule of thumb about indexes is what I read (sorry, don't remember which post) and is why I added another index. However, it does not appear to help. The printer is set to my default printer. Thanks again for all help. "Jeff Boyce" wrote: Define "response time" ... If you mean "how long it takes to add a new record in", then yes, when Access has to add the new data AND an index, it takes longer. If you don't have a high-volume data-entry business, you won't notice how much longer. If you mean "how long it takes to retrieve data via a query", then no, a properly-indexed set of tables will generate results faster. A general rule of thumb (i.e., not set in stone) is to index any field that is used as a selection criterion, as a join, or as a sort. Of course, if you only have two possible values (e.g., Male, Female), the index really won't help. Slow to open is one thing ... slow to open in design view may be something else. Do you have a printer "assigned" to the report? Regards Jeff Boyce Microsoft Office/Access MVP "jmoore" wrote in message ... After reading several posts here I have changed all of the lookup fields to text fields in my table and deleted the relationships to the lookup tables. I came to this area looking for a solution to slow response time. Again, after reading several posts, I suspect it is due to the database design, but it is what I have to work with. Other than the lookup tables, there are only two related tables. If it would be helpful, I can remove the lookup tables. tSample (with the major fields listed below) Key (primary key) CNTYNAME ID Strata Sample tReview ReviewKey (PK) Key (FK) A1 A2. . . B1 B2. . .(in total there are over 60 number fields and 50 memo fields). I read that creating indexes will increase response time. Will creating a unique index from two fields in addition to the PK increase response time (e.g., in tSample, a unique key comprised of CNTYNAME and ID)? All was going well for the individual county reports, but I had problems when an aggregate report was needed at year end. I was able to solve the problem with advice obtained through this site. First, I created a union query (qUnion); a crosstab query based on the union query (qUnionCrosstab); and combined qUnionCrosstab and qReviewCountCrosstab into one query (qCrosstabsCombined) for the aggregate report. The aggregate report is extremely slow to open and painfully slow in design view. Will it help to create additional indexes? If not, I can live with this report as it is since I only need it once a year. I don't know how to change the design and transfer the data. Thanks for any tips for improvement. I am very grateful for the great information I have found here. |
#9
|
|||
|
|||
do I need more indexes?
Thanks for your offer. I posted the SQL for the queries in the queries
group. Each field in tReview is a rating of that criteria for a sample set of cases from tSample. There are a large number of cases in tSample that will not be used. Each case in tReview is a row, and contains all of the information for the complete review. A report for each county with a summary of their cases is produced during the year. These work fine. At the end of the year, an annual aggregate report is needed. It gives the information we need, but is very slow. I realize this is designed like a spreadsheet, but this has been in use for a year and I don't know how to change it, or transfer all of the data to a new design. I hope this is enough detail. I appreciate any advice you have. "Steve" wrote: You can significantly improve performance by a redesign of your tables. Without knowing anything about your database, I can tell you the design of tReview is very wrong just looking at what you posted. Your query(s) have got to be very complex hust to overcome the misdesign of tReview! Please give a detailed description of what you are trying to do in your database, and I will be very glad to give you a suggested redesign of your tables. Steve "jmoore" wrote in message ... After reading several posts here I have changed all of the lookup fields to text fields in my table and deleted the relationships to the lookup tables. I came to this area looking for a solution to slow response time. Again, after reading several posts, I suspect it is due to the database design, but it is what I have to work with. Other than the lookup tables, there are only two related tables. If it would be helpful, I can remove the lookup tables. tSample (with the major fields listed below) Key (primary key) CNTYNAME ID Strata Sample tReview ReviewKey (PK) Key (FK) A1 A2. . . B1 B2. . .(in total there are over 60 number fields and 50 memo fields). I read that creating indexes will increase response time. Will creating a unique index from two fields in addition to the PK increase response time (e.g., in tSample, a unique key comprised of CNTYNAME and ID)? All was going well for the individual county reports, but I had problems when an aggregate report was needed at year end. I was able to solve the problem with advice obtained through this site. First, I created a union query (qUnion); a crosstab query based on the union query (qUnionCrosstab); and combined qUnionCrosstab and qReviewCountCrosstab into one query (qCrosstabsCombined) for the aggregate report. The aggregate report is extremely slow to open and painfully slow in design view. Will it help to create additional indexes? If not, I can live with this report as it is since I only need it once a year. I don't know how to change the design and transfer the data. Thanks for any tips for improvement. I am very grateful for the great information I have found here. |
#10
|
|||
|
|||
do I need more indexes?
Before taking Steve up on his offer, you might want to google him and his
history in these newsgroups. Remember, not all advice you receive is of equal quality/applicability. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "jmoore" wrote in message ... Thanks for your offer. I posted the SQL for the queries in the queries group. Each field in tReview is a rating of that criteria for a sample set of cases from tSample. There are a large number of cases in tSample that will not be used. Each case in tReview is a row, and contains all of the information for the complete review. A report for each county with a summary of their cases is produced during the year. These work fine. At the end of the year, an annual aggregate report is needed. It gives the information we need, but is very slow. I realize this is designed like a spreadsheet, but this has been in use for a year and I don't know how to change it, or transfer all of the data to a new design. I hope this is enough detail. I appreciate any advice you have. "Steve" wrote: You can significantly improve performance by a redesign of your tables. Without knowing anything about your database, I can tell you the design of tReview is very wrong just looking at what you posted. Your query(s) have got to be very complex hust to overcome the misdesign of tReview! Please give a detailed description of what you are trying to do in your database, and I will be very glad to give you a suggested redesign of your tables. Steve "jmoore" wrote in message ... After reading several posts here I have changed all of the lookup fields to text fields in my table and deleted the relationships to the lookup tables. I came to this area looking for a solution to slow response time. Again, after reading several posts, I suspect it is due to the database design, but it is what I have to work with. Other than the lookup tables, there are only two related tables. If it would be helpful, I can remove the lookup tables. tSample (with the major fields listed below) Key (primary key) CNTYNAME ID Strata Sample tReview ReviewKey (PK) Key (FK) A1 A2. . . B1 B2. . .(in total there are over 60 number fields and 50 memo fields). I read that creating indexes will increase response time. Will creating a unique index from two fields in addition to the PK increase response time (e.g., in tSample, a unique key comprised of CNTYNAME and ID)? All was going well for the individual county reports, but I had problems when an aggregate report was needed at year end. I was able to solve the problem with advice obtained through this site. First, I created a union query (qUnion); a crosstab query based on the union query (qUnionCrosstab); and combined qUnionCrosstab and qReviewCountCrosstab into one query (qCrosstabsCombined) for the aggregate report. The aggregate report is extremely slow to open and painfully slow in design view. Will it help to create additional indexes? If not, I can live with this report as it is since I only need it once a year. I don't know how to change the design and transfer the data. Thanks for any tips for improvement. I am very grateful for the great information I have found here. |
Thread Tools | |
Display Modes | |
|
|