Almost 2 years after this discussion was started I got the same problem... so I'm posting this for future generations of access programmers (like me
):
this topic was found:
http://p2p.wrox.com/topic.asp?TOPIC_ID=25861
Try running:
Sub ListIndexes()
Dim cat As New ADOX.Catalog
Dim idx As New ADOX.Index
Set cat.ActiveConnection = CurrentProject.Connection
For Each idx In cat.Tables("tblAsset").Indexes
Debug.Print idx.Name
Next
End Sub
Access will automatically create indexes behind the scenes that aren't listed in the Indexes dialog under certain circumstances. For example, all foreign key fields in your table are automatically indexed, and you won't see these indexes in the index list. I kinda' doubt 27 of your 46 fields are foreign key fields, but listing the index names might turn up something interesting.
Just as an example, in the Northwind database the index dialog of the Order Details table list 3 indexes:
PrimaryKey
OrderID
ProductID
But the table really has 5 indexes because OrderID and ProductID are foreign key fields:
PrimaryKey
OrderID
OrdersOrder Details
ProductID
ProductsOrder Details
See what you get. If the code doesn't list 32 indexes, you probably gotta assume the table's data pages are corrupt, and you might try importing your data into a new table.
HTH,
Bob
----------------------------------------------------------------------
Thats actually an interesting question Rohan, given that Microsoft - in much of its query performance optimization lit - recommends explicitly indexing fields on both sides of a join.
Not a good thing to do in Access. Since Access creates a hidden index on all foreign key fields when you establish a relationship, if you explicitly create one, that means all you've done is duplicate an index. Now the Jet engine has twice as much work to do every time data in an indexed field changes (inserts, updates, deletes). If you insert a field into your table with 21 indexes, Jet now has to write to the index buffer 42 times instead of 21 times. Besides, Jet will use the implict index over the explicit index when queries are run anyway. The explicit index is simply ignored.
Here is a portion of the query plan that the Jet query optimizer produces when I run a simple query based on the Customers and Orders tables in Northwind:
--- temp query ---
- Inputs to Query -
Table 'MSysRelationships'
- End inputs to Query -
01) Restrict rows of table MSysRelationships
using rushmore
for expression "(szObject=wszTableName) OR (szReferencedObject=wszTableName)"
--- temp query ---
- Inputs to Query -
Table 'Customers'
Table 'Orders'
Using index 'CustomersOrders'
Having Indexes:
CustomersOrders 830 entries, 4 pages, 89 values
which has 1 column, fixed
CustomerID 830 entries, 4 pages, 89 values
which has 1 column, fixed
- End inputs to Query -
01) Inner Join table 'Customers' to table 'Orders'
using index 'Orders!CustomersOrders'
join expression "Customers.CustomerID=Orders.CustomerID"
"CustomersOrders" is the implicit index that Access creates on the CustomerID field in the Orders table, and that Jet uses to perform the query. "CustomerID" is the duplicate explicit index, which is not used. The indexes are identical: 830 entries, 4 pages, 89 values
A word of caution. Its possible to over-index a table. Jet automatically updates the indexes on a table every time a record is added or deleted, or any time the data in an indexed field changes. Don't be surprised if you perform, say, a bulk update operation (update 1000 records) on your 21 index table and it takes a while. Jet is busy writing to the index buffer 2100 times.
Indexes should be used cautiously,e.g. primary key fields, frequently sorted fields, and feilds that serve as query criteria. That's about it.
HTH,
Bob
------------------------------------------------------------------------
A final thought: Another place to view the hidden indexes on your tables is Access's db object Documenter. You can find it on the main menu at Tools -> Analyze -> Documenter. Indexes (implicit and explicit) are listed toward the end of the report.
Bob
-----------------------------------------------------------------------
Just a quick comment on this.
I created another couple of tables and this time when I created the relationships between them I didn't enforce RI, thus when I checked the indexes there was no index automatically created.
So I guess if you enforce RI there's no need to create indexes on table joins, but if you don't enforce it (and I've seen places that actually don't, don't know what their developers are thinking) then you need to explicitly create an index.
Cheers
Rohan
--------------------------------------------------------------------------
Interesting Rohan, didn't realize that.
Also noticed Microsoft states that if indexes already exist on foreign key fields (i.e., you create explicit indexes), and then enforce RI, Jet will use the pre-existing indexes; if the indexes don't already exits when you enforce RI, Jet will create its own hidden, internal indexes.
As we've found, Jet (Jet 4.0 anyway) doesn't seem to actually behave that way. The duplicate indexes are created instead. Thanks for kicking this around.
Bob
