如何微调此sql server查询？

hfwmuf9z 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(291)

以下查询需要10秒以上。如何微调此查询？

SELECT
    COALESCE(A.KEY1, B.KEY1) as KEY1,
    COALESCE(A.KEY2, B.KEY2) as KEY2,
    COALESCE(A.KEY3, B.KEY3) as KEY3,
    A.NUMBER, A.SERVICE_DATE, A.SERVICE_TYPE, B.CLERK_NAME,
    (SELECT TOP 1 VALUE FROM C 
     WHERE ((A.KEY1 = C.KEY1 AND A.KEY2 = C.KEY2 AND A.KEY3 = C.KEY3) OR  
            (B.KEY1 = C.KEY1 AND B.KEY2 = C.KEY2 AND B.KEY3 = C.KEY3)) 
       AND C.CODE LIKE '*_SABC') AS XXXSVTM,
    (SELECT TOP 1 VALUE FROM C 
     WHERE ((A.KEY1 = C.KEY1 AND A.KEY2 = C.KEY2 AND A.KEY3 = C.KEY3) OR  
            (B.KEY1 = C.KEY1 AND B.KEY2 = C.KEY2 AND B.KEY3 = C.KEY3)) 
       AND C.CODE LIKE '*_SBCD') AS XXXSVCM,
    (SELECT TOP 1 VALUE FROM C 
     WHERE ((A.KEY1 = C.KEY1 AND A.KEY2 = C.KEY2 AND A.KEY3 = C.KEY3) OR  
            (B.KEY1 = C.KEY1 AND B.KEY2 = C.KEY2 AND B.KEY3 = C.KEY3)) 
       AND C.CODE LIKE '*_SCDE') AS XXXCSRV,
    (SELECT TOP 1 VALUE FROM C 
     WHERE ((A.KEY1 = C.KEY1 AND A.KEY2 = C.KEY2 AND A.KEY3 = C.KEY3) OR  
            (B.KEY1 = C.KEY1 AND B.KEY2 = C.KEY2 AND B.KEY3 = C.KEY3)) 
       AND C.CODE LIKE '*_SDEF') AS XXXSRFN,
(select TOP 1 VALUE from C where ((A.KEY1=C.KEY1 and A.KEY2=C.KEY2 and A.KEY3=C.KEY3) OR (B.KEY1 = C.KEY1 AND B.KEY2 = C.KEY2 AND B.KEY3 = C.KEY3)) and C.CODE like '*_SEFG') as XXXCSRV
,(select TOP 1 VALUE from C where ((A.KEY1=C.KEY1 and A.KEY2=C.KEY2 and A.KEY3=C.KEY3) OR (B.KEY1 = C.KEY1 AND B.KEY2 = C.KEY2 AND B.KEY3 = C.KEY3)) and C.CODE like '*_SFGH') as XXXSRLN
,(select TOP 1 VALUE from C where ((A.KEY1=C.KEY1 and A.KEY2=C.KEY2 and A.KEY3=C.KEY3) OR (B.KEY1 = C.KEY1 AND B.KEY2 = C.KEY2 AND B.KEY3 = C.KEY3)) and C.CODE like '*_SXYZ') as XXXSRCY
,(select TOP 1 VALUE from C where ((A.KEY1=C.KEY1 and A.KEY2=C.KEY2 and A.KEY3=C.KEY3) OR (B.KEY1 = C.KEY1 AND B.KEY2 = C.KEY2 AND B.KEY3 = C.KEY3)) and C.CODE like '*_SVFN') as XXXSVFN
FROM
    A 
FULL JOIN
    B ON (A.KEY1 = B.KEY1 AND A.KEY2 = B.KEY2 AND A.KEY3 = B.KEY3);

更新：是打字错误，现在调整了where子句

sql sql-server query-optimization sql-tuning

来源：https://stackoverflow.com/questions/63307474/how-to-fine-tune-this-sql-server-query

1条答案

按热度按时间

y53ybaqx1#

看起来像是a和c之间的内部连接。
你两次加入a和c之间。
对每条记录运行8个相关子查询。
你要的是c的第一张唱片，没有特别的顺序。
这将在更短的时间内提供相同的结果。

select COALESCE(A.KEY1, B.KEY1) as KEY1
, COALESCE(A.KEY2, B.KEY2) as KEY2
, COALESCE(A.KEY3, B.KEY3) as KEY3
, A.NUMBER
, A.SERVICE_DATE
, A.SERVICE_TYPE
, B.CLERK_NAME
, min(case when C.CODE like '*_SABC' then C.VALUE) as XXXSVTM
, min(case when C.CODE like '*_SBCD' then C.VALUE) as XXXSVCM
, min(case when C.CODE like '*_SCDE' then C.VALUE) as XXXCSRV
, min(case when C.CODE like '*_SDEF' then C.VALUE) as XXXSRFN
, min(case when C.CODE like '*_SEFG' then C.VALUE) as XXXCSRV
, min(case when C.CODE like '*_SFGH' then C.VALUE) as XXXSRLN
, min(case when C.CODE like '*_SXYZ' then C.VALUE) as XXXSRCY
, min(case when C.CODE like '*_SVFN' then C.VALUE) as XXXSVFN

from A 
    inner join C on A.KEY1=C.KEY1 and A.KEY2=C.KEY2 and A.KEY3=C.KEY3
    full outer join B on A.KEY1=B.KEY1 and A.KEY2=B.KEY2 and A.KEY3=B.KEY3
;

如果每个子查询中的或的右侧 B.KEY1=C.KEY1 等等。。。

select q.KEY1
, q.KEY2
, q.KEY3
, q.NUMBER
, q.SERVICE_DATE
, q.SERVICE_TYPE
, q.CLERK_NAME
, min(case when C.CODE like '*_SABC' then C.VALUE) as XXXSVTM
, min(case when C.CODE like '*_SBCD' then C.VALUE) as XXXSVCM
, min(case when C.CODE like '*_SCDE' then C.VALUE) as XXXCSRV
, min(case when C.CODE like '*_SDEF' then C.VALUE) as XXXSRFN
, min(case when C.CODE like '*_SEFG' then C.VALUE) as XXXCSRV
, min(case when C.CODE like '*_SFGH' then C.VALUE) as XXXSRLN
, min(case when C.CODE like '*_SXYZ' then C.VALUE) as XXXSRCY
, min(case when C.CODE like '*_SVFN' then C.VALUE) as XXXSVFN

from (
    select COALESCE(A.KEY1, B.KEY1) as KEY1
    , COALESCE(A.KEY2, B.KEY2) as KEY2
    , COALESCE(A.KEY3, B.KEY3) as KEY3
    , A.NUMBER
    , A.SERVICE_DATE
    , A.SERVICE_TYPE
    , B.CLERK_NAME

    from A 
      full outer join B on A.KEY1=B.KEY1 and A.KEY2=B.KEY2 and A.KEY3=B.KEY3
) q
  inner join C on q.KEY1=C.KEY1 and q.KEY2=C.KEY2 and q.KEY3=C.KEY3
;

赞(0）回复(0）举报 2021-07-24

我来回答

如何微调此sql server查询？

1条答案

相关问题

热门标签

最新问答