MS Index Questions

Posted on 2013-12-09
Last Modified: 2014-01-17
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.
Question by:pcalabria
  • 3
LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39706027
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.

LVL 57
ID: 39706030
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.

LVL 84
ID: 39706036
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.
LVL 57
ID: 39706165
<<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.

LVL 34

Expert Comment

ID: 39706431
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


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now