Database is TOO slow

[Badnapp]

Good morning,

I have created a small database for law clerks that I manage to input and track their weekly tasks.

On the advice of others, I split the database into a front and back end, storing the back end on a shared server that we wall have access to, and distributing copies of the front end to all law clerks.

There are 26 law clerks in total, and they are in different offices across the State.

Two of the comboboxes in the database are cascading, and whenever the user tries to make a selection, it takes minutes. In general, when they are using the comboboxes to create records the database is TOO SLOW. One of the clerks said that it takes her up to 5 minutes for her computer to unfreeze each time she clicks a combo box.

Any ideas what is going on here? I realize the problem is quite vague, but I have to start somewhere.

 

[CJ_London]

Just some thoughts

Are you saying that in all other respects the speed of the front end is OK? Also that all users are experiencing this problem- if so, it suggests the issue is with your network speeds.

Are your users hard wired to the network or using wireless? Again, wireless can be slow.

You could try changing the query behind the combo rowsources to snapshot rather than dynaset.

Is your combo rowsource efficient? is it bringing too much data through?

If the rowsources are fairly static, consider having a local table for the rowsources which is updated when the front end is opened

 

[David R]

What do the comboboxes do to 'create a case'? I agree that this is probably network problems, but if any of this uses domain functions (Dlookup, etc) that will slow you down immensely.

 

[Badnapp]

CJ_London:

1 - Yes, the front end is quick 'enough' for all users, with the exception of the cascading combo boxes. These lock up the users machine for a couple of minutes.

2 - All users are hardwired to the network. The back end is stored on the J:\ drive of this network and all users have been given access to the J:\ drive and distributed a front end which they store locally. One user commented "the J:\ drive is slow with everything"

3 - Will try changing the source to snapshot

4 - The combo boxes do not have a rowsource. They are populated by a lookup based on the previous combo box using this vba code.

Private Sub cboDivision_AfterUpdate()
On Error Resume Next
Select Case cboDivision.Value
Case "Civil"
cboSubcategory.RowSource = "CivilSubcategories"
Case "Criminal"
cboSubcategory.RowSource = "CriminalSubcategories"
Case "Divisional Court"
cboSubcategory.RowSource = "DivisionalCourtSubcategories"
Case "Family"
cboSubcategory.RowSource = "FamilySubcategories"
Case "Administrative"
cboSubcategory.RowSource = "AdminSubcategories"
Case "Chief Justice Office/Legal Research Facility"
cboSubcategory.RowSource = "CJOSubcategories"
End Select
End Sub

5 - I am just now realizing that a lot of the tables are static information. The only table that ever gets affected by the front end is "Tasks." Would the database run much more efficiently if I restructured the front ends so that the only linked table is "Tasks." By storing all of the static information in their front ends this will increase performance? I never thought of this until now. I'm an Access n00b.

David R:

Actually a good guess, I had thought of doing this initially. I circumvented the problem by having a "Log in" form with comboboxes that is hidden once the user selects their name, this is then referenced by a query on their task list to hide tasks from any user except the one who is logged on. So I don't believe that is the issue.

 

[David R]

That actually has 0% to do with what I was asking. Do you use anything like DLookup, DSum, Dcount, in whatever those comboboxes 'do' when a user tries to use them to create a record? Or are they just comboboxes ON the record and they are the first thing your users fill out in a new record? User terminology can be very imprecise sometimes.

It will help a smidge to load your static tables into the FE, but unless these tables hold thousands and thousands of lookup values, that's not the problem here. You'll also trade out the ability to quickly update anything in those tables (right now, once they close the form/database and reopen, it will draw the linked table again without having to download a whole new FE).

I don't know much about cascading combo boxes so I'll let somebody else address that.

 

[Badnapp]

My apologies, now I believe I am following you.

The comboboxes I'm referring to are comboboxes on a form used to create a record of a task.

ex. User logs in --> Access opens Task List form which shows all tasks done by that user

User needs to create a new task entry --> Clicks "New Task" Button --> Access opens New Task form which is a series of comboboxes tied to the headings of the Task List form

All of this data is saved in the back end in tables related to tasks.

The goal here is for 26 users who are all in different cities to be able to use forms to enter their tasks each week... the comboboxes are on the forms so that the users cannot accidentally spell something incorrectly - but they greatly slow down the process... I tested it after making the changes I mentioned and there was a noted improvement in speed. Basically there is only one combobox that is quite slow now. The combo box rowsource is a query sorting names alphabetically, the names are pulled from a spreadsheet that is stored in the back end. This is the only table aside from tasks that contains data which changes from time to time. All other tables are 99% static. (Once a year we get new clerks, so names would have to change at that point)

Any tips on improving the front end performance of a database split between 26 users in different regions? I don't have admin responsibility either so I can't modify much at their end.

 

[jdraw]

Please tell us more about the comboboxes based on this quote, and what exactly are these cboSubcategory.RowSource?

4 - The combo boxes [COLOR="Purple"][I]do not have a rowsource[/I][/COLOR]. They are [COLOR="Purple"][I]populated by a lookup[/I][/COLOR] based on the previous combo box using this vba code.

Private Sub cboDivision_AfterUpdate()
On Error Resume Next
Select Case cboDivision.Value
 Case "Civil"
    cboSubcategory.RowSource = "CivilSubcategories"
 Case "Criminal"
    cboSubcategory.RowSource = "CriminalSubcategories"
 Case "Divisional Court"
    cboSubcategory.RowSource = "DivisionalCourtSubcategories"
 Case "Family"
    cboSubcategory.RowSource = "FamilySubcategories"
 Case "Administrative"
    cboSubcategory.RowSource = "AdminSubcategories"
 Case "Chief Justice Office/Legal Research Facility"
    cboSubcategory.RowSource = "CJOSubcategories"
End Select
End Sub

 

[Badnapp]

I'm pretty terrible with VBA code, but as I understand it....

cboDivision is a combobox with rowsource "Division of Law" which is a table

The table is simply six categories: civil, criminal, divisional court, family, admin, CJO office

All of these categories have their own corresponding table with subcategories (ie family - divorce, criminal - assault, admin - photocopying etc)

So my idea was to use that VBA code in the afterupdate event of the cboDivision box.

Thus, the form works as follows at the moment:

User selects a category from cboDivision --> cboSubcategory restricts possible choices based on the category selected in cboDivision.

I hope I explained that logically.

 

[jdraw]

Here are 2 videos on combo boxes -watch them, especially combo2 and let us know if that is basically what you have done.

http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

I want to ensure you are not usiing lookup fields in tables.

 

[Badnapp]

Yes, the second one is very similar to what I did. I remember coming across that tutorial while researching cascading comboboxes. However I didn't understand it at that time and I am using Access 2010 and got sketched out because he's using an older version, so I chose to use VBA code to build the relationships instead of building it through queries. I realize now I probably could have built the relationships more easily using queries.

 

[Badnapp]

Another difference between his second example and my database is that he is using a table as the location recordsource that has all of the possible values, where my subcategories are stored in 5 separate tables and the afterupdate event in my cboDivision box references one of those tables depending on your selection.

 

[jdraw]

Can you post a copy of your database (no confidential info) with enough data to highlight the problem. We don't need real names --Donald Duck, John Doe are fine. Best to put it in zip format.

 

[CJ_London]

Basically there is only one combobox that is quite slow now. The combo box rowsource is a query sorting names alphabetically, the names are pulled from a spreadsheet that is stored in the back end

Accessing a workbook across a network can be quite slow - can this not be held in a table and indexed on names?

 

[Badnapp]

Attached is a copy of the database in .zip format *before* I split it.

The two tables that are linked are "Tasks" and "Judges" and the slowest combo box is based on the "Judges" query.

 

[jdraw]

A few comments:
.I would recommend names without spaces embedded
.no relationships between tables
.why do you use DISTINCT in your queries

the slowest combo box is based on the "Judges" query.

I don't see a Judges query that has Judges and Task tables related
There is a query "JudgesNames"


Where is this combo box -- form name?

 

[Badnapp]

Re: Comments:

. I can remove spaces from all names if you think this would speed it up, but I think it's a different issue.

. I have no clue how to build relationships lol. I know that sounds ridiculous, but this is the first Access DB I made and I kind of fumbled through it. I'm assuming you just link the same names to each other?

. I used DISTINCT in my division of Law query because it is the source for a combobox that is on the "Task List" form which I use as a filter. ie there are thousands of tasks in the "Tasks" table. The user can use the combobox on the "Task List" form to filter all of the tasks displayed by division of work (family, civil, criminal etc)

The slow combobox is on the form name "Task Details" and it is labelled WorkAssignedBy. The combo box is based on the query "JudgesNames." The idea being, the spreadsheet containing judges' names is an excel file with my headings, all I want extracted is the first and last name as one value which is why I used a query. Then the combobox displays all of the first and last names of judges via that query.

 

[jdraw]

I think most of your slowness issue is because of your database structure. You say this is your first database, so that explains the structure issues to some extent. Unfortunately it appears you have done what many "newbies" do---jump blindly into Access. There are many principles and concepts regarding data base design and relational database operation. Getting too involved with Access (or any DBMS software) without some familiarity with those design principles and concepts leads to less than optimal performance etc.

Do you run this in production mode?
What is dependent on this database as it is?
How did you test this database? Was it slow there too?
Do you have any specifications regarding the design/development of this database?

 

[Badnapp]

1. What is production mode? The back end is saved as a .accdb file stored on a shared drive. The front end is a .accde file distributed to the 26 clerks. I built the file using Access 2010 Professional.

2. 26 Clerks depend on the database to enter their daily tasks. One manager uses a copy of the front end to create reports.

3. I tested it over a three week period in a group of 5. I did nothing different with the testing group. They mentioned at times it was slow, but nothing disastrous.

4. I wanted to create a simple database to track tasks during the week. I have 26 clerks who used to fill out reports in microsoft word and then e-mail them to their manager, then it was up to the manager to compile the data, sort it, and subtotal/total it. In order to save him time, we want a system where the clerks input their weekly tasks using forms. The reason we would prefer forms is so that they do not make silly errors like spelling mistakes, and the format is uniform across clerks. Somebody pointed out to me that I can do all of that in Access, so I attempted to create this in my spare time. Everything works fine when the DB is used on a local drive, or in a small test group, but as soon as more than one person uses the front end simultaneously it all goes to hell. One thing that I can't wrap my head around is that I have a front end on my C:\ drive, and it NEVER lags, but the clerks that are using the front end have ALL said it lags drastically, sometimes up to 5 minutes when you click a combo box. I've done a small amount of research and gotten mixed opinions as to whether Access was even the correct application to run this amount of data in the first place. My friend in IT for example, has suggested transferring the 2 linked tables in the back end into an SQL database, and linking that to the front end instead of using my .accdb file for the back end, although I'm sure it's just another can of worms. I greatly enjoyed working on this project, but admittedly I probably made many errors that didn't expose themselves until all 26 users were trying to use it.

 

[jdraw]

Thanks for the update. I think you've done extremely well as a first database.
Production mode is when you run this in real operations of your organization. This is different from Test or Acceptance To Production or Maintenance. So this is the real McCoy so to speak -work depends on it.

Since you have created an accde and distributed it to all 26 users, and they all have their own copy on their own PC, I hope you have a back up of your accdb. There is no code in the accde and you can't simply convert it to an accdb. You back that accdb up like gold. That's your only vehicle to make improvements/adjustments.

I don't know your business well enough to comment on how well the database design supports your requirement. I never build a database without a data model. And for all but the most trivial I build test data and try to stump the model. Once I'm happy that the model supports the various "business scenarios", we move to development.

As for Access being appropriate for this sort of application, I would say very much so. You will read this time and again --when developing a database make sure your tables and relationships support your business. If these aren't correct, you'll spend a lot of time building workarounds to solve a structure problem.

Things often work well when
-the database is all on one machine
-there are only 1-2 users doing testing.

Some things that cause slowness are
-non-normalized tables
-lack of primary keys
-improper indexing
-convoluted code
-network hiccups in split database
-getting all records rather than a specific record when only 1 is needed
- etc.

Moving to SQL Server is an option but it is not the root cause of the current slowness in my view. My analogy here is - it's a bit like buying a new car because there's a plug wire loose on the current one.

If most tables are static,they could be put in the FE, but they're smallm and I don't think that is the issue.

One thing I used to do was include some logging (either as Debug.Print statements or logging to a file or table). During development I'd include a log statement for each procedure or form names as a log entry, same with various events. I could review the logs and see the process flow. You could do something similar and check times as well.

I created 2 test Tasks for Jack Dempsey, no delay but I didn't expect one, but the Tasks did not get a TaskID??? see jpg
I altered the Rowsource of WorkAssignedBy to

sql against the Judges table directly.
SELECT DISTINCT [NAME] & " " & [First] AS Judges
FROM Judges;

[no need to make a query then use that query as rowsource]


I don't really know what more to advise you here. Others like pr2-Eugin or CJ_London may be able to decipher your code and spot something basic.

I'm going to suggest this link on database design.. You will learn lots with this.

And this one on Performance of Access databases.

Good luck.

 

[gemma-the-husky]

you mentioned the db was distributed "across the state".

general performance over a WAN is likely to be poor. you may be able to improve it with careful system design.

the other thing, is are you maintaining a persistent connection - because that will cause speed issues as well, even over a LAN