How can I optimize this query on SQL Server?

by Alexandre NOPRE   Last Updated April 15, 2019 10:26 AM

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.

Tags : sql sql-server


Answers 1


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
Ian P.
Ian P.
April 15, 2019 09:00 AM

Related Questions


Updated November 20, 2017 05:26 AM

Updated February 17, 2016 02:11 AM

Updated December 06, 2017 20:26 PM

Updated August 24, 2017 04:26 AM

Updated March 08, 2018 18:26 PM