Search code examples
sqlms-access

How can I optimize/modify this SQL query to have the same results but with better performance?


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'))))
)

Solution

  • 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