Required sql query Optimization

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

  1. How long does the inner query take if instead of WHERE ISNULL(MEMECK, '') != '' you test it with MEMECK IS NOT NULL
  2. Also add this pc.ClaimType IN ('R','O') in the inner query and try
  3. So you do have Index on Member_PharmacyClaim.PharmacyNo, Member_Pharmacy.PharmacyNo and Member_PharmacyClaim.ClaimType?

Are you saying the table used in the subquery has 10 million rows and the query returns over 23 million?

How long does the subquery take.
How long does the query take if just join the subquery to one of the other tables.
If the subquery doesn't take a long time populate a temp table from it, index it then join to the others and see how long that takes. If that's ok you could look at creating another temp table from that with just the join columns and using that to join to all three tables

In the subquery you could add
having MIN(ClaimType) in ('R','O') - don't know if that would make any difference.
You also might look at whether you can reduce the number of rows queried by this.

How often are you running this and how volatile is the table?
I would look at trying to maintain the aggregates when the claim table is updated or keep an accumulating snapshot.
At the end of each day copy that days aggregates rows to another table.
At the end of the month aggregate to a monthly table.
Then you would only be querying the current day rows from the live table - everything else would be aggregated already.

Thanks for Reply all.

we r trying to change logic and discussing with business team, but the only sub query is taking
long time for run (1 hour - 10 millions records).

Regards

Rajnidas