MS Index Questions

Hello Experts,

Is there any way that I can determine what fields should be indexed in a specific table, or group of tables, without crawling through the code and analyzing all the queries?

I am specifically concerned with a group of 201 tables that I use to store customer inventory.  The tables have names like 0000,0001,0002...0200, and each table, with the identical structure, has about 125 fields.

This table naming convention that I've used, allows me to programmatically create queries and recordsources based upon a specific customers inventory (for example, table [0101], so I can not simply search my code for a specific table name as you would normally do to find all the SQL statements in our code that reference a specific table (this code has matured for more than 10 years, so the variable names used in the code varies depending upon the routine.)

I'm sure there are at least 200 queries and RecordSource statements embedded in my code.

In addition, I likely have a hundred or more queries, that use this data.

Is there any way to determine which fields should be indexed, without crawling through the code and examining every SQL statement and query?

Some of the tables have a hundred thousand records, so performance is a critical concern.


In other words, my code may use a variable to represent the table name.
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
Short answer: no.

Indexes in general are almost more of an art form and you'll always be chasing the weakest link in determining which indexes to use.

More to the point, with JET, there are few few tuning tools available to you.   There is JET SHOWPLAN (shows you the query costing plan) and the ISAMSTATS method, so you can see what the dbEngine si doing in order to process a query in terms of page hits.

There is the database performance analyzer, but I've found it to be worthless.

What I'd do is:

1. Index the obvious - PK's need to be indexed of course, and any foreign key field (do this only if you do not enforce RI through a relationship - if you do, JET indexs the key for you).

2. In general, do not use compound keys for an index, index the individual fields.   A compound key won't be used unless the operation matches the index exactly.  

3. Don't index fields with low cardinaility (range of values).   for example, a yes/no field.   Since JET indexes are ISAM based, doing this yeilds an index that can be as large as the table.  Overall it's faster to simply scan the table.

4. Index some of the critial criteria fields - ie.  Start/End Dates, Invoice #, Sales Order Number, etc.   In other words those fields that are not keys, but would very often be used for an inquiry or report.  Don't go overboard here though - only do the most obvious ones.

Use that as a starting point, then go after your longest running queries and see how that might be improved.

Keep in mind that with every index you add, you slow down CUD (Create, Update, Delete) operations.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and BTW, if you have a SQL backend, then it's a whole different story, but the indexing rules still apply as well as the approach.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How about using something like Total Access Analyzer:

I know it will let you know of issues with indexes, but I'm not sure if it will suggest fields you should index.

It will, however, let you know where fields are used, so you can then determine if/when you should be indexing. While indexing is somewhat of a fluid science, in general you should index on any field used in a Join or WHERE clause, any field used extensively in searches, etc. You can over-index as well, so consider that when you determine which to add.

Note too that indexing needs can change as your database grows. What makes sense as in index today may not make sense in 6 months, so you should periodically revisit the indexing scheme.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I know it will let you know of issues with indexes, but I'm not sure if it will suggest fields you should index. >>

 I don't believe it does, simply because there are no real hooks into the DB engine to get any kind of statistics.

 The only public ones are JET Showplan and ISAM stats, and even Showplan is not complete (it's doesn't show you the costing plan for any sub-queries).

 There are hooks in the dbEngine that Microsoft internally uses for development, but no one outside of Microsoft knows how to turn them on.

Primary keys are automatically indexed so do not create additional ones.  Foreign keys are also automatically indexed if you have defined them in the Relationships window.  I don't know whether enforcing RI makes a difference.  It doesn't sound to me like your database is normalized so this point may be moot.

Access may already have created a bunch of spurious indexes for you if you left the "Auto Index on Import/Create" setting at its default value.  It arbitrarily indexes any column that ends in ID, CD, and some other values.  Best practice is to remove this automatic indexing and create your own.

If your tables are all the same, you should be able to examine one of them and that should give you a clue what you need to do for the others.  In general you should index on columns you normally join on (other than the PK/FK which already have automatic indexes) and you should also index any columns you normally search on.  It was already pointed out that you shouldn't create indexes for fields with low cardinality of values.  You could however, use compound indexes in some cases.  The database engine will use compound indexes if the search field is the first field in the index.  So, if I add employeeID as the second column in my Department index, the database engine might use the index.  To play around with this, you would need to spend a lot of time reading query plans to determine if you are having any impact so don't go here unless you have a slow query and you want to try to speed it up.

There is a downside to over indexing particularly in a database with the type of duplication you have implemented and that is each index takes space and so increases the overall size of the database file and each index takes time to maintain so as you add and update records, the indexes must also be maintained.  The data part of your tables isn't shoved around when record size changes.  The updated record is simply added to the end of the table in the first available free space.  However, indexes are managed differently and might be rebuilt on the fly if you run out of free space in a given area.  The whole point of indexes is that records are physically adjacent and so inserts and updates need to be placed where they logically would fall.

Here's some code that finds indexes in a database and records them into a table.  This code comes from an application I created to document databases.  You'll need to create the table used in the code and create a form to collect the database name and run the code.  
Sub Create_tblTableIndexes()

    Dim db As DAO.Database
    Dim ThisDB As DAO.Database
    Dim tblLoop As DAO.TableDef
    Dim fldLoop As DAO.Field
    Dim idxLoop As DAO.Index
    Dim TD1 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TempSet1 As DAO.Recordset
    Dim Position As Integer
    Dim CountIndexes As Integer
    Dim strDatabase As String

    'strDatabase = "C:\hartman\ImportDB2.mdb"
    strDatabase = Forms!frmPrintDoc!txtDBName
    CountIndexes = 0
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    Set QD1 = ThisDB.QueryDefs!QdeltblTableIndexes
    Set TD1 = ThisDB.TableDefs!tblTableIndexes
    Set TempSet1 = TD1.OpenRecordset

    ' Loop through TableDefs collection.
    For Each tblLoop In db.TableDefs
        ' Enumerate Fields collection of each
        ' TableDef object.
'' add error checking for 3024 - not found in collection
'' add error checking for 3110 - no permission to read msysmodules2

        On Error GoTo ErrorHandler
        For Each idxLoop In tblLoop.Indexes
            CountIndexes = CountIndexes + 1
            Forms!frmPrintDoc!txtIndexCount = CountIndexes
            Forms!frmPrintDoc!txtIndexName = tblLoop.Name
            If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 1) = "z" Or Left(tblLoop.Name, 1) = "~" Then
                Position = 1
                For Each fldLoop In idxLoop.Fields
                        TempSet1!IndexName = idxLoop.Name
                        TempSet1!Unique = idxLoop.Unique
                        TempSet1!OrdinalPosition = Position
                        TempSet1!FieldName = fldLoop.Name
                    Position = Position + 1
                Next fldLoop
            End If
        Next idxLoop
    Next tblLoop

Exit Sub


Select Case Err.Number
    Case 3110
        MsgBox "Open " & strDatabase & " and change the admin security to allow read for MSysModules", vbOKOnly
    Case 3043
        MsgBox "Please select a valid database", vbOKOnly
    Case 91   ' db was not opened so it cannot be closed.
        Exit Sub
    Case Else
        MsgBox Err.Number & "-" & Err.Description
End Select
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.