optimize a form with query by form with lots of criteria

[kballing]

I have a rather complicated DB that is running incredibbly slow. Access is not in my job description so I'm asking for advice. Pardon the rather lengthy description.

DB Info:
I have a DB for screening patients for a clinical trial. Essentially there are two tables, one for patient demographics (name, DOB, etc.) and one for data regarding their eligibility (criteria1, criteria2, etc.). Lets call the tables TableA and TableB and they are linked by a unique patientID.

The main form has three parts:
1. A bunch of controls to filter the list of patients. The form's record source is a query that uses these controls as filter criteria. Also there are some buttons as the column headings for the listbox that when clicked append the appropriate 'Order By' statement to the form and listbox record source.

2. A large listbox with the row source the same as the query used for the form's record source. There is VBA code behind it to sync the selected list item to the current record and vice versa and requery both the form and listbox.

3. A subform for entering screening data and displaying patient details.

There are about 35k records in both TableA and TableB.
The main form is bound to a query joining both TableA and TableB since I need to filter records by patient demographics and screening info.
The subform is bound to a different query with a TableA, TableB join, but could probablly be based solely on TableB.
The forms are linked by PatientID.

Loading the form takes a long time, sorting the form takes a long time. Filtering the form takes a long time. Moving through records works great.

This program is going to be used by many users over the network simultaneously so I set up replication. In the replica, the form stalls terribly and seems like it is trying to sync data or update something, making loading/sorting/filter nearly impossible.

What am I doing wrong? How can I improve this without losing functionality?

 

[kballing]

More Details:

Query FilterPatients - This is the recordsource for the form

Select TableA.*, TableB.criteria1, TableB.criteria2 . . .
From TableA Inner Join TableB on TableA.PatientID = TableB.PatientID
Where Name like Forms!MainForm.NameFilter AND AGE < Forms!MainForm.AgeMax . . .;

Query ScreeningInfo - This is the recordsource for the subform

Select TableB.*, TableA.Name, TableA.AGE . . .
From TableB Inner Join TableA on TableB.PatientID = TableA.PatientID;

Some VBA

'onload
me.form.RecordSource = FilterPatients;
me.listbox.Rowsource = me.form.RecordSource
me.form.requery
me.listbox.requery
 
'when a sort button is clicked
'some code to generate an OrderByString
me.form.RecordSource = "Select * From FilterPatients " & OrderByString &";"
me.listbox.Rowsource = me.form.RecordSource
me.form.requery
me.listbox.requery
 
'when filter criteria is entered
me.form.requery
me.listbox.requery
me.listbox.requery

 

[dfenton]

Replication should NEVER be used as a solution to performance problems. That's not what it's for, and you're just moving the problem without actually solving. You've also mis-identified the problem -- replication might be a solution when the problem is contention for locks on the data file, but in the case you describe, the problem is actually in inefficient design of the application UI. Multiplying the number of back ends by using replication will not resolve the underlying issue at all.

Get rid of the replication and fix the real problem.

 

[kballing]

Yes, I understand that replication is a solution for resolving data lock conflicts. However, because of the fact that many users will be simultaneously using this program, I have chosen to do this.
In no way did I think this would speed things up. I was just trying to mention that it indeed made it worse.
Whatever solution I come up with has to take into account that the final database will be replicated.
Yes, the problem is inefficient design. That is where I'm asking for advice.

However, it just occured to me that I haven't indexed my tables or set up relationships at all. That should surely help.

 

[dfenton]

Yes, I understand that replication is a solution for resolving data lock conflicts.

I hope you mean NOT a solution, since it's not.

However, because of the fact that many users will be simultaneously using this program, I have chosen to do this.

There is no necessity of using replication just because you have simultaneous users.

Is the app split into front end (forms/reports/queries/etc.) and back end (data tables only)? If not, that may be one of the things making you think you need replication -- many people believe MS when they suggest that distributing application updates is a good use of replication.

But it's *not* a good use of replication.

Indeed, it's one of the worse things you can do, because Jet replication does not work reliably with anything other than pure Jet objects (tables and queries). In fact, replicating a front end can lead to problems that eventually result in corruption and eventual loss of the entire VBA project.

But the worst part of this is that it's not necessary in the first place. No multi-user Access app should be distributed in an unsplit setup. That means there's one back end file with data tables on a file server, shared by everybody, but each end user gets a copy of the front end (forms/reports/etc), which has links to the shared data file. This means you can work on the front end and edit it to your heart's content because nobody has it open but you. And distributing updates to the front end is as easy as replacing each users' existing front end.

In no way did I think this would speed things up. I was just trying to mention that it indeed made it worse.
Whatever solution I come up with has to take into account that the final database will be replicated.

Given what I've said above, are you still sure you need replication?

Yes, the problem is inefficient design. That is where I'm asking for advice.

However, it just occured to me that I haven't indexed my tables or set up relationships at all. That should surely help.

Lack of indexes is surely a major cause of problems. And lack of indexes is in indication of either extreme inexperience or outright incompetence on the part of the original developer. That kind of colossal error indicates to me that there are likely to be significant design errors throughout the application and that there won't be any simple fix to the performance problems.

 

[SOS]

And just to add one more voice of sanity - I agree with what David said. Your situation does NOT warrant the use of Replication, and it can only lead to problems.

 

[Simon_MT]

You need to de-construct your database. 35000 records are not significant. Are you using a FE/BE solution? I have found even without filtering, accessing records is not a major issue, although using criteria on the call to open the Form is fast.

If anyone catches me trying to replicate a database they have my permission to give me a jolly good slap.

Simon

 

[jdraw]

Yes, I understand that replication is a solution for resolving data lock conflicts. However, because of the fact that many users will be simultaneously using this program, I have chosen to do this.
In no way did I think this would speed things up. I was just trying to mention that it indeed made it worse.
Whatever solution I come up with has to take into account that the final database will be replicated.
Yes, the problem is inefficient design. That is where I'm asking for advice.

However, it just occured to me that I haven't indexed my tables or set up relationships at all. That should surely help.

I agree fully with the others offering help. However, I have not seen your answer as to whether or not you have a FE/BE set up. That, along with some indexes, is a major first step.

 

[kballing]

I think I've hit on a sensitive subject.

Ok, I know this thread has diverged a bit, so let me explain my situation. I understand about setting up a FE/BE database. The actual programmer in my departement, who is always 'too busy' working from home to help me, insists that replication be used. Perhaps that is why I am confused as to why.

So, just considering the front end. Suppose I have my front end database with forms and reports, etc. in a sigle location on the network. This file links to the backend tables in a file in a nearby folder. Will this cause problems if multiple users open the same FE file and start making changes to the BE data? (ideally, they won't be editing the same records at once, but it is possible).

As far as speed goes, I'm going through all my forms and queries, etc. and doing my best to optimize them. I've found quite a few poorly written things. I've also set up relationships and feel good about the final organization of my tables.

Anyway, my wife is calling me and it's Friday afternoon. I'll get back on this Monday. In the meantime, I'd love to get some more feedback.

 

[Simon_MT]

I would go to your department head and suggest that your programmer wouldn't know sh*t from clay even if he licked it!

When you get on Monday we can give pointers in the right direction.

Have a nice weekend.

Simon

 

[dfenton]

Splitting and then sharing the front end is almost a waste of time. It gets you almost none of the benefits of splitting, while retaining almost all of the problems of running unsplit.

Each user should have an individual copy of the front end.

Updates to the front end should never be distributed via replication -- doing so is a mis-use of replication guaranteed to eventually fail.

There are a dozen different ways to distribute front-end updates. If that's the stumbling block keeping the front end replicated, then we can offer many suggestions as to how to solve the problem without replication.

By the way, just so you know, I'm not anti-replication (see http://dfenton.com/DFA/Replication/ for evidence of that). It's a great technology when used properly. It doesn't sound to me like it's being used for any good purpose in your situation.