Removing Index Doesn't Hurt Humongous Join?

[PeteCresswell]

Two tables:
- tblSecurity
- tblPaymentAccrualDaily

11,000 and 3 million records respectively.

Joining them on SecurityID (PK of tblSecurity) returns 3 million
records in about 10 seconds.

Removing SecurityID as PK and removing SecurityID index in both tables
(and compacting/repairing just for good measure) does not seem to
affect the speed of said query: it's still about 10 seconds.

I find this tb counter-intuitive.

Or is my idea of "humongous" way short of what's really humongous?

 

[Dirk Goldgar]

Two tables:
- tblSecurity
- tblPaymentAccrualDaily

11,000 and 3 million records respectively.

Joining them on SecurityID (PK of tblSecurity) returns 3 million
records in about 10 seconds.

Removing SecurityID as PK and removing SecurityID index in both tables
(and compacting/repairing just for good measure) does not seem to
affect the speed of said query: it's still about 10 seconds.

Do you have a relationship defined and enforced between the two tables? If
you do, there is a hidden index to support that relationship, whch is not
displayed for you.

 

[PeteCresswell]

Do you have a relationship defined and enforced between the two tables?  If

you do, there is a hidden index to support that relationship, whch is not
displayed for you.

Yes, and that would explain it.

Would that suggest that, to avoid over-indexing, I should avoid
explicitly indexing any field - either FK or PK - that is involved in
a relationship?

 

[Dirk Goldgar]

Yes, and that would explain it.

Would that suggest that, to avoid over-indexing, I should avoid
explicitly indexing any field - either FK or PK - that is involved in
a relationship?

You must index the primary key, regardless. However, you do not need to
index a foreign-key field that is involved in an *enforced* relationship,
and it would be more efficient not to do so. Note that relationships for
which referential integrity is not enforced do *not* result in hidden
indexes being created, so you do want to index those fields.

It would be nice if Access would not create additional indexes to enforce
relationships, when an adequate index already exists. Failing that, it
would be nice if it would show us the hidden indexes it has created.
Unfortunately, its behavior in this are doesn't seem likely to change any
time soon. Note that you can write your own code using DAO objects to
examine the indexes and relationships. For example,

'----- start of code -----
Sub ListIndexes()


Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ix As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb

For Each tdf In db.TableDefs

If Left(tdf.Name, 4) <> "MSys" Then
If tdf.Indexes.Count <> 0 Then

Debug.Print "----- Table: "; tdf.Name; " -----"

For Each ix In tdf.Indexes

Debug.Print "Index: "; ix.Name; _
IIf(ix.Primary, " (primary)", ""); " -----"

For Each fld In ix.Fields
Debug.Print , "Field: "; fld.Name
Next fld
Next ix

End If
End If

Next tdf

Set db = Nothing

End Sub
'----- end of code -----

 

[PeteCresswell]

It would be nice if Access would not create additional indexes to enforce

relationships, when an adequate index already exists.  Failing that, it
would be nice if it would show us the hidden indexes it has created.

Every time a hard-core database person looks at one of my apps, they
gig it for having redundant indexes on PKs.

e.g. I designate "SecurityID" as the PK and JET creates "PrimaryKey"
and "SecurityID" indexes no matter what.

Would I be ok in opening up the table in Design mode before creating
any relationships and manually deleting the "SecurityID" index?

 

[Dirk Goldgar]

Every time a hard-core database person looks at one of my apps, they
gig it for having redundant indexes on PKs.

e.g. I designate "SecurityID" as the PK and JET creates "PrimaryKey"
and "SecurityID" indexes no matter what.

Would I be ok in opening up the table in Design mode before creating
any relationships and manually deleting the "SecurityID" index?

Sure. You can also tell Access not to automatically index fields ending in
"ID", "Code", and other common suffixes. It's only trying to help, but you
can tell it not to be so helpful on the Tables/Queries tab of the Options
dialog, where there's a list labeled "AutoIndex on Import/Create:". If you
delete the entries in this list, Access won't automatically create indexes
for them.

 

[Douglas J. Steele]

Sounds like Access trying to be helpful.

Look on the Tables/Queries tab under Tools | Options on the menu bar. Blank
out the "AutoIndex on Import/Create" text box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It would be nice if Access would not create additional indexes to enforce
relationships, when an adequate index already exists. Failing that, it
would be nice if it would show us the hidden indexes it has created.

Every time a hard-core database person looks at one of my apps, they
gig it for having redundant indexes on PKs.

e.g. I designate "SecurityID" as the PK and JET creates "PrimaryKey"
and "SecurityID" indexes no matter what.

Would I be ok in opening up the table in Design mode before creating
any relationships and manually deleting the "SecurityID" index?