Problem with very slow query (1 Viewer)

[VBANewBie :)]

Hi Guys , I have a very slow query (Even slower in splitted database_be) tried every thing to speed it up but in no vain , Please find attached the query mentioned name ResultFinal .
Thanks in advance

 

[isladogs]

I'm not surprised it is slow as it is based on multiple layers of queries with many outer joins and filters with wildcats both before and after the selected form values. Furthermore many of the underlying parts of the final query also have similarly complex structures.
It would have been helpful to have supplied the form used to provide the filter parameters so someone could test it properly and suggest ways of improving the performance.
In the meantime, check the structure of your queries step by step removing as much complexity as possible. Use indexes on any fields used in filters and joins. Avoid outer joins where possible. Try to only wildcards after the form selection value so as to allow indexes to be used in filtering.

You may find my article on optimising queries useful Speed Comparison Tests 8 - Mendip Data Systems

 

[VBANewBie :)]

It would have been helpful to have supplied the form used to provide the filter parameters so someone could test it properly and suggest ways of improving the performance.

Thanks for your reply , I added the form used in search .
About edits need to be done I already have tried as much as i know about queries , Thanks for your help

 

[plog]

I see a few unnecessary steps and my gut feels a lot more. I've inherited a few databases with spaghetti queries like this before and what I did is this:

Get a piece of paper and start at the top of your query stack.
Write "ResultFinal" at the top center of the page and circle it.
Go into design view and see it is built on ResultSub2 and QeryCountPoinDate2, write those under the circled "ResultFinal".
From ResultSub2 write a line angling left and down an inch or so.
Rewrite "ResultSub2" and cirlce it.
Open it in design view and write the queries/tables its comprised of under it.
Keep mapping out where each query is used and what data sources they are made of until you hit tables and have every query in your diagram.

Now you have a map of how this thing is pieced together. Next you look for queries you can combine, incorporate into a higher level query or just get rid of completely.

For example, I see 5 you can merge together: QryVariousDownReasonsX. You have 5 of these with essentially the same SQL, the only thing you are changing is the number in the query name that is then used as criteria in the query. Instead, You add the SerialFormat field to the query and eliminate all the numbered criteria, thus making those 5 queries 1.

My gut is telling me you do not need all the aggregation queries (ones that use GROUP BY, SUM, COUNT, etc.). It looks like a query is aggregated, then you use it in another query that is aggregated whcih in turn is used by another query which is aggregated. If you could make it so that just one of those queries does the aggregation you would eliminate a lot of inefficiency and might even be able to eliminate a query because of it.

Again, map out how al these are tied together then work through your map seeing if any queries can be eliminated or merged.

 

[VBANewBie :)]

I got it thanks man i will do as you explained , I have learned many things from your reply thanks.

 

[VBANewBie :)]

For example, I see 5 you can merge together: QryVariousDownReasonsX. You have 5 of these with essentially the same SQL, the only thing you are changing is the number in the query name that is then used as criteria in the query. Instead, You add the SerialFormat field to the query and eliminate all the numbered criteria, thus making those 5 queries 1.

About this i made 5 queries because of the 5 reasons are used in the same line in the search form , If i used your way i will have to use IIF statement to deal with that one merged query to choose the SerialNumber for Example :

IIF([SerialFormat]="Num1",[Reason],"")

This IIF will be used 5 times to choose the right serial for the right field get it ? Or i’m getting it wrong i could make an example to demonstrate ? thanks

 

[plog]

First, you use SerialFormat="Num1" in the WHERE clause so that conditional is doing nothing--every record that makes it past the WHERE clause has SerialFormat="Num1" so testing it in the field is pointless.

Second, once you remove that test from the WHERE clause you still don't need it because you will be adding SerialFormat to the query as a field. With that there you simply need Reason without a test.

Again, I didn't do a deep dive into the whole query system you have. But I know logically that those 5 queries can be merged into 1 and eventually achieve the same results you need.

 

[isladogs]

I agree with the general points made by @plog about your query structure.
I want to mention some other issues which are also massively contributing to your speed issues

1. Using subdatasheets in your tables significantly affects performance as these need to be loaded whenever tables or queries based on these are open. End users should never view tables or queries directly so you should remove all subdatasheets
2. As already stated ensure all fields used in joins and filters are indexed. This will make a major difference in terms of speed
3. Your form search is currently based on the On Change event which runs after each key press. Change this to the After Update event
4. Only search one field with each search box. If you want to allow users to search 4 fields, use 4 different search boxes. I recommend using a combo for each
5. Remove the leading wildcards as this stops the index being used. Only allow a trailing wildcard on each case. This will allow the use of indexes.
5. Subforms always load before the main form. You can speed up the main form loading by removing the subform row source on form load. Then set the row source to your modified ResultFinal query as part of the search box after update event

I've made some of the above changes in the attached database. I hope this helps.
Much more needs to be done but as I can't read / understand the language used, I'm unable to do any more

 

[VBANewBie :)]

I've made some of the above changes in the attached database. I hope this helps.

You have been a great help thank you so much , I will start working on the points you all mentioned .
I can’t thank you enough