Solved

MS Access Split Database Runs Slow

Posted on 2013-10-25
10
339 Views
Last Modified: 2013-10-26
Hi,
I have a Access 2007 (Office 2010) database which I recently split. The back end is located on a server in a shared folder. The front end is located on each end user's local drive and is synchronized with a master copy each time the database is opened.

The database has a  main member's form (frmMembers). It is bound the the main table (tblMembers). On the main form is a command button that opens a custom search dialog. This dialog has an option group to force the user to select a search criteria (phones, names, addresses, etc.). The user chooses a search (usually phones) and begins typing in the search edit box. As soon as the user begins typing, a list of matching criteria begins to appear in the search results window. The more information they type, the more the search is narrowed.

This is where it has become "painfully" slow. I suspect that the problem is the way the database is locking the back end? I've tried creating a simple, one-record table and binding it with a hidden form, that runs hidden the entire time the database is open, but no luck.

I have attached the custom dialog code, so that possibly, one of ya'll could take a look and come up with a more efficient solution? Create a recordset and search that versus the main table? Please help. The client is driving me crazy!
Document.txt
0
Comment
Question by:waverobber
  • 5
  • 4
10 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 39601436
Are all the fields you are searching indexed in the table?  If not, try adding an index on the fields.
0
 

Author Comment

by:waverobber
ID: 39601587
I assume you're referring to the individual fields (most text) in the tables being searched? If so, do you suggest changing index property from "no" to "yes(allow duplicates)? "
0
 
LVL 21
ID: 39601611
Yes. That is correct.

As a test, add one index to a field that try the search for this field.  If it helps, then index additional fields.
0
 
LVL 57
ID: 39601657
<<The user chooses a search (usually phones) and begins typing in the search edit box. As soon as the user begins typing, a list of matching criteria begins to appear in the search results window.>>

  How is the search actually being performed?  And how is the result displayed?

Jim.
0
 
LVL 21
ID: 39601734
Jim,

This was in the attached text file:

Private Sub edSearchString_Change()
    
    Dim searchString As Variant
    Dim strSQL As String
    
    searchString = edSearchString.Text
    
    Select Case ogSearchType
        
        Case 1:                 'Phone numbers
            strSQL = "SELECT DISTINCTROW tblMembers.MemberId, tblMembers.Cell1, tblMembers.Cell2, tblMembers.HomePhone, tblMembers.WorkPhone1, tblMembers.FirstName1, tblMembers.LastName1, tblMembers.FirstName2, tblMembers.LastName2 FROM tblMembers "
            strSQL = strSQL & "WHERE ((tblMembers.Cell1) Like '" & searchString & "*') "
            strSQL = strSQL & "OR ((tblMembers.Cell2) Like '" & searchString & "*') "
            strSQL = strSQL & "OR ((tblMembers.HomePhone) Like '" & searchString & "*') "
            strSQL = strSQL & "OR ((tblMembers.WorkPhone1) Like '" & searchString & "*') "
            strSQL = strSQL & "ORDER BY tblMembers.Cell1, tblMembers.Cell2, tblMembers.HomePhone, tblMembers.WorkPhone1"
            lstSearchResults.ColumnCount = 9
            lstSearchResults.ColumnWidths = ".38 in;.7 in;.7 in;.7 in;.7 in;.85 in;1 in;.85 in;1 in"
            
        Case 2:                 'Member Id
            strSQL = "SELECT DISTINCTROW tblMembers.MemberId, tblMembers.FirstName1, tblMembers.LastName1, tblMembers.FirstName2, tblMembers.LastName2 FROM tblMembers "
            strSQL = strSQL & "WHERE ((tblMembers.MemberId) Like '" & searchString & "*') "
            strSQL = strSQL & "ORDER BY tblMembers.MemberId"
            lstSearchResults.ColumnCount = 5
            lstSearchResults.ColumnWidths = ".38 in;.85 in;1 in;.85 in;1 in"
            
        Case 3:                 'Last names
            strSQL = "SELECT DISTINCTROW tblMembers.MemberId, tblMembers.LastName1, tblMembers.FirstName1, tblMembers.LastName2, tblMembers.FirstName2 FROM tblMembers "
            strSQL = strSQL & "WHERE ((tblMembers.LastName1) Like '" & searchString & "*') "
            strSQL = strSQL & "OR ((tblMembers.LastName2) Like '" & searchString & "*') "
            strSQL = strSQL & "ORDER BY tblMembers.LastName1, tblMembers.FirstName1, tblMembers.LastName2, tblMembers.FirstName2"
            lstSearchResults.ColumnCount = 5
            lstSearchResults.ColumnWidths = ".38 in;1 in;.85 in;1 in;.85 in"
        
        Case 4:                 'First names
            'etc, etc.
    
    End Select
    
    lstSearchResults.RowSource = strSQL
    lstSearchResults.Requery
    Me!edSearchString.SetFocus

End Sub

Private Sub ogSearchType_Click()
    
    Select Case ogSearchType
        
        Case 1: lblSearchString.Caption = "Enter member's phone number"
        Case 2: lblSearchString.Caption = "Enter member's Id"
        Case 3: lblSearchString.Caption = "Enter member's last name"
        Case 4: lblSearchString.Caption = "Enter member's first name"
        

    End Select
    
    edSearchString.SetFocus

End Sub

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:waverobber
ID: 39601767
Hi Jim,
I was responding to your question and got tied up on a call. Yes, the attached code is where the "slowness" occurrs. As you can see, I'm building a SQL string (each key press in the search text box generates another character in the statement) and comparing the typed text to the data in the table.

If for example, the user is searching by phone: they type 8.
The routine searches all phone fields (there are 4 of them: cell1, cell2, home, work) in the main table and displays all members whos phone (any of the four) begins with 8. User types 0, search for all members whos phone begins with "80," etc. I am displaying the results in a list box. The list box gets it data from the SQL statement built...

Therefore, with each key press, the resulting list box gets narrowed down to what the user is searching for. It works really well, but I'm concerned as to why it is sooooo slow? Keep in mind there are a minimum of 10 users accessing the same back end.

I am experimenting with indexing the search fields, but currently, I can only do that when the client is closed as it involves editing the back end tables...Any suggestions on improving the attached code is appreciated...
0
 
LVL 21
ID: 39601786
waverobber,

Indexing will help no mater what method you use.  Coding  or SQL can get around not having an index. Without an index every record must be read each time to find matches

If the fields are not indexed then you definitely want to limit the times you make a trip to the back end to retrieved data. This mean you will wait to search until the user has typed in all the search criteria.
0
 

Author Comment

by:waverobber
ID: 39601793
I was able to quickly edit the main table and change the phone fields (4 of them) index property from "no" to "yes, allow duplicates." It seems to be working, but I do not want to start doing back handsprings yet! So far so good. Thanks for the suggestion "HighTech..."

Is it optimal to index all four? Can I get by with just indexing the primary (cell1)?

How about the names? Currently, I am allowing the user to search by Lastname. However, there are Lastname1 and Lastname2 in the table. Should I index only Lastname1?

It is looking so far, that the points are heading your way. I would prefer to give it a day or so, because, after the initial split (no indexes other than primary key) were in use, the search routine worked fine. It was after a couple of days that it slowed. This is why I was headed toward the lock optimistic direction...Also, any suggestions on improving the code to make it faster/more efficient is appreciated.
0
 
LVL 21
ID: 39602216
oops I meant to say "Coding  or SQL can NOT get around not having an index."

My motto for an index on a field is: If you will regularly search or sort on it then it probably needs indexed

Is it optimal to index all four? Can I get by with just indexing the primary (cell1)?
You need to index every field you will be search if you want it faster.

If all the phone number as separate fields in the same record then you have repeating fields which violates the rules of data normalization. The design will require four index not one if it was better normalized. That would be having one phone number pr record. Which world require one one index.

One of the main things I do when optimizing someone's database for performance is to fix normalization issues. Next index fields that need it and remove any index that is not really  needed.  I find that properly normalizing the tables does help with performance.

How about the names? Currently, I am allowing the user to search by Lastname. However, there are Lastname1 and Lastname2 in the table. Should I index only Lastname1?
Yes, both last name fields will need indexed for better performance. This is another red flag there is design issues.  These normalization issues probably  have a negative impact on performance.

To help keep the indexed fields  optimized you will probably want to regular run compact on the back end. How often I run the compact is based on how fast records are getting added or field that have an indexed are edited. My clients that enter a lot of data daily will also compact daily.  Some get by with weekly.  If only a few records are added a week then you can probably get by with once a month or possible longer.
0
 

Author Closing Comment

by:waverobber
ID: 39603427
KISS is the best! I'm keeping an eye on things and getting end-user feedback. So far, so good! Thanks Bro...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 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

13 Experts available now in Live!

Get 1:1 Help Now