Using Select for multiple columns

Question

Is there a more efficient way to perform this query? It runs, but takes about 6 minutes to complete. I believe the multiple select statements are creating the delay. Running any by them self the return a result in < 1 sec.

SELECT
    x.HPCODE AS [Health Plan],
    x.HPFROMDT AS [Eff Date],
    x.MEMBID AS [ID #],
    x.LASTNM AS [Mbr L Name],
    x.FIRSTNM AS [Mbr F Name],
    x.BIRTH AS DOB,
    DATEDIFF(YEAR, birth, GETDATE()) AS Age,
    x.PCP as [Prov ID#],
    Y.LASTNAME as [Prov L Name],
    Y.FIRSTNAME as [Prov F Name],
    PA.PHONE as [Provider Phone],
    (SELECT MAX(cd.FROMDATESVC) 
     FROM [dbo].[Claim_Masters] cm 
     LEFT JOIN [dbo].[Claim_Details] cd ON cm.claimno = cd.claimno 
                                        AND cm.MEMBID = x.MEMBID 
     WHERE LEFT(cd.PROCCODE, 5) IN ('G0402', 'G0438', 'G0439') 
       AND YEAR(cm.SERVICEDATEFROM) = YEAR(GETDATE())) AS [AWV],
    (SELECT MAX(cd.FROMDATESVC) 
     FROM [dbo].[Claim_Masters] cm 
     LEFT JOIN [dbo].[Claim_Details] cd ON cm.claimno = cd.claimno 
                                        AND cm.MEMBID = x.MEMBID 
     WHERE (LEFT(cd.PROCCODE, 5) IN ('G0402', 'G0438', 'G0439') 
            OR RIGHT(cd.PROCCODE, 5) IN ('99490', '99487', '99489'))
       AND YEAR(cm.SERVICEDATEFROM) = YEAR(GETDATE())) AS [CCM],
    (select max(cd.FROMDATESVC) from [dbo].[Claim_Masters] cm left join [dbo].[Claim_Details] cd on cm.claimno = cd.claimno and cm.MEMBID = x.MEMBID Where (Right(cd.PROCCODE,5) in ('99495','99496') )and Year(cm.SERVICEDATEFROM) = Year(Getdate())) as [TCM],
    (select max(cd.FROMDATESVC) from [dbo].[Claim_Masters] cm left join [dbo].[Claim_Details] cd on cm.claimno = cd.claimno and cm.MEMBID = x.MEMBID Where (Right(cd.PROCCODE,5) in ('99211','99212','99213','99214','99215','99201','99202','99203','99204','99205') )and Year(cm.SERVICEDATEFROM) = Year(Getdate())) as [Office],
    (select max(cd.FROMDATESVC) from [dbo].[Claim_Masters] cm left join [dbo].[Claim_Details] cd on cm.claimno = cd.claimno and cm.MEMBID = x.MEMBID Where (Right(cd.PROCCODE,5) = '1170F' )and Year(cm.SERVICEDATEFROM) = Year(Getdate())) as [FUNC],
    (select max(cd.FROMDATESVC) from [dbo].[Claim_Masters] cm left join [dbo].[Claim_Details] cd on cm.claimno = cd.claimno and cm.MEMBID = x.MEMBID Where (Right(cd.PROCCODE,5) in ('1157F','1158F') )and Year(cm.SERVICEDATEFROM) = Year(Getdate())) as [ACP],
    (select max(cd.FROMDATESVC) from [dbo].[Claim_Masters] cm left join [dbo].[Claim_Details] cd on cm.claimno = cd.claimno and cm.MEMBID = x.MEMBID Where (Right(cd.PROCCODE,5) in ('0521F','1125F','1126F') )and Year(cm.SERVICEDATEFROM) = Year(Getdate())) as [PAIN],
    (select max(cd.FROMDATESVC) from [dbo].[Claim_Masters] cm left join [dbo].[Claim_Details] cd on cm.claimno = cd.claimno and cm.MEMBID = x.MEMBID Where (Right(cd.PROCCODE,5) in ('99605','99606','1160F','1111F','1159F') )and Year(cm.SERVICEDATEFROM) = Year(Getdate())) as [MTM]
FROM 
    [dbo].[MEMB_COMPANY] X 
LEFT JOIN  
    [dbo].[PROV_COMPANY] Y ON X.PCP = Y.PROVID
JOIN
    [dbo].[PROV_ADDINFO] PA ON Y.PROV_MPI_NO = PA.PROV_MPI_NO 
                            AND (PA.EDI_DEFAULT = 1)
WHERE
    (ISNULL(x.OPTHRUDT, '') = '' OR 
     x.OPTHRUDT > GETDATE()) 
    AND X.HPCODE  = 'CHPS'

Are you certain you understand what your goal is? Can a member have multiple claims? If they can, then your subqueries can (will) pull dates from different claims - because you only correlate them by member ID. So one claim might supply TCM while another supplies ACP. — SMor, Oct 01 '17 at 00:19
This query looks impossible to support. Think about your colleagues or yourself a month or two later. — svgrafov, Oct 01 '17 at 01:05

Radim Bača · Answer 1 · 2017-09-30T19:36:08.927

I assume that you have 1:M relationship between MEMB_COMPANY and PROV_COMPANY and 1:M relationship between PROV_COMPANY and PROV_ADDINFO. Then it should be possible to rewrite your subqueries using GROUP BY and conditional aggregation. Something like the following query. I rewrote just first two subqueries I guess the idea is clear from it.

SELECT
   x.HPCODE as [Health Plan]
  ,x.HPFROMDT as [Eff Date]
  ,x.MEMBID AS [ID #]
  ,x.LASTNM as [Mbr L Name]
  ,x.FIRSTNM as [Mbr F Name]
  ,x.BIRTH as DOB
  ,datediff(year,birth,getdate()) as Age
  ,x.PCP as [Prov ID#]
  ,Y.LASTNAME as [Prov L Name]
  ,Y.FIRSTNAME as [Prov F Name]
  ,PA.PHONE as [Provider Phone]
  ,max(CASE WHEN left(cd.PROCCODE,5) in ('G0402','G0438','G0439') and Year(cm.SERVICEDATEFROM) = Year(Getdate()) THEN cd.FROMDATESVC END)
  ,max(CASE WHEN left(cd.PROCCODE,5) in ('G0402','G0438','G0439') or Right(cd.PROCCODE,5) in ('99490','99487','99489')  and Year(cm.SERVICEDATEFROM) = Year(Getdate()) THEN cd.FROMDATESVC END)
  -- the subsequent conditions follows
FROM [dbo].[MEMB_COMPANY] X 
LEFT JOIN  [dbo].[PROV_COMPANY] Y ON X.PCP = Y.PROVID
Join [dbo].[PROV_ADDINFO] PA ON Y.PROV_MPI_NO = PA.PROV_MPI_NO and (PA.EDI_DEFAULT = 1)
LEFT JOIN [dbo].[Claim_Masters] cm ON cm.MEMBID = x.MEMBID
LEFT JOIN [dbo].[Claim_Details] cd on cm.claimno = cd.claimno
WHERE (isnull(x.OPTHRUDT, '') = '' or x.OPTHRUDT > GETDATE()) and X.HPCODE  = 'CHPS'
GROUP BY x.MEMBID, x.HPCODE ,x.HPFROMDT, x.LASTNM, x.FIRSTNM, x.BIRTH, x.PCP, Y.LASTNAME, Y.FIRSTNAME, PA.PHONE

I feel that your query could be accelerated as well if you precompute the left(cd.PROCCODE,5) and right(cd.PROCCODE,5) values in the table.

Stéphane CLEMENT · Answer 2 · 2017-09-30T19:44:30.200

Subqueries are often slow... You should try to avoid them with a syntax like :

SELECT
       x.HPCODE as [Health Plan]
      ,x.HPFROMDT as [Eff Date]
      ,x.MEMBID AS [ID #]
      ,x.LASTNM as [Mbr L Name]
      ,x.FIRSTNM as [Mbr F Name]
      ,x.BIRTH as DOB
      ,datediff(year,birth,getdate()) as Age
      ,x.PCP as [Prov ID#]
      ,Y.LASTNAME as [Prov L Name]
      ,Y.FIRSTNAME as [Prov F Name]
      ,PA.PHONE as [Provider Phone]
      ,MAX(CASE WHEN left(cd.PROCCODE,5) in ('G0402','G0438','G0439') THEN cd.FROMDATESVC ELSE NULL END) as [AWV]
      ,MAX(CASE WHEN left(cd.PROCCODE,5) in ('G0402','G0438','G0439') or Right(cd.PROCCODE,5) in ('99490','99487','99489') THEN cd.FROMDATESVC ELSE NULL END) as [CCM]
      ,MAX(CASE WHEN Right(cd.PROCCODE,5) in ('99495','99496') THEN cd.FROMDATESVC ELSE NULL END) as [TCM]
      ,MAX(CASE WHEN Right(cd.PROCCODE,5) in ('99211','99212','99213','99214','99215','99201','99202','99203','99204','99205') THEN cd.FROMDATESVC ELSE NULL END) as [Office]
      ,MAX(CASE WHEN Right(cd.PROCCODE,5) = '1170F' THEN cd.FROMDATESVC ELSE NULL END) as [FUNC]
      ,MAX(CASE WHEN Right(cd.PROCCODE,5) in ('1157F','1158F') THEN cd.FROMDATESVC ELSE NULL END) as [ACP]
      ,MAX(CASE WHEN Right(cd.PROCCODE,5) in ('0521F','1125F','1126F') THEN cd.FROMDATESVC ELSE NULL END) as [PAIN]
      ,MAX(CASE WHEN Right(cd.PROCCODE,5) in ('99605','99606','1160F','1111F','1159F') THEN cd.FROMDATESVC ELSE NULL END) as [MTM]
    FROM [dbo].[MEMB_COMPANY] X
        LEFT JOIN  [dbo].[PROV_COMPANY] Y ON (X.PCP = Y.PROVID)
        INNER JOIN [dbo].[PROV_ADDINFO] PA ON (Y.PROV_MPI_NO = PA.PROV_MPI_NO and (PA.EDI_DEFAULT = 1))
        LEFT JOIN [dbo].[Claim_Masters] cm ON (cm.MEMBID = x.MEMBID 
        LEFT JOIN [dbo].[Claim_Details] cd ON (cm.claimno = cd.claimno)
    where (isnull(x.OPTHRUDT, '') = '' or x.OPTHRUDT > GETDATE()) and X.HPCODE  = 'CHPS'
GROUP BY x.HPCODE,x.HPFROMDT,x.MEMBID,x.LASTNM,x.FIRSTNM,x.BIRTH,datediff(year,birth,getdate()),x.PCP,Y.LASTNAME,Y.FIRSTNAME,PA.PHONE

Using Select for multiple columns

2 Answers2