Just one little Index (2 Viewers)

[Shimon]

Hi,
I'm not really an Access developer, but took up the responsibility for implementing changes in an existing Access database.
I do have previous experience writing "raw" applications in Access and served as a Data Administrator (not DBA) in a big project.

The application is for a Non-Profit dress rental organization. It was written using Hebrew (RTL) in most DB objects, which makes it very difficult for me.

The programmer is a very capable self-learned programmer, but has a very weak understanding of DB principles, and no understanding of Basic Design Principles ( Open Closed, Dependency Inversion, Model-View-Controller etc.)

It has a BE of about 15 tables, Customer, DressStyle, DressInstance, Rental, RentalDetails, RentalPayment, RentalPaymentRecvd, Employees, EmployeeAttendance, ProgramPreferences and OrganizationPreferences. It was developed for another Organization that has similar Non-Profit rentals.

When I got the program, it had no Indexes or relations.

I added indexes for all the fields that were used in the Forms that had poor response time, but did not make any other changes to the underlying Data Model. For some reason, I did not add an Index to the CustomerID, as there were no business processes that looked up the Customer. Only in the Admin Forms was there the ability to look up the Rentals for a specific Customer, and I changed only the Data that hindered the daily use of the program.
The first and main form was the New Rental Form, which had :

• section for adding ( or choosing) a Customer,
• choosing the date of the wedding ( or whatever) and then
• a combo-box list for choosing the dress-style-number, size and amount.

This form worked fine for almost two years, but became very slow and error-prone in the last 6 months.
When I finally got around to checking it, I added five or six indexes to the tables in the BE and the problem was solved. As I was curious to know what the root problem was, I rolled back to on older BE and added the Indexes, one at a time to the fields that I thought were the root of the problem.
As the lag happened when choosing the dressInstances available for a specific date, I was sure the problem was the missing index in the Date fields of the table RentalDetails which saved the date that a specific instance was taken , but adding these Indexes did not alleviate the problem.

What did help was the Index on the CustomerID field in the Customer table.

Now there is no logical explanation that I can think of, except that there was some circular lock on some tables and this point in time, when a specific part of the Form was used. Maybe Access uses different locking strategies for a Indexed table???
So what's the lesson learned by me?
Not much, as I still strongly believe in the saying "If it ain't broken, don't fix it".

Sincerely,
Simon from the Holy Land.

 

[mike60smart]

Hi Simon
Can you upload a screenshot of your table relationships?

 

[The_Doc_Man]

Hello, Simon. Shalom.

The need for an index is best determined by examining the relationships or the specific query. An index helps processing speed when some code or query exercises that index, perhaps to establish or explore the table as part of a formal or informal relationship. Since you see an improvement when you establish that index, it means you ARE using that index that whether on not you know how you use it. Don't forget that a field index can help when there is an ORDER BY, GROUP BY, or WHERE-clause reference in any query. Its effects are not limited to JOIN...ON clauses.

To the best of my knowledge, there is no automatic difference in locking strategies for indexed vs. non-indexed tables though there IS such a thing as Access building an internal query plan. I just don't think locking ever enters into that plan. The lock strategy is set in the expressed or implied query and can be found by looking at the properties of the query. Based on a database setting found through the ribbon via File >> Options >> Current Database, the default will either be No Locks or Optimistic locks. Even if there is no difference in locking strategy, the query's locking method (No locks, Optimistic, Pessimistic) could affect efficiency of execution.

The no-index case has to search for a name by running through the whole table (expected efficiency: N/2) but the index case search is much faster (expected efficiency: Log<base 2>(N) ). If you have a table of 1000 customers (even if only a few are currently active), the no-index search takes 500 time units (on average) whereas the index search takes only about 10 time units (on average). I'm deliberately ambiguous about "time units" because the length of one of those time units depends on whether the front-end/back-end link is internal to a single machine or has to cross network links. Also, the selected locking method enters into the length of a time unit. Both network delays and locking delays are essentially constants. Only the use of an index is variable in this efficiency computation.

In order to have a formal JOIN query you are required to have an index for the "one" side of a one/many or many/one relationship. But it is possible to have an improperly formed JOIN, which is a query that doesn't use a formal JOIN...ON clause but DOES name multiple tables and involves restrictive WHERE clauses that have the same effect as a JOIN...ON clause. You did suggest that the original author wasn't well-versed in techniques, so I'm guessing that person could have tried something like this.

@mike60smart requested that you take a screen-shot of your relationships window, which would help us see if there is an obvious situation where your customerID is involved. However, because it is possible to have built improper JOIN queries, you might have to see if there are queries that name customerID.

 

[Mark_]

For me, the quickest way to find out what fields are used in QUERIES, would be to open each in SQL view, copy it to a document that allows search (such as word) and then start looking at all of the WHERE and ORDER clauses.

This is to help implement what Doc posted above. Trying to find this by looking at the queries in design view would not be fun.

 

[Pat Hartman]

@Shimon
You need to add PKs to all the tables. If there is a natural key, you can use that. Otherwise add an autonumber. Do NOT add autonumbers to tables that have existing natural keys.

At this point, adding relationships will require you to fix up all the bad data and I can assure you that there will be bad data given the poor practices of the initial developer. If I were going to be responsible for the application, I would take the time to clean up the data because having bad data in a database offends me. The developer has a responsibility to the users to ensure that the data is as clean as humanly possible. Users make business decisions based on what your reports tell them.

When you create a PK, Access should automatically add a unique index so there should have been one created for CustomerID.

It doesn't sound like your db should have tables that are large enough to make it slow so that problem may be caused by bloat. You should run the compact and repair utility at least once a month and make daily backups of the BE. And backup the FE every time you make changes. Compile and compact at this time also.

 

[isladogs]

For me, the quickest way to find out what fields are used in QUERIES, would be to open each in SQL view, copy it to a document that allows search (such as word) and then start looking at all of the WHERE and ORDER clauses.

This is to help implement what Doc posted above. Trying to find this by looking at the queries in design view would not be fun.

No need to export. SQL view in Access has provided Find and Replace functionality for several years in both the standard SQL editor . . .

and now in Monaco . . .

In addition, you can use the database documenter.

 

[Shimon]

Hi Simon
Can you upload a screenshot of your table relationships?

I'll try, but I don't know if I'll get to it until next week.

 

[GPGeorge]

@Shimon
You need to add PKs to all the tables. If there is a natural key, you can use that. Otherwise add an autonumber. Do NOT add autonumbers to tables that have existing natural keys.

At this point, adding relationships will require you to fix up all the bad data and I can assure you that there will be bad data given the poor practices of the initial developer. If I were going to be responsible for the application, I would take the time to clean up the data because having bad data in a database offends me. The developer has a responsibility to the users to ensure that the data is as clean as humanly possible. Users make business decisions based on what your reports tell them.

When you create a PK, Access should automatically add a unique index so there should have been one created for CustomerID.

It doesn't sound like your db should have tables that are large enough to make it slow so that problem may be caused by bloat. You should run the compact and repair utility at least once a month and make daily backups of the BE. And backup the FE every time you make changes. Compile and compact at this time also.

In a recent presentation to our Access User Group, Tom van Stiphout and Kim Young talked about transitioning a project from one developer to another. One of the things they made a point of is that you need to establish a baseline for yourself as to what things must be dealt with before assuming responsibility for someone else's project. Pat has noted the same thing here with regard to data integrity. You might find it useful to review what all Tom and Kim discussed with our group.

 

[Shimon]

Thanks all for your input.
I spent a few hours creating a set of queries to copy all the tables, delete the data from the original ones and append the original data from the copied tables back into original ones, after creating the necessary PK and relations. I then removed part of the relations, as I was afraid that some of the forms would stop working, if the relations were enforced.
This way I got a clean data set, unique PK and indexes on all PKs..
As the table names are in Hebrew, a picture would not mean much to most of you.
As there are bugs that the original developer fixes every once in a while, I am hesitant of doing a major refactoring of all object names.
Simon

 

[Pat Hartman]

Here's some code from a database I built to document databases. I took the code out of context so I hope nothing is missing. The app needs references to the current db which is called ThisDB and the database being documented is referenced as simply db. The argument "vSourceDBID" is used because you can accumulate data from other db's so you could use the code to link to db1, import the querydefs, then link to db2, import those querydefs, etc until you have all the querydefs collected from the databases you are interested in. You could add an overall loop to collect querydefs from all databases in a folder.

However, if you don't use querydefs, you are SOL unless you want to scan all your code.

Sub Create_tblQueries(vSourceDBID)

''''Reference Field object definition https://msdn.microsoft.com/en-us/library/office/ff193203.aspx

    Dim db              As DAO.Database
    Dim qryLoop         As DAO.QueryDef
    Dim fldLoop         As DAO.Field
    Dim propLoop        As DAO.Property
    Dim proppropLoop    As DAO.Property
    Dim tdQ             As DAO.TableDef
    Dim qdQ             As DAO.QueryDef
    Dim rsQ             As DAO.Recordset
    Dim tdQF            As DAO.TableDef
    Dim qdQF            As DAO.QueryDef
    Dim rsQF            As DAO.Recordset
    Dim strDatabase     As String
    Dim ThisDB          As DAO.Database
    Dim CountQueries    As Integer
    Dim SAVEQueryID     As Integer
'''    Dim DateUpdated As Date
    
    On Error GoTo Err_Create_tblQueryFields
    strDatabase = Forms!frmPrintDoc!txtDBName
    
    CountQueries = 0
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    
    db.Containers.Refresh
    
    Set tdQ = ThisDB.TableDefs!tblQueries
    Set rsQ = tdQ.OpenRecordset
    Set tdQF = ThisDB.TableDefs!tblQueryFields
    Set rsQF = tdQF.OpenRecordset

    ' Enumerate QueryDefs collection.
    For Each qryLoop In db.QueryDefs
        ' Enumerate Fields collection of each
        ' QueryDef object.
        If Left(qryLoop.Name, 2) = "zz" Or Left(qryLoop.Name, 2) = "xx" Then     'don't ignore leading ~ since those are embedded queries
        Else
            CountQueries = CountQueries + 1
            Forms!frmPrintDoc!TxtQueryCount = CountQueries
            Forms!frmPrintDoc!txtQueryName = qryLoop.Name
            Forms!frmPrintDoc.Repaint
            rsQ.AddNew
                rsQ!SourceDBID = vSourceDBID
                rsQ!QueryName = qryLoop.Name
                rsQ!RecordsetType = qryLoop.Type
                rsQ!SQL = qryLoop.SQL
                rsQ!LastUpdateDT = qryLoop.LastUpdated
                rsQ!CreateDT = qryLoop.DateCreated
                SAVEQueryID = rsQ!QueryID
            rsQ.Update

            'Debug.Print qryLoop.Name
            'Debug.Print qryLoop.SQL
            For Each fldLoop In qryLoop.Fields
                rsQF.AddNew
                    rsQF!QueryID = SAVEQueryID
                    rsQF!FieldName = fldLoop.Name
                    rsQF!SourceField = fldLoop.SourceField
                    rsQF!SourceTable = fldLoop.SourceTable
                    rsQF!OrdinalPosition = fldLoop.OrdinalPosition
                    ''rsQF!RecordsetType = qryLoop.Type
                    ''rsQF!SQL = qryLoop.SQL
                    rsQF!AllowZeroLength = fldLoop.AllowZeroLength
                    rsQF!DefaultValue = fldLoop.DefaultValue
                    'rsQF!FieldSize = fldLoop.FieldSize
                    rsQF!Required = fldLoop.Required
                    rsQF!Type = fldLoop.Type
                    rsQF!ValidationRule = fldLoop.ValidationRule
                rsQF.Update
            Next fldLoop
        End If
    Next qryLoop

Exit_Create_tblQueryFields:
    db.Close
    Exit Sub

Err_Create_tblQueryFields:
    Select Case Err.Number
        Case 3043, 3055
            MsgBox "Please select a valid database. Error #" & Err.Number, vbOKOnly
        Case 91   ' db was not opened so it cannot be closed.
            Exit Sub
        Case 3251
            Debug.Print "      --PPL_Value ------- Error"
            Resume Next
            Resume
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
    Resume Exit_Create_tblQueryFields
    Resume
End Sub

 

[Mark_]

No need to export. SQL view in Access has provided Find and Replace functionality for several years in both the standard SQL editor . . .

View attachment 119211

and now in Monaco . . .
View attachment 119212

In addition, you can use the database documenter.

Definitely can, but I was trying to figure out an easy way for OP to have a list of the fields.

In word, OP would be able to end up with "These are the fields I need to index" and then go in and make the changes. ACCESS doesn't like if you try to open the same application twice to do this.

 

[Pat Hartman]

Definitely can, but I was trying to figure out an easy way for OP to have a list of the fields.

Check out the code I posted. The OP will need to create a form with a couple of specific fields in order to use it.