Hi every one ,
i have to optimized the below query can anyone help me on this. its taking more than one hour for run and total records (23562843 ). all column has indexed properly and used #temp table also but no improvement. The table - Member_PharmacyClaim has huge data (10 million records).
SELECT
pc.ClaimNo + '_' + Cast(pc.SeqNo as varchar(5)) AS DRUG_CLAIM_NUMBER
,pc.SeqNo AS DRUG_CLAIM_SEQUENCE_NUMBER
,M.MEMBER_ID_1 AS MEMBER_ID_NUMBER
,pc.PrescriptionNo AS PRESCRIPTION_RX_NUMBER
,pc.DateSvc AS PRESCRIPTION_FILL_DATE
,pc.NewRefillInd AS REFILL_INDICATOR
,pc.NDC AS DRUG_NDC_NUMBER
,pc.PrescriberDEA AS PRESCRIBING_PHYSICIAN_DEA_NUMBER
,pc.DaysSupply AS DAYS_SUPPLIED
,pc.DrugName AS DRUG_NAME_AND_STRENGTH
,phm.PharmacyNo AS PHARMACY_NABP_NUMBER.
,NULL AS PRESCRIBING_PROVIDER_NUMBER
,CASE
WHEN phm.StatusCode ='10' THEN 'M'
ELSE 'R'
END TYPE_INDICATOR
,CASE
WHEN pc.GenericBrandInd = '1' THEN 'G'
ELSE 'B'
END AS BRAND_OR_GENERIC
,pc.DrugStrength AS DRUG_STRENGTH
FROM Suppliers M
INNER JOIN( SELECT ClaimNo, SeqNo, MEMECK
,Min(PrescriptionNo) AS PrescriptionNo
,Max(DateSvc) AS DateSvc
,Min(NewRefillInd) AS NewRefillInd
,Min(NDC) AS NDC
,Min(PrescriberDEA) AS PrescriberDEA
,Min(DrugName) AS DrugName
,Sum(QtySubmitted) AS QtySubmitted
,Min(DaysSupply) AS DaysSupply
,SUM(AmtPaid) AS AmtPaid
,Min(GenericBrandInd) AS GenericBrandInd
,MIN(DrugStrength) AS DrugStrength
,SUM(MemberDeductibleAmt) AS MemberDeductibleAmt
,SUM(PlanCoinAmt) AS PlanCoinAmt
,SUM(MemberCoPayAmt) AS MemberCoPayAmt
,SUM(AmtRejected) AS AmtRejected
,SUM(MemberPaidAmt) AS MemberPaidAmt
,Max(DateCutoff) AS DateCutoff
,SUM(OriginalPlanAllowedAmt) AS OriginalPlanAllowedAmt
,SUM(AmtBilled) AS AmtBilled
,MAX(DateTimeStamp) AS DateTimeStamp
,MAX(ISNULL(PrescriberNPI,'')) AS PrescriberNPI
,MIN(ClaimType) AS ClaimType
,MIN(PharmacyNo) AS PharmacyNo
FROM Member_PharmacyClaim WITH (NOLOCK)
WHERE ISNULL(MEMECK, '') != ''
GROUP BY ClaimNo, SeqNo, MEMECK ) AS pc
ON M.MEME_CK = pc.MEMECK
JOIN Member_Pharmacy AS phm WITH (NOLOCK)
ON phm.PharmacyNo = pc.PharmacyNo
WHERE pc.ClaimType IN ('R','O')
Thanks
Rajni