我有以下查询,它消耗了SQL server 2017 web版服务器上的高CPU。
SELECT DISTINCT IM_ID,IM_NAME, IM_ADDR_ID
FROM MST_INSURANCE
WHERE IM_NAME >= 'a'
AND IM_NAME <= 'Zzzzzzz'
AND EXISTS
(SELECT BLH_ID
FROM TRN_BILLING_HEAD,
TRN_BILLING_DET
WHERE BLH_BOOL_INACTIVE = 0
AND BLH_TRNTYPE = 'BL'
AND BLD_BLH_ID = BLH_ID
AND BLH_CLAIM_STATUS = 'B'
AND BLD_BOOL_DO_NOT_SEND = 0
AND BLD_RESP_NOW <= 2
AND (
(BLD_RESP_NOW = 0
AND BLH_INS1_ID = IM_ID)
OR
(BLD_RESP_NOW = 1
AND BLH_INS2_ID = IM_ID)
OR
(BLD_RESP_NOW = 2
AND BLH_INS3_ID = IM_ID)
)
)
ORDER BY 2,1我知道使用的查询结构是旧的,因此我尝试通过在查询中添加INNER并删除EXISTS子句来优化查询,如下所示。这有助于优化查询成本,但我想我已经更改了查询逻辑。
SELECT DISTINCT IM_ID,IM_NAME, IM_ADDR_ID, BLH_ID FROM MST_INSURANCE AS INSU
INNER JOIN TRN_BILLING_HEAD AS HEAD ON BLH_INS1_ID = IM_ID AND BLH_INS2_ID = IM_ID AND BLH_INS3_ID = IM_ID
INNER JOIN TRN_BILLING_DET AS DET ON BLD_BLH_ID = BLH_ID
WHERE
IM_NAME >= 'a' AND IM_NAME <= 'Zzzzzzz' AND
BLH_BOOL_INACTIVE = 0 AND BLH_TRNTYPE = 'BL'
AND BLH_CLAIM_STATUS = 'B' AND BLD_BOOL_DO_NOT_SEND = 0 AND BLD_RESP_NOW <= 2
AND (BLD_RESP_NOW = 0 OR BLD_RESP_NOW = 1
OR BLD_RESP_NOW = 2) ORDER BY 2,1我不是一个开发人员,因此发现很难优化查询。任何人都可以建议对查询进行任何更改吗?
发布于 2021-05-16 15:16:37
谢谢大家回复我,我真的很感激。
我已经在原始查询中包含了一些连接,如下所示,这显着降低了查询成本。
SELECT DISTINCT IM_ID,IM_NAME, IM_ADDR_ID FROM MST_INSURANCE AS INSU
INNER JOIN TRN_BILLING_HEAD AS HEAD ON BLH_INS1_ID = IM_ID OR BLH_INS2_ID = IM_ID OR BLH_INS3_ID = IM_ID
INNER JOIN TRN_BILLING_DET AS DET ON BLD_BLH_ID = BLH_ID
WHERE
IM_NAME >= 'a' AND IM_NAME <= 'Zzzzzzz' AND
BLH_BOOL_INACTIVE = 0 AND BLH_TRNTYPE = 'BL'
AND BLH_CLAIM_STATUS = 'B' AND BLD_BOOL_DO_NOT_SEND = 0 AND BLD_RESP_NOW <= 2
AND (BLD_RESP_NOW = 0 OR BLD_RESP_NOW = 1
OR BLD_RESP_NOW = 2) ORDER BY 2,1https://stackoverflow.com/questions/67062528
复制相似问题