do I need more indexes?

[jmoore]

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.

 

[Jerry Whittle]

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.

 

[Jeff Boyce]

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]

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.

 

[jmoore]

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.

RE: 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]

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]

Thanks Jeff. I've posted the SQL for all of the queries.

 

[jmoore]

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.

 

[Jeff Boyce]

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

 

[John... Visio MVP]

And by the way, if you are going to trust what Jeff Boyce is telling you
when he said nothing about the repetitive fields in tReview, then ignore
all the above because I won't help you!!!

Steve

oh please make it so. Since your intent has always been only to help
yourself, that is not a big promise.

John... Visio MVP

 

[John... Visio MVP]

The truth hurts doesn't it stevie?

Nothing wrong with what Jeff posted. It was a basic caveat emptor.

John... Visio MVP

 

[BruceM]

Your history puts people on guard. You did not specifically try to get
money from the OP *now*, but your past trolling for work leads to the
suspicion that your offer to redesign will morph into a solicitation for
payment.

 

[Jeff Boyce]

Steve

Sorry if you took umbrage at my suggestion.

I merely reminded the poster that not everyone's advice is of equal quality.
I would thing the code of conduct governing MVPs would encourage thoughtful
and informed consideration.

But as long as we're on the topic you raised, code of conduct, how do you
justify ignoring the newsgroup etiquette that precludes folks from
soliciting paid work?

Jeff

 

[Jeff Boyce]

correction ... "I would think ..."

Jeff

Steve

Sorry if you took umbrage at my suggestion.

I merely reminded the poster that not everyone's advice is of equal
quality. I would thing the code of conduct governing MVPs would encourage
thoughtful and informed consideration.

But as long as we're on the topic you raised, code of conduct, how do you
justify ignoring the newsgroup etiquette that precludes folks from
soliciting paid work?

Jeff

 

[Jeff Boyce]

Are you saying that you'll abide by it if it's written?

 

[Jeff Boyce]

That doesn't sound like a commitment to abide.

A simple on-line search with "rules of conduct" and "microsoft" will lead
straight to Microsoft's Rules of Conduct concerning the newsgroups.

.... or are you offering to pay for the research?<G!>

Jeff

 

[John W. Vinson]

Show where it is written and cite the source.

http://www.microsoft.com/communities/conduct/default.mspx#EMC

Advertising/Solicitation: These communities were created as a forum for
providing peer-to-peer assistance related to using Microsoft products and
services. We ask that you refrain from posting unsolicited advertisements that
do not pertain directly to the intended use and purpose of the newsgroup or
chat.


And please don't use the tired canard that your solicitations are the
"intended use". If you'll note, you are the ONE AND ONLY SINGLE PERSON posting
such solicitations.

 

[Keith Wilby]

As compared to vendors of shoes, watches and other merchandise that no MVP
cares one iota about????????

Irrelevant clap-trap, no one is trying to sell such items in the Access NGs.
Stop soliciting in the groups and everyone's happy. Simple. Are you
*really* that strapped for cash? We can have a whip round for you if it
will shut you up. And don't cite anyone's postings attacking you, they do
it because they're sick to the back teeth of you, and in any case, you've
done far more than anyone else as can be seen here:

http://home.tiscali.nl/arracom/whoissteve.html

 

[BruceM]

You're right, he should avoid some of the language he uses, and stick to the
facts such as your documented history, which would make the point more
effectively anyhow.

You could post the same link John V. did if you feel the language is
inappropriate, but if you do so you can expect the same right back at you.
As for personal attacks, you have posted that "BruceM is wasting your time"
or some such, and other things of the sort with others, so if you decide to
use the Rules of Conduct to admonish others you should be prepared to see it
come right back at you when you indulge in sniping.

Rules of Conduct

Appropriate Language: ....... Please avoid personal attacks, slurs, and
profanity in your interactions.

Why aren't you admonishing Arno R?

Why aren't you admonishing Visio John? Is it because he is a good-old-boy
MVP??

 

[Jeff Boyce]

Still not hearing any commitment to abide by those Rules.

Regardless of whether others chose to (and take hits if they don't), will
you?

Jeff

Rules of Conduct

Appropriate Language: ....... Please avoid personal attacks, slurs, and
profanity in your interactions.

Why aren't you admonishing Arno R?

Why aren't you admonishing Visio John? Is it because he is a good-old-boy
MVP??