Performance improvement in displaying records on a form

[SAC]

I want to improve the time it takes to display records on a form.

I have a table with 24,000 records in it and across a network it takes
longer than I would like to display records.

I'm using a sql statement to set the recordsource for the form. The reason
for this is that the record source changes a little based on different
selections after the form is displayed.

First of all, the initial sql statement has on order by clause in it with
two fields. Should I set and index on this table with these two fields in
it?

Thanks.

 

[Duane Hookom]

Definitely index the fields that you are sorting on. Also, I doubt you ever
need to return 24,000 records to a form. Create your form to return only the
minimum number of records and fields that you need your user to see at one
time.

 

[Jerry Whittle]

Like Duane says, you should create an index on the fields in the Order By.
Also index any fields that are part of the criteria or Where clause.

If you are dynamically creating the SQL in a function, it could be a little
slower than using a stored query. Access optimizes the execution plan for the
SQL the first time that you run it and this takes some time. If the query is
stored and not basically changed, the explain plan is reused and therefore
runs faster. You could still use a form to change the criteria in the query
by referencing the control by [Forms]![YourFormName]![YourTextBox] in the
criteria.

Last, but possibly not least, is your database split with the tables on the
network and everything else on each user's PC? This can make a difference.

 

[SAC]

Thanks!

Definitely index the fields that you are sorting on. Also, I doubt you ever
need to return 24,000 records to a form. Create your form to return only the
minimum number of records and fields that you need your user to see at one
time.

 

[SAC]

Thanks, Jerry!

Like Duane says, you should create an index on the fields in the Order By.
Also index any fields that are part of the criteria or Where clause.

If you are dynamically creating the SQL in a function, it could be a little
slower than using a stored query. Access optimizes the execution plan for the
SQL the first time that you run it and this takes some time. If the query is
stored and not basically changed, the explain plan is reused and therefore
runs faster. You could still use a form to change the criteria in the query
by referencing the control by [Forms]![YourFormName]![YourTextBox] in the
criteria.

Last, but possibly not least, is your database split with the tables on the
network and everything else on each user's PC? This can make a difference.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I want to improve the time it takes to display records on a form.

I have a table with 24,000 records in it and across a network it takes
longer than I would like to display records.

I'm using a sql statement to set the recordsource for the form. The reason
for this is that the record source changes a little based on different
selections after the form is displayed.

First of all, the initial sql statement has on order by clause in it with
two fields. Should I set and index on this table with these two fields in
it?

Thanks.