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