首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >CPU使用率高的查询

问CPU使用率高的查询
EN

Stack Overflow用户
提问于 2021-04-13 00:56:25
回答 1查看 57关注 0票数 0

我有以下查询,它消耗了SQL server 2017 web版服务器上的高CPU。

代码语言:javascript
复制
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子句来优化查询,如下所示。这有助于优化查询成本,但我想我已经更改了查询逻辑。

代码语言:javascript
复制
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

我不是一个开发人员,因此发现很难优化查询。任何人都可以建议对查询进行任何更改吗?

EN

回答 1

Stack Overflow用户

发布于 2021-05-16 15:16:37

谢谢大家回复我,我真的很感激。

我已经在原始查询中包含了一些连接,如下所示,这显着降低了查询成本。

代码语言:javascript
复制
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,1
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/67062528

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档