Why so slow

[John Sh]

In the attached file there are two forms.
They are essentially the same with the form SB_Search being a copy of Multi-Search. the only real difference between them is the data table and the queries.
The form SB_Search opens as expected but the Multi-search form takes a very long time to get to the Load event. After that it seems to load normally,
I have double checked the queries and they all seem to run as expected. It is just the slow loading of the Multi-Search form that has me tossed.
Why is it so?

 

[arnelgp]

I noticed that Grouping (Total Query) and not Grouped (simple Select Query) will
result in same number of records.
so you don't need to Group your Query.

 

[John Sh]

I noticed that Grouping (Total Query) and not Grouped (simple Select Query) will
result in same number of records.
so you don't need to Group your Query.

That does speed things up to where the delay is reasonable. I guess the rest is due to 12k records to be sorted by multiple criteria.
Thank you for the suggestion..

 

[arnelgp]

you can try adding Index to Main table on those fields:

also you may try to set Subdatasheet Name to None (on General Property, design view of Main table).

 

[John Sh]

you can try adding Index to Main table on those fields:
View attachment 105604

also you may try to set Subdatasheet Name to None (on General Property, design view of Main table).

Thanks.
They were all indexed except collector.
Unfortunately, the way the International Herbarium system works is that all data has to be included in a single table. That means that normalisation is not an option. Also the number of relationships necessary is prohibitive in Access. Not only that but it is advantageous to the user to be able to view all data on a single form so the form that calls the search form has some 90+ controls in the detail window. The search form simplifies finding the record for a particular specimen.
Thank you again for your valued assistance.
John

 

[arnelgp]

They were all indexed except collector

yes is see the table have indexes, Individually.
you can create a Compound index based on the "Order By" clause of your Query (see the Image i posted).
the Query will determine which optimal index it will use.
on your case, it will only use AccessionNumber.
if you add a compound index (see the Order By clause of your Query in SQL view) based on your
sort order, it will use that index.