Efficient Access form response time, when bound to a linked SQL Server table

MS Access 2013 Front End, SQL Server 2014 Developer backend.

I've been developing in Access for over a decade but now I'm working on my first Access app with a SQL Server backend.  When I started the project I asked for suggestions on EE about using an Access FE and a SQL BE.  I was under the impression that all data retrieval and storage should be done thru stored procedures.

Many EEer's disagreed with that and indicated that bound forms were perfectly acceptable and would perform well..  The forms are bound to SQL tables that are linked to the Access FE.

I developed the project using forms bound to an Access table, linked to SQL Server.  

All my testing to this point is on a single user non-networked machine so the response should be as best as can be expected.  

Initially when my data test set table sizes were in the hundreds of thousands of records range, the forms performed well.  However, I recently converted the entire DB and now the backend tables have as many as 25 million records.

Once the full data set was in place the response time slowed down considerably.  Some forms take 30 seconds to load a single record on a stand alone machine.

I began 'playing' with the forms to see if I could improve the performance and came up with a few items that helped reduce the load time of a form.

These comments only relate to the load time (the time it takes to present the one record requested on the screen) for a form that only works with a single record from the linked table at a time.  Records may be added, revised or deleted.  The load time only applies to records being edited or deleted.

1. Subforms can really slow down the loading of the form.  Even if the recordset of the main form is a specific record in the bound table.

2. If a record ID has been passed in OpenArgs, Any bound forms should go directly to the record to be edited rather than loading the entire table.  In all cases the ID passed in 'Openargs' is the primary key of the bound table.

In the Form load event
'
If IsNull(Me.OpenArgs) Then
Else
    Me.RecordSource = " Select Top 1 * From " & Me.RecordSource & " Where [MuniMastID] = " & Me.OpenArgs
End If

3. The ‘Top1’ clause also helps when only one record should be returned.

Am I on the right track?  Are there any other suggestions from experienced EEer's to help speed up Access forms bound to linked  SQL tables?







As the database got bigger.
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If you are passing the ID in the OpenArg, where are you using it in the form?  If it is after the form loads, Access has ALREADY populated the recordset based on the RecordSource.

A more efficient method is to use the Where argument of the OpenForm method or what I do is to have the RecordSource query reference a combo on the main form.  When the form opens, the combo is empty and so the form loads empty.  Then in the open event, I populate the combo with the ID from the OpenArgs and Requery the form.  This allows the form to be used two ways - by another form that passes in a specific record or by using the combo on the form to search for other records.

Make sure that the table is properly indexed and that your query isn't using some VBA or UDF function or join that is causing Access to request the entire table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thanks Pat.

The logic

'
If IsNull(Me.OpenArgs) Then
Else
    Me.RecordSource = " Select Top 1 * From " & Me.RecordSource & " Where [MuniMastID] = " & Me.OpenArgs
End If

Open in new window


Is in the form load event.

Please verify that Access will have already have populated the recordset prior to encountering this statement.

If so, that sure explains a lot of the delay.

Assuming the recordset is already loaded prior to my logic, could you explain the
what I do is to have the RecordSource query reference a combo on the main form.  When the form opens, the combo is empty and so the form loads empty.  Then in the open event, I populate the combo with the ID from the OpenArgs and Requery the form.  This allows the form to be used two ways - by another form that passes in a specific record or by using the combo on the form to search for other records.

When you create the form it has to be bound to the linked SQL table, right?

I'm not clear on how your empty, then populate, combo box would work.

Could you please give a more specific example?
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

In your case, I'm not sure why adding Top 1 will help if your Where clause will return only one record anyway.

Pat's right that the WhereCondition argument for OpenForm and OpenReport is an efficient way to go.  Also, some developers leave the RecordSource completely empty and populate it after the form loads, but I don't like that approach, for two reasons:  1. It makes the form harder to design if you have to keep adding the recordsource, and 2. I don't like SQL statements living in code, because that leads to hard-to-diagnose errors.  They should live in their "native habitats": saved queries or query properties.

You should know that forms and reports evaluate the recordsource even before the Open event, and that can take time.  Before your form or report closes, add WHERE 1=0 to its recordsource.  Then, the *next* time it opens, it will open much quicker, then in your open event you can set the Where clause to whatever you like.

Speaking of replacing Where clauses, we've written code to automatically find and replace the Where clause in a SQL statement.  It's a free download called "J Street SQL Tools" on our free J Street Downloads page at www.JStreetTech.com/downloads.

Take the text and paste it into a new module called basJStreetSQLTools.

To use it, try the function ReplaceWhereClause.  You send in a whole SQL statement and the new desired Where clause, and it locates and snips out the old one, inserts your new one, and gives you back the new statement.  If you send in a null or empty Where clause, the function just removes any existing one from the statement.

Rebuilding a whole SQL Statement in code is laborious and often leads to hard-to-diagnose errors.  Our replacement technique allows you to store the whole query structure in a property or saved query without having to rebuild it each time in code.  All you have to do is rebuild the Where clause.  It's one of the most useful functions we've ever written - it's used in every single Access application we build.  It works with both Access and SQL Server query syntax, so it works fine on passthrough queries too.

By the way, there's also a ReplaceOrderByClause function that does the same for sorting.

Cheers,
Armen
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
On most of my forms, the where clause refers to one or more combos in the form's header.  Since those are empty when the form loads, it loads without any records.  when the criteria is populated, the form is requeried to run the query again.
0
mlcktmguyAuthor Commented:
Thanks Pat and Armen:  Great techniques that have improved the response significantly.

1. In my testing, the only way the 'Where' clause on the open form works is if the recordsource of the called form is left to be the entire table.  For example recordsource: 'tblMuni_Master'.  
When I changed the recordsource in the saved form to 'Select * from tblMuni_Master Where 1 = 0' and then open the form using a 'where' clause the record is not found and the form opens blank.
Is that correct or am I doing something wrong?

2. Do you agree that subforms are a bad idea with the SQL backend or can these techniques be expanded to the subform?

Thanks again for your help.
0
PatHartmanCommented:
1. I think Armen must be replacing the entire RecordSource if what he has is hard-coded that way.  My RecordSource doesn't vary although I do have situations where I replace the entire RecordSource also.  If you are going to ALWAYS pass in the where argument using the OpenForm method, then leaving the RecordSource unqualified is fine.  I prefer to allow the user to stay on the form and refilter and that is why I use the WHERE argument that references one or more textboxes/combos in the form's header.  In reality, you need to determine how the user wants to interact with the form.  Do they always want to close the form and go back to the previous form or do they want to stay on the form and look for additional records.  In my apps, I use both methods.  The WHERE argument of OpenForm is always used when formA opens FormB for drill down.  For main forms, I sometimes make a complex search form.  The search form opens the main form directly if the search returns only a single record but if the search returns multiple records, a list style form is opened.  Then clicking on an item in the list form opens the main form to a single record.  In cases where the search is only one or two controls, I use the WHERE clause of the RecordSource to refer to form fields.

There is no single solution but there are several that will solve the problem. Choose the best for each situation.

2. You can't really avoid using subforms and I wouldn't go out of my way to do that.  I have a couple of tabbed forms that have a dozen zubforms.  With that many, I don't load the subform control until the tab gets the focus.  This minimizes the load time for the form.

One thing that can help with form loading is if you are using an ODBC BE, use pass through queries for the RowSources of combos and listboxes.  These are faster and since the RowSources don't get updated on the fly, this doesn't present a problem.
0
mlcktmguyAuthor Commented:
Thanks Pat, another good explanation but leads to a followup.

When you say
I don't load the subform control until the tab gets the focus.  This minimizes the load time for the form

How do you not load the subform control until the tab gets focus?
0
PatHartmanCommented:
I leave the ControlSource for the subform control empty.  Then set it to the proper subformname when the tab gets the focus.
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
We replace the Where clause when the form or report opens (not the whole RecordSource).  That gets rid of the Where 1=0.  However, the Where 1=0 technique isn't compatible with opening a form with a WhereCondition, because that just adds on another Where clause.  I don't think we've tried clearing the Where 1=0 in the Open event, and seeing if the WhereCondition is applied properly - that would be another approach if it works.

Again, all of these Where clause shenanigans are easy using our SQL Tools as I noted above.

I agree with Pat, there's no reason to avoid subforms and subreports - they're some of the best things about Access.  However, passthrough queries don't work with them.  If you have complex joins in a subform, you can base it on a linked SQL Server view instead of an Access query - we've found that can significantly improve performance.
0
mlcktmguyAuthor Commented:
Thanks to you both. This is very useful information and I'll be revising some of my other applications with accdb backends to take advantage of these techniques.

Pat: a Follow up to:
I leave the ControlSource for the subform control empty.  Then set it to the proper subformname when the tab gets the focus.

Do you mean you that when creating the main form you  put a subform contol on the tab but don't assign an actual subform to it?

If that's the case wouldn't you also have to define the parent child relationship when you set the subform name?

I'm sure it's much more simple than I'm envisioning but if you could explain in a bit more detail I hope to understand the technique since it sounds so promising.
0
PatHartmanCommented:
When I'm designing, I leave everything bound so I can check to see how everything lines up.  Once the form looks correct, I remove the ControlSource and populate it when the tab gains focus.

Technically, the parent child relationship is between the main form and the subform CONTROL.  It isn't directly to the subform.  I just checked and removing the SourceObject does not remove the master/child links.  To put it back:

    Me.sfrmTableFields.SourceObject = "sfrmTableFields"

Open in new window

0
mlcktmguyAuthor Commented:
Great information and many thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.