CODEWITHSUNDEEP
mysql
Sunil
Sunil

Reputation: 175

Sum() slows down my query

I am using sum() inside case statement. But this slows down my query. Is there any other way for this. This is the query. Please help me.

SELECT (SUM(PRI_INS_AGING) + SUM(SEC_INS_AGING) + SUM(TER_INS_AGING)) AS INS_AGING,SUM(PAT_AGING) AS PAT_AGING FROM
                (SELECT 
        CASE WHEN L.RESP_PARTY =1 AND VP.STATUS IN(3,5) AND VP.PRIMARY_PAID =0 AND VP.PRIMARY_PENDING >0 AND C.PRIMARY_PAYER_ID >0 AND C.HIDEN=0 THEN SUM(L.AMOUNT) ELSE 0 END AS PRI_INS_AGING,
        CASE WHEN L.RESP_PARTY =2 AND VP.STATUS IN(6,7,5) AND VP.SECONDARY_PAID =0 AND VP.SECONDARY_PENDING >0 AND VP.PRIMARY_PENDING <=0 AND C.SECONDARY_PAYER_ID >0 AND C.HIDEN=0 THEN SUM(L.AMOUNT) ELSE 0 END AS SEC_INS_AGING,
        CASE WHEN L.RESP_PARTY =3 AND VP.STATUS IN(8,9,5) AND VP.TERTIARY_PAID =0 AND VP.TERTIARY_PENDING >0 AND VP.PRIMARY_PENDING <=0 AND VP.SECONDARY_PENDING <=0 AND C.TERTIARY_PAYER_ID >0 AND C.HIDEN=0 THEN SUM(L.AMOUNT) ELSE 0 END AS TER_INS_AGING,
        CASE WHEN L.RESP_PARTY =4 THEN SUM(L.AMOUNT) ELSE 0 END AS PAT_AGING
        FROM VISIT_PROCEDURE VP
        JOIN CLAIM C 
        ON (C.CLAIM_ID = VP.CLAIM_ID AND C.CLINIC_ID = VP.CLINIC_ID)
        JOIN LEDGER L
        ON (L.CLAIM_ID = L.CLAIM_ID AND VP.CLINIC_ID = L.CLINIC_ID)
        WHERE C.CLINIC_ID = 34847 AND L.TYPE IN(1,8,9,10,11) AND L.ACTIVE=1 
        GROUP BY VP.PROCEDURE_ID,L.TYPE,L.RESP_PARTY,L.ACTIVE)T1

Thanks Sunil

Upvotes: 0

Views: 178

Answers (2)

DRapp
DRapp

Reputation: 48139

without extra dissecting your SQL, look into your join on LEDGER... you have it doing the CLAIM_ID on the same value...

JOIN LEDGER L   ON L.CLAIM_ID = L.CLAIM_ID AND VP.CLINIC_ID = L.CLINIC_ID

Should the "L.Claim_ID" be joined to a "VP.Claim_ID"??? or something else?

Ok, so with a little bit of time, I came up with this... I would swap the query around some. In addition, make sure you have an index ON your CLAIM table on Clinic_ID AND Hiden. Also, your inner query is breaking down the SUM of distinct parts of insurance claims, yet you are not doing anything ELSE with them except summing them in the outer. I would change to just sum ONCE at the outer for the given conditions

SELECT STRAIGHT_JOIN
      SUM( IF(   L.RESP_PARTY = 1 
             AND VP.STATUS IN(3,5) 
             AND VP.PRIMARY_PAID = 0 
             AND VP.PRIMARY_PENDING > 0 
             AND C.PRIMARY_PAYER_ID > 0, L.AMOUNT, 0 )

           +
           IF(   L.RESP_PARTY = 2 
             AND VP.STATUS IN(6,7,5) 
             AND VP.SECONDARY_PAID = 0 
             AND VP.SECONDARY_PENDING > 0 
             AND VP.PRIMARY_PENDING <= 0 
             AND C.SECONDARY_PAYER_ID > 0, L.AMOUNT, 0 )

           +
           IF(   L.RESP_PARTY = 3 
             AND VP.STATUS IN(8,9,5) 
             AND VP.TERTIARY_PAID = 0 
             AND VP.TERTIARY_PENDING > 0 
             AND VP.PRIMARY_PENDING <= 0 
             AND VP.SECONDARY_PENDING <= 0 
             AND C.TERTIARY_PAYER_ID > 0, L.AMOUNT, 0 ) ) as INS_AGING,

      SUM( IF( L.RESP_PARTY = 4, L.AMOUNT, 0 )) as PAT_AGING

   FROM 
      CLAIM C
         JOIN VISIT_PROCEDURE VP
            ON C.CLAIM_ID = VP.CLAIM_ID
           AND C.CLINIC_ID = VP.CLINIC_ID

             JOIN LEDGER L
                ON VP.CLINIC_ID = L.CLINIC_ID
               AND VP.CLAIM_ID = L.CLAIM_ID
               AND L.TYPE IN ( 1, 8, 9, 10, 11 ) 
               AND L.ACTIVE = 1 
   WHERE 
          C.CLINIC_ID = 34847 
      AND C.HIDEN = 0

Upvotes: 2

Brian
Brian

Reputation: 6450

If you're looking to get the SUM() of your data, I don't see you getting it any other way. That said, this is pretty complex and you might benefit from breaking it down into a stored proc and splitting the calculations up into stages.

Btw what does slow mean here? 1 second rather than 0.001? Or, 2 minutes?

Upvotes: 0

Related Questions