Skip to content
Advertisement

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

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement