November 28, 2006 at 10:22 pm
I have a slow running query and I'm not real familiar with indexes. Do any of you have any suggestions on how to index the query below?
SELECT mp.PatientFirstName, mp.PatientLastName, sp.BillingProviderInstitutionName,
sp.BillingProviderFirstName, sp.BillingProviderLastName, rsd.ClaimNumber, rsd.ClaimKeyNumber,
rsd.TotalBilledAmt, rsd.TotalPaidAmt, rsd.HeaderHIPAAClaimStatusCatCode, rsd.PricedInd,
CheckDate=coalesce(max(rsd.CheckDate),max(rsd.LineStatusDate),max(rsd.AdjudicationDate)),
rsd.DataSourceID,ServiceBeginDate = min(sp.ServiceBeginDate),
ServiceEndDate = max(sp.ServiceEndDate)
FROM RenderedServiceDetails rsd (nolock)
INNER JOIN MemberPatient mp (nolock) ON mp.RSDID = rsd.RSDID
INNER JOIN ServiceProvider sp (nolock) ON rsd.rsdid = sp.rsdid
INNER JOIN ReportingEntityGroup rg (nolock) ON rsd.ReportingEntityKey = rg.ReportingEntityKey
WHERE rg.ReportingGroupKey IN (869141) and sp.LineStatusCode IN ('F','D','A','B','R','P')
and ((sp.ServiceBeginDate between '10/02/2006' and '10/02/2006')
OR ('10/02/2006' BETWEEN sp.ServiceBeginDate and sp.ServiceEndDate))
GROUP BY sp.BillingProviderFirstName, sp.BillingProviderLastName, sp.BillingProviderInstitutionName,
rsd.ClaimNumber, rsd.ClaimKeyNumber, mp.PatientFirstName, mp.PatientlastName,
rsd.TotalBilledAmt, rsd.TotalPaidAmt, rsd.HeaderHIPAAClaimStatusCatCode, rsd.PricedInd,
rsd.DataSourceID
ORDER BY sp.ServiceBeginDate DESC, mp.PatientLastName
November 29, 2006 at 2:18 am
Hi,
I think you should consider your where clause to modify. I think that it is not too good
WHERE
rg.ReportingGroupKey =869141 and
and ((sp.ServiceBeginDate = '10/02/2006'
OR ('10/02/2006' BETWEEN sp.ServiceBeginDate and sp.ServiceEndDate))
And
sp.LineStatusCode IN ('F','D','A','B','R','P')
In my suggestion if you can use UNION ALL in place of OR then It may be work better then this.
Cheers
cheers
November 29, 2006 at 5:02 am
Hi jmowery
If there is proper Indexing on working tables then it must improve the performance.
There is Simple concept behind Indexing. Make Indexs on columns used in Joins as well as Where criteria in the Query. It will really improve your query performance. There is some other aspects like Clustered Indexes or Non-Clustered Indexes.. and lot more How data will query etc. But I'm trying to give solution you can move ahead...
And Using UNION ALL is not a good idea, U can not use every time UNION ALL in place of IN Keyword.
U Should consider the fallowing Tables columns for Indexing....
1. RenderedServiceDetails.RSDID
2. MemberPatient.RSDID
3. ServiceProvider.ReportingEntityKey
4. RenderedServiceDetails.ReportingEntityKey
5. ReportingEntityGroup.ReportingGroupKey
6. ServiceProvider.LineStatusCode
7. ServiceProvider.ServiceBeginDate --> If Possible Make Clustered Index with Descenging Order.
And there is other aspects how much data U R retrieving, If it is normal data size then ok other wise there will need to add values in the query.
First Try this
If U donot get the desired warm to query then Most Welcome again!
Bhudev Prakash
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply