Re: Need help in improving query performance in FE/BE Access DB

Bob Alston wrote:
Larry Linson wrote:
It is more likely that the complete _Index_ is moved across the network when you open the form (otherwise how would it know which records to retrieve in the initial "fetch"). Just as an experiment, set the Criteria to retrieve only one record (if that isn't the case, now) and compare the time. I try to avoid opening a form on a query of thousands of records in a network environment (whether split or client-server). Much of the time, I only need one record (if it exists) or none (if it does not) and can determine which, in advance.

As to list boxes only querying when "opened"... I am not sure what you mean. When you open the form, the List Boxes are populated so can you see the values -- how many records in an initial "fetch", I don't know. There can be a little hesitation when you scroll in the List Box, but that is not necessarily due to an initial fetch.

A good source of performance information for the multiuser environment (and avoiding corruption, too) is MVP Tony Toews' site, http://www.granite.ab.ca/accsmstr.htm. Another is former MVP Jeff Conrad's "Access Junkie" site, http://www.accessmvp.com/JConrad/accessjunkie.html.

Larry Linson
Microsoft Office Access MVP

"Bob Alston" <bobalston9@xxxxxxxxx> wrote in message news:6fwal.6961$1L3.126@xxxxxxxxxxxxxxx
Rick Brandt wrote:
On Sun, 11 Jan 2009 16:59:59 -0600, Bob Alston wrote:

I have a query defined as a record source for a form in a split DB
environment, about 9000 rows and about 100 columns. I select about 12
columns and has a criteria of one field equaling a constant.

The field in the criteria is indexed in the table.

The query accesses the table, not through another query.

When I run open the form it seems to take too long for the selected 240
rows or 9000 to be displayed. If I change the criteria and reset the
record source and requery the form, it is almost instantaneous. That
led me to believe that the entire table had been transferred on the
initial opening of the form.

What should I do to make it only transfer the 240 rows that match the
criteria?

Bob
It is not transferring the entire table. Do you have any ListBoxes, ComboBoxes or subforms? Those will also represent queries that have to run on startup that might not on a Requery.


Yes I also have some listboxes using the same table. but I thought those were only queried when opened.

And it sure feels like the entire table was pulled across.
The initial query time, based on the query time indicator at the lower left is about 8 seconds, which when I change the single selection criteria, I get results in about 1 second.

Bob


Thanks Larry. I also like to open only one record whenever possible.
This situation is to list active cases being managed by case managers.
From this list, a single client is selected and then detailed data on that one single client is displayed.

I agree it is likely b ring the index or at least portions of it. But I was surprised by how fast it was able to repaint the screen of a different selection.

bob
OK. found it. It was the combo boxes. When I removed them it worked slick. I incorrectly thought that the combo boxes were populated only when selected - WRONG.

I was using combo boxes to create selection options by status, program
and counselor - using select unique... I was scanning the entire database. Two of the three combo box fields was indexed and one was not.

Thanks for everyone's help.

Maybe I need some more sleep <grin>

bob

.

Relevant Pages

  • Re: Unexpected Enter Parameter Box
    ... I actually have a number of combo boxes and aside from the one that works, ... all other combos prompt me with one of two things. ... It asks for whatever criteria I have entered to be input again. ... My problem is only one of the selection criteria works, ...
    (microsoft.public.access.formscoding)
  • Add "All" to Combo Box and Use "All" as Criteria in Query
    ... add "All" as a selection to a combo box and then ... I use 3 different objects on a form as criteria for a query that is ... I have a simple form named, frmTeam, which has: 2 text boxes, 1 combo ...
    (comp.databases.ms-access)
  • Unexpected Enter Parameter Box
    ... I have created a Search form that uses combo boxes to select the various ... My problem is only one of the selection criteria works, ... 'Create Predicate ...
    (microsoft.public.access.formscoding)
  • Re: How? Linking a select all box to a query field criteria line
    ... >am linking the combo box range to the criteria line of my query. ... >will pick up the selection range from the combo boxes if it doesn't have the ...
    (microsoft.public.access.queries)
  • RE: Easy Drop Down Box Question
    ... Then in your query use something like this as the criteria for the state ... Private Function BuildWhereCondition(strControl As String) As String ... Dim strWhere As String ... Case Else 'Multiple Selection ...
    (microsoft.public.access.forms)
Loading