Query Performance - Speed Up

[saw15]

Recently started working with this query that I picked up from a co-worker, and it is extremely slow.

I have added what I would consider at min the proper indexes used within the where clause, but it is still taking way too long.

Any thoughts or tips for re-writting this for max eff would be appreciated.

SELECT DISTINCT CaseT.CaseNum CaseNum
FROM CaseTable CaseT
LEFT JOIN CaseLocationtable CLT ON CaseT.CaseNum = CLT.CaseNum
INNER JOIN CodeTable CodeT ON CaseT.Status = CodeT.Code,
baseclaimrates bcr
left join CaseLocationTable A on bcr.planoption = A.hmoplan and
bcr.healthplan = 'HMO' and A.hmoplan is not null
left join CaseLocationTable B on bcr.planoption = B.posplan AND
bcr.healthplan = 'POS' and B.posplan is not null
left join CaseLocationTable C on bcr.planoption = C.posplusplan AND
bcr.healthplan = 'POS+'
and C.posplusplan is not null
WHERE 1=1
AND ((CaseT.CaseNum = A.CaseNum)
OR(CaseT.CaseNum = B.CaseNum)
OR(CaseT.CaseNum = C.CaseNum))
AND (CaseT.GroupType = bcr.GroupType)
AND ((CaseT.ReceivedDate >= bcr.EffDate)
AND (CaseT.ReceivedDate <= bcr.EndDate))
AND bcr.plan_key = '122'

 

[sguslan]

Barring any general tuning problems like hardware capabilities or server available resources, i.e. server being dedicated, i would remove 1=1 in the where clause as a starting point ...

________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[sguslan]

I quote the link below. &quot;I was able to duplicate the problem from the application, so my first step to finding out what caused the problem was to run a trace in Profiler. This easily identified the guilty query.

I cut the slow-performing query from the trace window and pasted it into the Query Analyzer, then ran the &quot;Perform Index Analysis&quot; option. This resulted in the suggestion that I add a new index to the table the query was hitting.

I added the index and then re-ran the operation from the application. Now, instead of taking over 10 minutes to run, the operation now ran in about 5 seconds.&quot;

The link :

http://www.sql-server-performance.com/case_studies.asp

________________________________________________________________________________
If you do not like change, get out of the IT business...