Speeding up record search

[Paul H]

I've been revising this app for some time now. I've gotten rid of a ton of objects that have been accruing for the last dozen year and tried to speed up some time consuming data entry procedures. One time consuming task involved updating an existing applicant record. This Applicant table is fairly large (160,000 records) and heavily indexed. Every time I think I've got it licked it comes back to haunt me. I started by only loading one record in the form when it opens. This cut 20 to 30 seconds off the load time. I then switched from searching the SSN column via FindRecord to setting the record source in VBA equal to a specific SSN.

strSQL2 = "Select * FROM Applicant WHERE SSN like '" & InputBox("Social Security #" & vbNewLine & vbNewLine & _
                                                                    "Use an asterisk (*) to show all.", "Search") & "'"
 
    Set rst2 = CurrentDb.OpenRecordset(strSQL2)
    
    With rst2
    
        If .EOF Then
            MsgBox "SSN not found.", vbExclamation
            Exit Sub
        Else
            Me.RecordSource = strSQL2
        End If
    
        .Close

This initially worked quite well. In most cases search time went from 30 seconds to 5 or 10 seconds. It would mysteriously take longer in a few cases, but overall performance was much faster. Since the user would have to run this search for each record they were updating, this made a big difference. Just today, after returning from vacation, my very understanding user mentioned to me the search was running slow again. After checking I see that we are very nearly back to square one or worse. When the new RecordSource is specified something odd is happening. Even though the search result shows up almost instantly, the program goes into a long wait. Important to note there is an OnCurrent procedure that is kicked off by this, but the OnCurrent procedure doesn't even start till after the 20 to 30 second wait. The OnCurrent procedure takes only a few seconds to complete. I can't figure out what the system is doing in the mean time since my record already shows on the form.I'm perplexed.

[ItsMe]

Why not provide an unbound textbox control with an input mask for the user input and pass the value of the control to a string variable. Then, in your SQL statement use
WHERE SSN = '" & strSSN & "'"

Other controls on the form can provide the user an option to retrieve all records. Also, do you really need SELECT *. Maybe you can define which columns to select. Another thing to consider is to change the SQL of a temp query object. You can use DAO QueryDefs to do this. After you define the SQL of the query, use the name of the query object in the form's Recordsource. The On Current of the form can determine if there are not any records using Recordset clone and RecordCount of the clone object.

EDIT: I also noticed that you are not destroying the Recordset Object.

With rst2
        If .EOF Then
            MsgBox "SSN not found.", vbExclamation
            Set rst2 = Nothing
            Exit Sub
        Else
            Me.RecordSource = strSQL2
        End If
    
        .Close
        
End With
Set rst2 = Nothing

[Paul H]

I started out by trying the text box and building a named query and using it as the RecordSource. Initially this did not work any better. Then something strange happened. I tried using the SQL created with the text box and the search and related procedures took a couple seconds. I then switched it back to the original and it also ran quickly. Really, the two SQL strings were identical, just created differently. Now either one usually performs well usually, but really there was no change to the procedure. Still when I passed this along to my colleague she continued to have poor performance. After going through the same steps on her computer (switching between scripts once or twice) she got the same fast performance, which is hard to understand. I really didn't make any changes. What I'm trying to do is figure out why sometimes this is lightning quick (1 to 2 seconds) and other times takes forever (30 seconds to a minute). For now I'll continue to play tricks with the code, but this strikes me as superstition. Something odd is going on in the background.

Using "Select * From" relieves me of naming the 30 something columns needed for my form. I don't see any downside to it.

Also using Like * or just like instead of "=" also doesn't seem to present any problems.

[ItsMe]

It might be a recordlock issue. Access might be placing locks on some fields. If the user is not editing the record, you can try using a Snapshot vs. a Dynaset. If you are using a Query Object, you can change the property of the Query Object. If you are assigning an SQL statement to the Form, you can set the property of the form to Snapshot and I would not use DAO to take a special trip to see if you have a matching SSN. Use the On Current event of the form. It should fire after you assign the new recordsource. In the On Current, create a RecordsetClone of the Form Object. Count the records of the RecordsetClone.

Since you are retrieving one or zero records, a snapshot will be faster.

The only other thing I can think at the moment is the user's computer is running out of memory. So make sure you declare all of your variables and destroy objects that are not in use.

[Paul H]

I made the change to Snapshot and I also see how my If Then statement did not destroy my object and fixed that. I'm not used to declaring recordset types or the advantages and/or disadvantages of the various types. Recordset Clones are new critters for me also. I'm going to tread lightly till I understand these better. This DB has made me superstitious. At the moment it's running better than ever (I say this as I cross myself and toss some salt over my shoulder). I don't want to tempt fate.

Thanks for your suggestions.

Paul

[orange]

Paul,

Tell us more about the database. Is it split --front end/back end. Who are the users?
Can you post a jpg of your relationships.

This is a concern

I've gotten rid of a ton of objects that have been accruing for the last dozen year and tried to speed up some time consuming data entry procedures. One time consuming task involved updating an existing applicant record.

[Paul H]

It is a split front end/back end database. Users are several admin people who have been tracking job applicants for criminal history and managing fingerprint checks and payments. It was designed some 15 years ago by someone who is long gone and has been "managed" by someone with limited Access skills. She actually did a remarkable job keeping it afloat all these year, but in the process created hundreds of queries, reports and temporary table, most of which I have removed and substituted form driven reports and parameter queries. I ignored the data entry portion until recently when i started receiving complaints about slowness (which pre-existed my changes). This particular form only updates a single, but large table. The relationships are a mess, but have been left as I found them. I'm tempted to make a copy and just get rid of all of them and see what happens.

[orange]

Paul,

I see BatchNumber in some tables. Does all data entry occur at the same time as searching/other use?
Besides data entry what are the other major uses of the dATABASE?

[HiTechCoach]

@Paul H,

Curious, how often do you run compact/repair on the back end. Daily after each backup or weekly after the backup?

[Paul H]

Does all data entry occur at the same time as searching/other use?

Sometimes. We just tried some experiments. I kicked everybody (one person) off the system and tried some searching after logging off myself.
Searching remained slow the first 10 times I tried, then all of a sudden, boom. Instant results. I had someone log on and kept having quick results, but she didn't. Mine was instant. Hers took about 15 seconds. Then it switched. She got instant results, mine didn't. We are using different, but identical front ends.


what are the other major uses of the database?

Several hundred letters (Word Mail Merge) and reports are printed each week.


The delay seems to happen the moment after I set the RecordSource of the form. While the desired record comes up instantly, there is up to and 60 second delay before moving to the OnCurrent event.
I can try setting the SQL of a query from VBA, then leave the RecordSource of the form set to the query, then requery my form as suggested by Its Me. I feel a bit like I'm chasing my tail.

I do feel like it must be related to multiple users. Since we are on different floors, I'm not always aware who is using the system.

[ItsMe]

...I do feel like it must be related to multiple users. Since we are on different floors, I'm not always aware who is using the system...

In addition to the question regarding Compact and Repair ...

The backend is Access? I suggest using a Default form that connects to the BE and remains connected while the Default form and Application are open. I will typically connect to small table that defines Users. When the application launches, retrieve a single record with two or three columns from a small table.

[HiTechCoach]

Also see this:

Significantly Improve the Performance of Microsoft Access Databases with Linked Tables

[orange]

Further to ItsMe's suggestion re persistent link, I'm going to give some links re Performance Tips.
You might want to see if any of these have been done, or perhaps should be considered.

http://www.fmsinc.com/microsoftacces...ddatabase.html
http://www.fmsinc.com/microsoftaccess/Performance.html
http://access-diva.com/blog/?p=66
http://www.devhut.net/2012/09/29/ms-...plit-database/

A good overview for consideration http://www.kallal.ca/wan/wans.html

And back to Boyd's question re Compact and Repair and Backup - how often do you do these?

As for Several hundred letters (Word Mail Merge) and reports are printed each week. Are these done in off hours/ or could they be?

You might want to identify the 5-10 things that are "slow and critical" and prioritize some review of these.

[Paul H]

Here's where we stand at present. I started to go the query def route and accidentally found that the query ran much fast using and "=" instead of a "Like". I tried that with the RecordSource method with excellent results. Further testing with two of us on the system worked just as well. We were unable to break the program. I'm perfectly happy to leave it at that. I created another control to retrieve all records using a simple SQL statement without an operator Select * From Applicant as an alternate RecordSource. These suggestions were originally proposed by Its Me.

I compact and repair the Front End frequently, but not the BACK END. I seem to remember some VBA code that will do that. Thanks for everyone's suggestions. I'll review the links as time allows. I feel more confident now the repairs will stick, which is a big relief. Having these record searches go smoothly will save hours of time and aggravation. I just learned today that this search vexation has gone on for years, so my users are thrilled. That's almost as good as a pay increase in my book.

Thanks again.

[ItsMe]

...so my users are thrilled....

Congrats. It's all about the Users.