Query Optimization

[khurram7x]

Maybe i posted this in a wrong thread earlier. Adding another screenshot for reference:

I've got a query that when i run without enabling Unique Values, returns over 750,000 results and with Unique Values enabled... about 15,000 result sets.Query contains 8 tables.

I've a selection form based on this query, attached, and in this form every next combobox RowSource is based on the previous selections. since the number of results are huge, this takes some time every time I want to select from the next drop list.

how is it possible to optimize this query please??

Thanks

 

[CJ_London]

Looks like there are 4 or 5 potential areas for improvement

1. Relationships
Looks like your relationships can be improved. For example, instead of linking area to areacode, the area field in the installationtype table should be called areaID and linked to the id field of the lev3earea table.

2. Indexing
Sticking with that particular relationship - the ID field will be indexed - my guess is the areacode field isn't - the same goes for all your other tables. So if for some reason you cannot link correctly, ensure the areacode field is indexed

3. Query Design 1
rather than select distinct on the whole query, select distinct on the installationtype in a separate query and use this query instead of the table in your main query (all the other tables must be unique in themselves so no benefit in select distincting them)

4. Query Design 2
only have in the query those tables you need for the form

5. Form design
You say this is using cascading combo boxes so base the rowsource on the relevant tables only. For example project might be based only on the lev0 project table, Area on installationtype and lev3earea and with a criteria based on the project combobox
etc

 

[gemma-the-husky]

are there really 750000 records?

if you have not specified your table indexes correctly, then you may be getting a Cartesian join returning the wrong number of rows in the query.

 

[khurram7x]

Looks like there are 4 or 5 potential areas for improvement

1. Relationships
Looks like your relationships can be improved. For example, instead of linking area to areacode, the area field in the installationtype table should be called areaID and linked to the id field of the lev3earea table.

2. Indexing
Sticking with that particular relationship - the ID field will be indexed - my guess is the areacode field isn't - the same goes for all your other tables. So if for some reason you cannot link correctly, ensure the areacode field is indexed

3. Query Design 1
rather than select distinct on the whole query, select distinct on the installationtype in a separate query and use this query instead of the table in your main query (all the other tables must be unique in themselves so no benefit in select distincting them)

4. Query Design 2
only have in the query those tables you need for the form

5. Form design
You say this is using cascading combo boxes so base the rowsource on the relevant tables only. For example project might be based only on the lev0 project table, Area on installationtype and lev3earea and with a criteria based on the project combobox
etc

Just seen that I never thanked on this. It helped me solve one of the biggest problem i was facing for quite a time. Basically, application of point # 5 solved my issue alone, but later I improved Indexing based on point 2 and learned something new from point 3.
Thank you.

 

[Simon_MT]

If you are clever regarding 5 you can use fuzzy logic. Test is field is populated and include in SearchCriteria else Ignore otherwise concatenate into the SearchCriteria:

SearchCriteria = SearchCriteria " And .....

It is a bit messy to establish the first populated field and concatenate the next populated - first doesn't need concatenation or " And

Index any field that you use in the Search even Descriptors.

Simon