I have this piece of query that I would need to optimize, but I don't really have idea how to do it. Before giving you the query, here is some context :
This query is basically counting the amount of components in all the "MACHINE" type components. See it as a tree where I need to count all the elements below a certain node. Right now, this query has a cost of ~250, and needs ~5 seconds to be executed. It's included in a view in a CTE. The point is that the view without this query takes less than 1 second to be executed, and ~6 seconds with it. There is a real-time necessity on the user side, so that's why I need to optimize this query.
The query :
SELECT FATHER.ARTICLE, COUNT(DETAILS_SON.COMPONENT) FROM HEADER AS FATHER JOIN DETAILS AS DETAILS_FATHER ON DETAILS_FATHER.ARTICLE = FATHER.ARTICLE AND DETAILS_FATHER.VERSION = FATHER.VERSION JOIN DETAILS AS DETAILS_SON ON DETAILS_SON.ARTICLE=DETAILS_FATHER.COMPONENT JOIN ARTICLE AS COMPONENTS ON COMPONENTS.ARTICLE=DETAILS_SON.COMPONENT WHERE COMPONENTS.TYPE = 'COMPONENT' AND FATHER.WNT_CODITI = 'MACHINE' GROUP BY FATHER.ARTICLE
Edit : I corrected some things in the query. FATHER, ... are not the real names of the tables, I just wanted to make them understandable.
Concerning the indexes, I have indexes on : - WNT_CODITI, Table HEADER - ARTICLE, VERSION, Table HEADER - COMPONENT, Table DETAILS - ARTICLE, VERSION, Table DETAILS
For the execution plan, as I said it gives me a cost of 250, but I'm not good enough to analyze and understand the rest of the plan.
Your ARTICLE table or entity is not being used in this query so you can remove it and also for clarity, you need to specify which ARTICLE you are selecting
SELECT ARTICLE, -- clarify this value is it from FATHER, DETAILS_FATHER or from DETAILS_SON COUNT(DETAILS_SON.COMPONENT) FROM HEADER AS FATHER JOIN DETAILS AS DETAILS_FATHER ON DETAILS_FATHER.ARTICLE = FATHER.ARTICLE AND DETAILS_FATHER.VERSION = PEREWNOMETET.VERSION JOIN DETAILS AS DETAILS_SON ON DETAILS_SON.ARTICLE=DETAILS_FATHER.COMPONENT WHERE FATHER.WNT_CODITI = 'MACHINE' GROUP BY FATHER.ARTICLE