indexing both sides of a table relationship join

[Paul James]

In the section about improving the performance of an Access database, MS
Access Help first says:

You can make dramatic improvements in the speed of queries by indexing
fields on both sides of joins.

But then further down it also says you should only index fields that have
mostly unique values. However, in general, the field on the "many" side of
a join isn't unique. So how should this seemingly contradictory
advice be interpreted? Should you index the "many" side of a join?

Thanks in advance,

Paul

 

[Allen Browne]

When you create a relatinship, you almost always check the Referential
Integrity box. Access then creates a hidden index on the foreign key, so it
makes no sense to create another relationship yourself.

The recommendation to index only fields that have lots of different values
is fairly standard database theory. In practice, though we have found it
worthwhile indexing any field that is heavily used for sorting or criteria.
For example, a Yes/No field can only have 2 possible states, so would not
normally be a candidate for indexing. However, you often have an Inactive
field, where historical data is retained by flagged and excluded from most
searches and displays. We have found that indexing this Yes/No field is very
worthwhile in Access.

 

[Paul James]

Thanks, Allen. You've answered some other issues I'm confronting as well.

I'm also glad you mentioned that it's worthwhile to index the Yes/No fields
that are used in searches, because I've got plenty of those.

Paul

 

[Michael Miller]

Thanks also Allen,

I have a situation where a drop down is taking a little over a minute to
load, which is based on a query of about 4 tables, where the lookup tables
are all primary keyed, but the parent table ID's (other than the primary key)
are not indexed and link to their respective foreign keys in the query only,
which are primary keys in their respective lookup tables.

I have not put in relationships yet, as this is a conversion from Access and
I am handling integrity in code for now.

Should I index in the master table and should they be not clustered in with
the primary key, and should they be combined into their own IX_StarsProject
index cluster, or should they all be assigned their own index names and only
index one field in each index name?

Example:
Master table, StarsProject, primary key is ProjectID, contains another ID
field, not indexed of AssigneeID, which links to the Users Table where the
primary key is UserID.

Should AssigneeID be indexed and should it be in a cluster of about 4-5
(other ID) fields (that also link to lookup tables), or should all additional
indexes be separate?

StarsProject
--------------
ProjectID (PK)
AssigneeID* ---> Users/UserID (PK)
InitiativeID* ---> StarsInitiativeList/InitiativeID (PK)
RegionID* ---> StarsRegionList/RegionID (PK)
etc.

* not indexed yet.
Thanks.
MichaelM

 

[Allen Browne]

Firstly, you mentioned clustered indexing. That's not actually an Access
term, so is the data is Access tables, or SQL Server or something else?

Will assume Access. When you do create the relationship, will they be
created with referential integrity? If so, that's all you need. If not, then
yes, you should index the foreign key fields. In general, indexing each
field is probaby more efficient than using a multi-field index, because
Access can use each index independently as well as using them
co-operatively. The exception is when you want to insist that a combination
is unique, or using the combination as the keys of a relationship to another
table. For example, if you are selling limited edition prints of your
photographs, the PhotoID + EditionNo would be a good candidate for a 2-field
index.

Access cannot use the 2nd or subsequent field of a multi-field index on its
own. It also cannot use the index if you are not searching at the start of
the field, e.g.:
Like "*wildard in the middle or end"

The fields you regularly sort on will also benefit from indexing.

How many records are you loading into this combo into this combo? It should
be able to load a few thousand almost instantaneously. If you have more than
that, you may want to consider delay-loading the combo as described here:
http://members.iinet.net.au/~allenbrowne/ser-32.html

Do we know that it is the combo that is causing the delay? Easy enough to
test, by copying the form and removing the combo. If other factors are at
play, it might help to look at Tony Toews' Access Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm

 

[Michael Miller]

Allo Mate,

Access front end, SQL Svr back end.

No relationships in Access or diagram in Sql Svr, and no referential
integrety set.
<had a bad experience with Access, where I related all the tables in my app
and one day, a month or so later, came in to look at the diagram and it was
all gone. so I did not bother to reinstate it, as I was just trying to
create a converted app, quickly, based on what the dept had been doing in
Excel. I can go back once I get the access back end, sufficiently converted
to sql svr>

See the rest of my responses below*:
"***MDM-"

<* Let me know if it's more efficient for me to respond here, on top of the
message>

Thanks again, mdm

Firstly, you mentioned clustered indexing. That's not actually an Access
term, so is the data is Access tables, or SQL Server or something else?

Will assume Access. When you do create the relationship, will they be
created with referential integrity? If so, that's all you need. If not, then
yes, you should index the foreign key fields.

In general, indexing each

field is probaby more efficient than using a multi-field index, because
Access can use each index independently as well as using them
co-operatively.

***MDM-ok, that's what I needed to know. The fields are independent,
linking to their respective lists to pull Names. I understand and have used
multiple field primary keys, but didn't see the logic of clustering all the
independent fields in the master table.

The exception is when you want to insist that a combination

is unique, or using the combination as the keys of a relationship to another
table.

For example, if you are selling limited edition prints of your

photographs, the PhotoID + EditionNo would be a good candidate for a 2-field
index.

***MDM - are you a(nother) photographer?

Access cannot use the 2nd or subsequent field of a multi-field index on its
own. It also cannot use the index if you are not searching at the start of
the field, e.g.:
Like "*wildard in the middle or end"

The fields you regularly sort on will also benefit from indexing.

How many records are you loading into this combo into this combo? It should
be able to load a few thousand almost instantaneously. If you have more than
that, you may want to consider delay-loading the combo as described here:
http://members.iinet.net.au/~allenbrowne/ser-32.html

***MDM-One list that takes alotta time is just giving the users a dropdown
choice of a "Project". There are 1600 projects, but I have to link to about
4 tables to pull in Names that they can see in the list. Each of the sub
tables is prime keyed by their respective ID's. When the user starts typing
a (known) project # into the combo, or just drops it down, it takes a good 30
secs, and now that I have linked to sql, a good minute before it drops down
the list and highlights the selection. I use left outer joins from the
master to each of the child tables. In some cases Access is balking at other
joins I have in other queries, saying they are ambigious. But, those qrys
have been used for the last 6 months in access.

I will look up the code in your links. BTW, if I run the query, outside the
form, with a criteria of a project #, it comes up instantly.

 

[Allen Browne]

Ok: Snipped and answers embedded.

Access front end, SQL Svr back end.

Okay: presumably you have created your relationship and indexes in SQL
Server. Can't imagine you running it without any.

In general, indexing each
***MDM - are you a(nother) photographer?

Have done some in years gone by, but the first Access database I sold
(Access 1, in 1993) was for a gallery doing this kind of thing.

***MDM-One list that takes alotta time is just giving the users a dropdown
choice of a "Project". There are 1600 projects, but I have to link to
about
4 tables to pull in Names that they can see in the list. Each of the sub
tables is prime keyed by their respective ID's. When the user starts
typing
a (known) project # into the combo, or just drops it down, it takes a good
30
secs, and now that I have linked to sql, a good minute before it drops
down
the list and highlights the selection. I use left outer joins from the
master to each of the child tables. In some cases Access is balking at
other
joins I have in other queries, saying they are ambigious. But, those qrys
have been used for the last 6 months in access.

Agreed, that's unusable. When you convert a snappy Access app to SQL Sever,
it normally does become slower to fee lots of subforms, combos, and so on,
but it it was already taking 30 sec in Access, it was already unworkable
before that.

I will look up the code in your links. BTW, if I run the query, outside
the
form, with a criteria of a project #, it comes up instantly.

True? Would it be worth dropping a new combo onto a blank, unbound form with
nothing else on it, and verifying that this combo alone take 60 sec to load
also?

BTW, did you work through the list of things in Tony's "Performance FAQ" at:
http://www.granite.ab.ca/access/performancefaq.htm

Regards