This SQL query takes time to execute and I don't know how can I optimize it/or modify it to have the same results with better performance

SELECT equipment, relationType, client, IIF([_status] = 'D', -1, 0) as removed
FROM synchro_my3dshp_equipmentClient
WHERE [_destination] = 5
AND ([_status] IN ('M', 'D') 
OR equipment in (
SELECT distinct synchroKey
FROM synchro_my3dshp_clientAssembly
WHERE [_destination] = 5
AND ([_status] IN ('M', 'D') 
OR synchroKey in (
SELECT distinct equipment
FROM synchro_my3dshp_equipmentClient
WHERE [_destination] = 5 AND [_status] IN ('M', 'D'))))
)

CodePudding user response：

Since we don't have your data, and, thus, cannot reproduce your problem, we can only give "try this and see if improves your situation" answers. I personally don't like this kind of answer, but that's all we can do here.

At the risk of stating the obvious: Check your indexes. Based on your query, the following indexes should exist:

• synchro_my3dshp_equipmentClient: Combined index on _destination and _status.
• synchro_my3dshp_equipmentClient: Combined index on _destination and equipment.
• synchro_my3dshp_clientAssembly: Combined index on _destination and _status.
• synchro_my3dshp_clientAssembly: Combined index on _destination and synchroKey.

If they don't exist, add them.

CodePudding user response：

As your second subquery seems identical to the top query:

SELECT 
    equipment, 
    relationType, 
    client, 
    ([_status] = 'D') as removed
FROM 
    synchro_my3dshp_equipmentClient
WHERE 
    [_destination] = 5
    AND 
    ([_status] IN ('M', 'D') 
    OR 
    equipment in 

        (SELECT distinct 
            synchroKey
        FROM 
            synchro_my3dshp_clientAssembly
        WHERE 
            [_destination] = 5
            AND 
            ([_status] IN ('M', 'D') 
            OR 
            synchroKey in 

                (SELECT distinct 
                    equipment
                FROM 
                    synchro_my3dshp_equipmentClient
                WHERE 
                [_destination] = 5 
                AND 
                [_status] IN ('M', 'D')))))

it perhaps can be reduced to (and relieved from distinct):

SELECT 
    equipment, 
    relationType, 
    client, 
    ([_status] = 'D') AS removed
FROM 
    synchro_my3dshp_equipmentClient
WHERE 
    [_destination] = 5
    AND 
    ([_status] IN ('M', 'D') 
    OR 
    equipment IN 

        (SELECT  
            synchroKey
        FROM 
            synchro_my3dshp_clientAssembly
        WHERE 
            [_destination] = 5
            AND 
            [_status] IN ('M', 'D')))

However, I may be missing something, don't have your data, and are not sure what your goal is.

CodePudding user response：

I rewrote my query like this and it's very fast

SELECT EC.equipment, EC.relationType, EC.client, IIF(EC.[_status] = 'D', -1, 0) AS removed
FROM synchro_my3dshp_equipmentClient AS EC
WHERE EC.[_destination] = 5 AND EC.[_status] IN('M', 'D')
UNION
SELECT EC.equipment, EC.relationType, EC.client, IIF(EC.[_status] = 'D', -1, 0) AS removed
FROM synchro_my3dshp_equipmentClient AS EC
INNER JOIN synchro_my3dshp_clientAssembly AS CA ON CA.synchroKey = EC.equipment 
WHERE CA.[_destination] = 5 AND CA.[_status] IN('M', 'D') AND EC.[_destination] = 5

Correct me, if I am wrong