Just one little Index

[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.

 

[Shimon]

Hi,
I tried to refactor the DB so that the main table names and columns would be in English, so that I can show a screenshot. The First attempt took about 3 hours and failed.

The second attempt took about two hours and failed on a stupid calculated field. I say stupid, as in my humble opinion, in most cases there shouldn't be such a field. It should be in a query the "wraps" the table.

I cannot charge for the hours spent, and I realize that refactoring is probably not a good business decision, even if it would make life much easier for me.
I probably should search the Forum on the subject of Refactoring and then post my dilemma in reference to my specific application.

 

[Pat Hartman]

I guess I'm not sure why using one of the find and replace addins didn't rename all the fields. Going from one language to another is not something I've ever tried with one of these tools.

You can't effectively use NameAutoCorrect (the built in feature) because it is very limited in what objects it will propagate name changes to.

 

[Isaac]

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.

Or loop through all the project's querydefs and if instr(qd.sql), then debug.print

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

What did you mean by if you don't use querydefs?

 

[Pat Hartman]

What did you mean by if you don't use querydefs?

Some people prefer to use embedded SQL for everything because they've always done it that way and they consider the QBE to be inferior and not worth using for anything.

So, if all your queries were variables in VBA, you have no object model to help you. If you wanted to find out how many places you used tblA or fldX, that is simple enough with a text search but if what you want is a report that shows all the places all fields are used, then you can probably do it but the VBA would be pretty complex and probably pretty slow since you have to parse all the VBA code procedures and figure out which part of each was actually SQL. This would be easier if you were anal about naming conventions so your parsing code could use specific variable names to find the start of an SQL string. Whereas, if the vast majority of your queries were querydefs, you can use simple queries to answer the question in a few minutes.

There are a couple of situations where I use embedded SQL but they are few and far between. Mostly I use embedded SQL when I am creating a search form. When a query has a fixed select list and fixed selection criteria where only the actual value of the parameter changes at runtime, I hold my nose and use the QBE because of the other things it does for me.

 

[Isaac]

Some people prefer to use embedded SQL for everything because they've always done it that way and they consider the QBE to be inferior and not worth using for anything.

So, if all your queries were variables in VBA, you have no object model to help you. If you wanted to find out how many places you used tblA or fldX, that is simple enough with a text search but if what you want is a report that shows all the places all fields are used, then you can probably do it but the VBA would be pretty complex and probably pretty slow since you have to parse all the VBA code procedures and figure out which part of each was actually SQL. This would be easier if you were anal about naming conventions so your parsing code could use specific variable names to find the start of an SQL string. Whereas, if the vast majority of your queries were querydefs, you can use simple queries to answer the question in a few minutes.

There are a couple of situations where I use embedded SQL but they are few and far between. Mostly I use embedded SQL when I am creating a search form. When a query has a fixed select list and fixed selection criteria where only the actual value of the parameter changes at runtime, I hold my nose and use the QBE because of the other things it does for me.

That makes sense on what you meant when you said using query defs. At first I thought well who cannot use them? but you're right a person could do everything by VBA, damn that would be hard!

 

[Pat Hartman]

damn that would be hard!

When I first started using a relational database (DB2) back in the 80's, the only option was embedded SQL. There was a preprocessing step for the compiler that extracted the SQL strings (I don't remember how they were identified) and evaluated them separately. Then if that succeeded, the compile and link steps ran to complete the process. I used to dream about a tool that could remove the tedium of typing (and not making typos) all those SQL strings and testing them outside of the program to minimize the number of compile steps. Remember, those were still the days of batch compilation and so it could be hours between the time you submitted your "deck" for compilation and you got the executable back so you could test. Programmers today have no clue how long development used to take before instantaneous compiles.

There are lots of things not to like about the QBE but Monaco makes it a little better. The people who insist on embedded SQL are obsessed with formatting the string and how pretty it looks in code rather than the shortcuts and other benefits of the QBE once you get past its limitations.

 

[isladogs]

This is an extract from a lengthy article I wrote back in 2019: Query vs SQL vs Query Def

Like many developers, when I started using Access some 20 years ago, I used queries and macros before moving across to using SQL statements in code.

When I made the change, I was informed how much more efficient it was to run SQL in code.
At some point after that, I read that was nonsense and that queries run faster because they are pre-optimised.

And, just to complicate things further, others said using query definitions was better than either of those.
Those expressing opinions for each view included experienced developers, some of whom were MVPs

Faced with this disparity, I long ago decided to continue mainly using what worked best for me – SQL statements in code though others will have their own preferences.

As the article makes clear, there is no demonstrably 'best approach' and in the end, it comes down to personal preference.
Speed differences between the various approaches are usually negligible despite the benefits of QEPs and Rushmore.

These days I mainly use embedded SQL in VBA but will often use the QBE to assist with constructing more complex SQL.
However, the QBE does have its limitations and annoyances such as excessive use of parentheses and inclusion of superfluous fields.

Of course, there are plenty of occasions where SQL has to be used e.g. , union, DDL & passthrough queries as well as non-equi joins.

P.S. Monaco will soon provide even prettier SQL

Monaco SQL Editor Improvements Coming Soon

This short article outlines three significant improvements to the Monaco SQL Editor recently announced by the Access team and which will be released in the coming months

www.isladogs.co.uk

 

[Isaac]

This is an extract from a lengthy article I wrote back in 2019: Query vs SQL vs Query Def



As the article makes clear, there is no demonstrably 'best approach' and in the end, it comes down to personal preference.
Speed differences between the various approaches are usually negligible despite the benefits of QEPs and Rushmore.

These days I mainly use embedded SQL in VBA but will often use the QBE to assist with constructing more complex SQL.
However, the QBE does have its limitations and annoyances such as excessive use of parentheses and inclusion of superfluous fields.

Of course, there are plenty of occasions where SQL has to be used e.g. , union, DDL & passthrough queries as well as non-equi joins.

P.S. Monaco will soon provide even prettier SQL

Monaco SQL Editor Improvements Coming Soon

This short article outlines three significant improvements to the Monaco SQL Editor recently announced by the Access team and which will be released in the coming months

www.isladogs.co.uk

Nice summary of the situation, I agree. Someday I'll have to get back to doing some Access development - it's been a couple years since I did any serious dev in Access--and test out this Monaco new thing! Sounds like a big improvement.

PS ... the benefits of learning to write SQL from scratch, which is doubtless a sure byproduct of writing it in VBA, are not to be underestimated either. They xfer from thing to thing in career - very positive thing