How Can i Tune this Query ! Sugestions welcome
-
Hi All
I am running a query on a view, the query returns a list of sums, and couple of group by's. This view has around 60m records on it.
It joins to some other table, the foreign keys on the other tables have got indexes on them.
I need to find a way to tune the Query, but the view has been created and hasn't got an index.
Is there anything i can do ? I tried indexexing the View, but its not allowing me. MedicRecs is a view that is not indexed !
Here is the code, I dont understand what needs to be tuned:
Please note that MedicRecs has 90millon rows
select Sum(MED.ConsultTime) as ConsultDuration,
MED.VisitSRN,
MED.CheckedSRN,
Sum(MED.charge) as Cost,
r.description as Rate,
Count(ProvidersRecognitionMethod) as VisitCount,
c.description as Providers,
MED.Providers_ID,
l.Descriptive_Text as Locale,
cs.Descriptive_Text as Service
from MedicRecs MED
left join Rating r on MED.Rating_id = r.Rating_id
left join Providers c on MED.Providers_id = c.Providers_id
left join Locale l on MED.Locale_id = l.Locale_id
left join MedicPRoviders cs on MED.MedicPRoviders_id = cs.MedicPRoviders_id
Group By MED.VisitSRN,
MED.CheckedSRN,
r.Descriptive_Text,
c.Descriptive_Text,
MED.Providers_ID,
l.Descriptive_Text,
cs.Descriptive_Text
-
John
Have a look at the execution plan and see if you can tell what part of the query is taking the most resources. Do you have an index on each column that you are joining to in the other tables, and if so, are they being used? Another thing to check is the definition of the view itself. If you don't have the correct indexes on the underlying tables then this will cause you problems. Also, read up in Books Online about indexed views - it may be that you need to replace your view with an indexed view.
John
-
This is a very good link please review it
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_536v.asp
Minz..
"More Green More Oxygen !! Plant a tree today"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply