如何以复杂的方式使用用例

2022-03-03 00:00:00 sql sql-server stored-procedures case

我有一个新的CASE要添加到我的查询中，它显著地改变了我的SQL的方式，并且可能需要用CASE语句来保证一些东西，但是我不确定如何将我现在作为一个联合所想做的事情组合成一个CASE语句。案例表和概念表非常大/慢，最好不要单独/两次连接它们。但是，新的查询部分不需要#tempCms、外部用户数据、cm.first/lastName或cm.user_id。一个有队列名称，另一个没有。每个都有不同的主题。有没有人知道如何组合查询，以避免两次命中慢表？

以下是存储过程的旧部分：

SELECT distinct
 c.id
 ,uc.id
 ,cm.FIRSTNAME as CM_first
 ,cm.LASTNAME as CM_last
 ,cm.user_id as cm_userid
 ,c.person_id 
 ,eou.external_id--eou.external_id AS CM_ExternalUserId
 ,c.id+' '+' Query1' AS subject 
 ,emd.externalId externalPersonId 
 ,eou.system_id as externalSystemId
 ,'' --no queue passed for this area
FROM
    #tempCMs CM with (NOLOCK) 
    INNER JOIN dbo.CASES c with (NOLOCK)  ON CM.Personid=c.id
    INNER JOIN dbo.UM_CASE uc with (NOLOCK) ON uc.case_id=c.id 
    INNER JOIN dbo.LOS S WITH (NOLOCK) ON S.case_id =  UC.case_id 
    INNER JOIN dbo.LOS_EXTENSION SC WITH (NOLOCK) ON SC.los_id= S.id 
    INNER JOIN dbo.USER u with (NOLOCK) on u.id=cm.user_id --
    INNER JOIN dbo.PERSON op with (NOLOCK) on op.id=c.Person_id
    INNER JOIN dbo.U_MEMBER_CONCEPT MC ON MC.CID = op.cid 
    INNER JOIN dbo.EXTERNAL_USER_DATA eou with (NOLOCK) ON eou.user_id = cm.user_id --
    INNER JOIN dbo.EXTERNAL_MEMBER_DATA emd with (NOLOCK) ON emd.CID = op.cid
    WHERE     

        u.disable <> 1 --not disabled/inactive user

            AND
            --not external case manager
               (cm.firstname not like '%external%' and cm.lastname not like '%case manager%')

            AND 
            (
                C.ID in (SELECT case_id FROM #CASES where concept_id='501620')
            )

这是新的联合部件：

UNION
SELECT DISTINCT
 c.id
 ,uc.id
 ,''--cm.FIRSTNAME as CM_first --?
 ,''--cm.LASTNAME as CM_last --?
 ,''--cm.user_id as cm_userid --? 
 ,c.person_id 
 ,'' --not needed for this case
 ,c.id+' '+' Query2' AS subject 
 ,emd.externalId externalPersonId 
 ,''--not needed 
 ,'queueName' AS externalQueue --new admission for this case
FROM
    --#tempCMs CM with (NOLOCK) 
    dbo.CASES c with (NOLOCK)  
    INNER JOIN dbo.UM_CASE uc with (NOLOCK) ON uc.case_id=c.id 
    INNER JOIN dbo.LOS S WITH (NOLOCK) ON S.case_id =  UC.case_id 
    INNER JOIN dbo.LOS_EXTENSION SC WITH (NOLOCK) ON SC.los_id= S.id 
    --INNER JOIN dbo.USER u with (NOLOCK) on u.id=cm.user_id
    INNER JOIN dbo.PERSON op with (NOLOCK) on op.id=c.Person_id
    INNER JOIN dbo.U_MEMBER_CONCEPT MC with (NOLOCK) ON MC.CID = op.cid 
    --INNER JOIN dbo.EXTERNAL_USER_DATA eou with (NOLOCK) ON eou.user_id = cm.user_id --??don't need/cm
    INNER JOIN dbo.EXTERNAL_MEMBER_DATA emd with (NOLOCK) ON emd.CID = op.cid
    INNER JOIN dbo.UM_SERVICE sv (NOLOCK) ON sv.case_id =  uc.case_id --different here
    WHERE     
            (
                sv.diag_code in (SELECT * FROM dbo.T_CAE1350_diag_codes_Indicator)
            )
            AND --member not already handled
            (
                C.person_ID not in (select person_id from #tempCMs ) 
            )

这样的表格设置：

CASES
id   person_id   

UM_CASE
case_id  

LOS
case_id  id

LOS_EXTENSION
los_id

Person
id    cid

U_MEMBER_CONCEPT
cid

EXTERNAL_USER_DATA
cid   user_id

EXERNAL_Member_data
cid

UM_SERVICE
case_id     diag_code

USER
id  first_name   last_name

CREATE TABLE #tempCMs
(
    CASEID VARCHAR(12),
    Person_id int,
    id VARCHAR(25),
    user_id int,
    LASTNAME VARCHAR(150),
    FIRSTNAME VARCHAR(150)
)

INSERT INTO #tempCMs(CASEID,Person_id, id, user_id, lastname, firstname)
SELECT case_id,person_id, id, ccv.user_id, u.LAST_NAME, u.FIRST_NAME
FROM dbo.CM_CASES_VIEW ccv
inner join dbo.USER u with (nolock) on ccv.user_id=u.id

CM_CASES_VIEW
user_id   person_id  case_id


SELECT DISTINCT C.ID AS CASE_ID
,mcv.concept_id
,mcv.STR_VALUE
INTO #CASES
FROM 
    dbo.CASES C 
    INNER JOIN dbo.UM_CASE UC (NOLOCK) ON  C.ID = UC.case_id                    
    INNER JOIN dbo.PERSON OP (NOLOCK) ON  C.person_id = OP.ID
    INNER JOIN dbo.U_MEMBER_CONCEPT MCV (NOLOCK) ON OP.CID = MCV.CID

我不确定要在网上搜索什么来回答这个问题，因为它非常复杂。非常感谢您的点子！

**周六更新.你怎么看？这是否能很好地处理慢速表，但是将信息放入CTE并在以后使用它？

;with commonElement_cte as(
SELECT DISTINCT
     c.id
     ,uc.id as ucid
     ,uc.case_id
     ,c.person_id 
     ,c.id+' '+' Query2' AS subject 
     ,emd.externalId externalPersonId 
    FROM
        dbo.CASES c with (NOLOCK)  
        INNER JOIN dbo.UM_CASE uc with (NOLOCK) ON uc.case_id=c.id 
        INNER JOIN dbo.LOS S WITH (NOLOCK) ON S.case_id =  UC.case_id 
        INNER JOIN dbo.LOS_EXTENSION SC WITH (NOLOCK) ON SC.los_id= S.id 
        --INNER JOIN dbo.USER u with (NOLOCK) on u.id=cm.user_id
        INNER JOIN dbo.PERSON op with (NOLOCK) on op.id=c.Person_id
        INNER JOIN dbo.U_MEMBER_CONCEPT MC with (NOLOCK) ON MC.CID = op.cid 
        INNER JOIN dbo.EXTERNAL_MEMBER_DATA emd with (NOLOCK) ON emd.CID = op.cid
        
)  --common Element cte

select 

    SELECT distinct
     ce.id
     ,ce.ucid
     ,cm.FIRSTNAME as CM_first
     ,cm.LASTNAME as CM_last
     ,cm.user_id as cm_userid
     ,ce.person_id 
     ,ce.external_id--eou.external_id AS CM_ExternalUserId
     ,ce.id+' '+' Query1' AS subject 
     ,ce.externalId externalPersonId 
     ,eou.system_id as externalSystemId
     ,'' --no queue passed for this area
    FROM
        commonElement_cte ce
        INNER JOIN #tempCMs CM on CM.person_ID=ce.person_ID
        INNER JOIN dbo.USER u with (NOLOCK) on u.id=cm.user_id --
        INNER JOIN dbo.EXTERNAL_USER_DATA eou with (NOLOCK) ON eou.user_id = cm.user_id --
        WHERE     
    
            u.disable <> 1 --not disabled/inactive user
    
                AND
                --not external case manager
                   (cm.firstname not like '%external%' and cm.lastname not like '%case manager%')
    
                AND 
                (
                    ce.ID in (SELECT case_id FROM #CASES where concept_id='501620')
                ) 

Here's new union part:

    UNION
    SELECT DISTINCT
     ce.id
     ,ce.ucid
     ,''--cm.FIRSTNAME as CM_first --?
     ,''--cm.LASTNAME as CM_last --?
     ,''--cm.user_id as cm_userid --? 
     ,ce.person_id 
     ,'' --not needed for this case
     ,ce.id+' '+' Query2' AS subject 
     ,ce.externalId externalPersonId 
     ,''--not needed 
     ,'queueName' AS externalQueue --new admission for this case
    FROM
        commonElement_cte ce
        INNER JOIN dbo.UM_SERVICE sv (NOLOCK) ON sv.case_id =  ce.case_id --different here
        WHERE     
                (
                    sv.diag_code in (SELECT * FROM dbo.T_CAE1350_diag_codes_Indicator)
                )
                AND --member not already handled
                (
                    ce.person_ID not in (select person_id from #tempCMs ) 
                )

解决方案

您是否检查了执行计划，以便了解减速的原因？

看起来您正在分别扫描案例和U_Member_Concept 3次。一次填充#案例，并在每个查询中填写一次。这个怎么样：

SELECT DISTINCT C.ID AS CASE_ID
, uc.id
, mcv.concept_id
, mcv.STR_VALUE
, c.person_ID
, op.cid
INTO #CASES
FROM         dbo.CASES C 
  INNER JOIN dbo.UM_CASE UC           ON C.ID = UC.case_id                    
  INNER JOIN dbo.PERSON OP            ON C.person_id = OP.ID
  INNER JOIN dbo.U_MEMBER_CONCEPT MCV ON OP.CID = MCV.CID

如果可能，我建议在其中添加WHERE子句。

SELECT distinct
  c.case_id
, c.id
, cm.FIRSTNAME as CM_first
, cm.LASTNAME as CM_last
, cm.user_id as cm_userid
, c.person_id 
, eou.external_id--eou.external_id AS CM_ExternalUserId
, c.id+' '+' Query1' AS subject 
, emd.externalId externalPersonId 
, eou.system_id as externalSystemId
, '' --no queue passed for this area
FROM #tempCMs CM
  INNER JOIN #CASES c ON CM.Personid=c.id
  --INNER JOIN dbo.UM_CASE uc with (NOLOCK) ON uc.case_id=c.id 
  INNER JOIN dbo.LOS S WITH (NOLOCK) ON S.case_id =  UC.case_id 
  INNER JOIN dbo.LOS_EXTENSION SC WITH (NOLOCK) ON SC.los_id= S.id 
  INNER JOIN dbo.USER u with (NOLOCK) on u.id=cm.user_id --
  --INNER JOIN dbo.PERSON op with (NOLOCK) on op.id=c.Person_id
  --INNER JOIN dbo.U_MEMBER_CONCEPT MC ON MC.CID = op.cid 
  INNER JOIN dbo.EXTERNAL_USER_DATA eou with (NOLOCK) ON eou.user_id = cm.user_id --
  INNER JOIN dbo.EXTERNAL_MEMBER_DATA emd with (NOLOCK) ON emd.CID = c.cid

WHERE u.disable <> 1 --not disabled/inactive user
  AND cm.firstname not like '%external%'
    and cm.lastname not like '%case manager%'
  AND C.concept_id='501620' 

UNION
SELECT DISTINCT
  c.case_id
, c.id
, ''--cm.FIRSTNAME as CM_first --?
, ''--cm.LASTNAME as CM_last --?
, ''--cm.user_id as cm_userid --? 
, c.person_id 
, '' --not needed for this case
, c.id+' '+' Query2' AS subject 
, emd.externalId externalPersonId 
, ''--not needed 
, 'queueName' AS externalQueue --new admission for this case

FROM         #CASES c
  --INNER JOIN dbo.UM_CASE uc with (NOLOCK) ON uc.case_id=c.id 
  INNER JOIN dbo.LOS S WITH (NOLOCK) ON S.case_id =  UC.case_id 
  INNER JOIN dbo.LOS_EXTENSION SC WITH (NOLOCK) ON SC.los_id= S.id 
  --INNER JOIN dbo.USER u with (NOLOCK) on u.id=cm.user_id
  --INNER JOIN dbo.PERSON op with (NOLOCK) on op.id=c.Person_id
  --INNER JOIN dbo.U_MEMBER_CONCEPT MC with (NOLOCK) ON MC.CID = op.cid 
  --INNER JOIN dbo.EXTERNAL_USER_DATA eou with (NOLOCK) ON eou.user_id = cm.user_id --??don't need/cm
  INNER JOIN dbo.EXTERNAL_MEMBER_DATA emd with (NOLOCK) ON emd.CID = c.cid
  INNER JOIN dbo.UM_SERVICE sv (NOLOCK) ON sv.case_id =  uc.case_id --different here

WHERE sv.diag_code in (SELECT * FROM dbo.T_CAE1350_diag_codes_Indicator)        --  It is very unlikely this will work.
  AND C.person_ID not in (select person_id from #tempCMs)

并且不要使用SELECT *。永远不会。

相关文章