1
\$\begingroup\$

This stored procedure is taking a lot of time to execute. How can I optimize this query?

USE `asddadad`$$

DROP PROCEDURE IF EXISTS `repbillingpaymentsummary`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `repbillingpaymentsummary`(IN ptype VARCHAR(100), IN pAccountCodes VARCHAR(4000),
IN pFromDOS DATE, IN pToDOS DATE, IN pFromDOP DATE, IN pToDOP DATE, IN pFromDOE DATE, IN pToDOE DATE,
IN psalesCodes VARCHAR(4000))
BEGIN

    IF ptype = 'accountCode' THEN
        SELECT 
            practiceCode AS dataCode, NAME AS dataName, 
            IFNULL(SUM(IFNULL(creditAmount,0)),0) AS totalPayment 
        FROM 
            claimdetail clm, practice prac

        WHERE
            clm.practiceCode = prac.code
        AND
            clm.transType = 2
        AND
            clm.active = 1
        AND 
            clm.fromDOS >= COALESCE(pFromDOS,clm.fromDOS)
        AND 
            clm.fromDOS <= COALESCE(pToDOS,clm.fromDOS)
        AND 
            clm.DOP >= COALESCE(pFromDOP,clm.DOP)
        AND 
            clm.DOP <= COALESCE(pToDOP,clm.DOP)
        AND 
            clm.DOE >= COALESCE(pFromDOE,clm.DOE)
        AND 
            clm.DOE <= COALESCE(pToDOE,clm.DOE) 
        AND
            FIND_IN_SET(clm.practiceCode,COALESCE(pAccountCodes,clm.practiceCode))
        AND
            FIND_IN_SET(IFNULL(clm.accountGroupName,'NA'),COALESCE(psalesCodes,IFNULL(clm.accountGroupName,'NA')))          

        GROUP   BY 
            clm.practiceCode

        ORDER BY
            totalPayment DESC;
    END IF;

    IF ptype = 'insCode' THEN
        SELECT 
            tranPatientInsuranceCode AS dataCode, NAME AS dataName, 
            IFNULL(SUM(IFNULL(creditAmount,0)),0) AS totalPayment
        FROM 
            claimdetail clm, insurance ins

        WHERE
            clm.tranPatientInsuranceCode = ins.code
        AND
            clm.transType = 2
        AND
            clm.active = 1
        AND 
            clm.fromDOS >= COALESCE(pFromDOS,clm.fromDOS)
        AND 
            clm.fromDOS <= COALESCE(pToDOS,clm.fromDOS)
        AND 
            clm.DOP >= COALESCE(pFromDOP,clm.DOP)
        AND 
            clm.DOP <= COALESCE(pToDOP,clm.DOP)
        AND 
            clm.DOE >= COALESCE(pFromDOE,clm.DOE)
        AND 
            clm.DOE <= COALESCE(pToDOE,clm.DOE) 
        AND
            FIND_IN_SET(clm.practiceCode,COALESCE(pAccountCodes,clm.practiceCode))
        AND
            FIND_IN_SET(IFNULL(clm.accountGroupName,'NA'),COALESCE(psalesCodes,IFNULL(clm.accountGroupName,'NA')))
        AND
            IFNULL(zeroTrans,0) = 0
        GROUP   BY 
            clm.tranPatientInsuranceCode

        ORDER BY
            totalPayment DESC;
    END IF;


    IF ptype = 'CPTCode' THEN
        SELECT 
            CPTCode AS dataCode, cpt.shortDescription AS  dataName, 
            IFNULL(SUM(IFNULL(creditAmount,0)),0) AS totalPayment
        FROM 
            claimdetail clm, cptcodes cpt

        WHERE
            clm.CPTCode = cpt.code
        AND
            clm.transType = 2
        AND
            clm.active = 1
        AND 
            clm.fromDOS >= COALESCE(pFromDOS,clm.fromDOS)
        AND 
            clm.fromDOS <= COALESCE(pToDOS,clm.fromDOS)
        AND 
            clm.DOP >= COALESCE(pFromDOP,clm.DOP)
        AND 
            clm.DOP <= COALESCE(pToDOP,clm.DOP)
        AND 
            clm.DOE >= COALESCE(pFromDOE,clm.DOE)
        AND 
            clm.DOE <= COALESCE(pToDOE,clm.DOE) 
        AND
            FIND_IN_SET(clm.practiceCode,COALESCE(pAccountCodes,clm.practiceCode))
        AND
            FIND_IN_SET(IFNULL(clm.accountGroupName,'NA'),COALESCE(psalesCodes,IFNULL(clm.accountGroupName,'NA')))

        GROUP   BY 
            clm.CPTCode

        ORDER BY
            totalPayment DESC;
    END IF;



    END$$

DELIMITER ;
\$\endgroup\$
  • \$\begingroup\$ I suspect table structure details, including datatypes, indexes and keys would be useful in interpreting your procedure. \$\endgroup\$ – forsvarir May 21 '16 at 10:11
  • \$\begingroup\$ Hi @sitansu-s-swain, for the 3 sql blocks, how many seconds does each of the query takes to run? \$\endgroup\$ – Andy K May 21 '16 at 10:19
  • 1
    \$\begingroup\$ Could you please explain what the code does and also update the title with that information. \$\endgroup\$ – ferada May 21 '16 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.