Hi,

I am wondering if anyone has come across this problem before.

I have got a table with 46 fields in it. 17 of them are ID's linked to lookup tables, and 9 of them are linked to other tables in a 1-to-many type relationship. The rest is just a combination of date/text/currency/number fields, and no memo's.

I am trying to put indexes on the 17 fields, but I can only put in about 5 before I get the following message "The operation failed. There are too many indexes on table 'tblAsset'. Delete some or all of the indexes and try the operation again."

I know the limit is 32, but I don't get anywhere near that limit. In table design view, I can see only 7 indexes there. When the table was created, I turned off automatically creating indexes on fields where it contains "ID/Num/etc...."

If anyone has come across this before or has a solution, that will be great!!

Cheers :)

__________________
Cheers

Rohan

Hi,

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

Hey Bob,

Thanks for that code snippet. It shows me all the 'hidden' indexes on that table. Although it only lists 21 indexes (and I can't add anymore), but I can live with this as there are indexes on what I want.





Cheers

Rohan

Hey again.

Actually, I've got another question that extends from this one.

This is the result of running the code on a different table.

tblAssetCriticalRating
    - PrimaryKey
    - Asset
    - CriticalRating
    - tblAssettblAssetCriticalRating
    - tblCriticalRatingtblAssetCriticalRating

Those highlighted in red are the indexes automatically created when I create relationships to other tables.

The other indexes in blue are indexes I created (Asset, CriticalRating), which perform the same function as the ones in red. As I actually put indexes on table joins.

So the new question is, (as silly as it sounds) do I ever need to put indexes on table joins? As it seems that Access takes care of this automatically. Am I just creating superflous/unnecessary joins?

Cheers again :)

Cheers

Rohan

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

Oops..."Jet is busy writing to the index buffer 2100 times" should read 21000 times.

Bob

Hey,

Thanks for that. It explains it all pretty well. And yes, the only reason I was adding all those indexes was because of all the doco I'd read about putting indexes on table joined fields.

However, it still doesn't explain why I've only got about 22 indexes on tblAsset.

But there are a few things I'll try with that first before I query it.

Thanks again :)


Cheers

Rohan

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