這個 SQL 查詢需要時間來執行，我不知道如何優化/或修改它以獲得相同的結果和更好的性能
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'))))
)
uj5u.com熱心網友回復：
我像這樣重寫了我的查詢，而且速度非常快
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
糾正我，如果我錯了
uj5u.com熱心網友回復：
由于我們沒有您的資料，因此無法重現您的問題，我們只能給出“試試這個，看看是否能改善您的情況”的答案。我個人不喜歡這種答案，但這就是我們在這里所能做的。
冒著陳述顯而易見的風險：檢查您的索引。根據您的查詢，應存在以下索引：
- synchro_my3dshp_equipmentClient：聯合對指數
_destination和_status。 - synchro_my3dshp_equipmentClient：聯合對指數
_destination和equipment。 - synchro_my3dshp_clientAssembly：聯合對指數
_destination和_status。 - synchro_my3dshp_clientAssembly：聯合對指數
_destination和synchroKey。
如果它們不存在，請添加它們。
uj5u.com熱心網友回復：
由于您的第二個子查詢似乎與頂級查詢相同：
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')))))
它也許可以簡化為（并從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')))
但是，我可能遺漏了一些東西，沒有您的資料，并且不確定您的目標是什么。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/314229.html
上一篇：在MSAccess中使用to_sql(...,method='multi')時出錯
下一篇：合并訪問中以逗號分隔的單元格