July 5, 2006 at 10:46 am
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.
July 5, 2006 at 11:02 am
In order to index a view, it must be created with SCHEMABINDING option in force. This is not the default. If the view is to be indexed you will need to drop and recreate it with this option.
Personally, I would tune the underlying tables. Have you tried running the query through the base tables and seen what the execution plan is? Could you use the index tuning wizard on such a query?
July 5, 2006 at 11:20 am
Agreed - try tuning the underlying query first.
One important thing to note is that an indexed view will actually exist as an object in the schema. Another thing is that it can SIGNIFCANTLY affect write performance on the underlying tables. Take this into careful account.
We implemented indexed views for our post billing reports but then found that it increased the time it took for billing to process by almost 200%!!! This is due to the fact that the data in the view has to be updated as the underlying tables change.
If you do implement them be sure to test all functionality that would use these related objects!
July 5, 2006 at 12:13 pm
Here is the code, I dont understand what needs to be tuned:
Please note that MedicRecs has 90millon rows
select Sum(MED.ConsultTime) as CallDuration,
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
July 5, 2006 at 1:09 pm
You can only use INNER JOIN for an indexed view.
I take it that a MedicRecs record might not have a record in
Where possible I design my databases so that LEFT/RIGHT JOINs are kept to a minimum. To achieve this I would have a specific entry in the Ratings table (Say Rating_Id = -1 with a specific entry of "Unrated") so that MED.Rating_Id will be -1 if the item has been unrated.
This technique will allow you to switch to INNER JOINS
I would also qualify the table names with their owner
dbo.MedicRecs etc. If the person hitting the query is not dbo then SQL Server will first look for .MedicRecs before falling back to dbo.MedicRecs so you will gain a slight performance boost there.
Can I assume that your database has foreign keys?
If so MedicRecs.Rating_Id may benefit from being indexed as will
MecidRecs.Provider_Id etc.
For 90 million records indexing that little lot might take some time and disk space.
TEMPDB will have to be pretty big to deal with it.
Does the view need to bring back ALL rows?
If you could put a WHERE clause in there to limit the number of rows brought back then that would lighten the load. For example, if the vast majority of MediRecs are irrelevant due to their age then have a criteria that excluded ancient records.
July 5, 2006 at 2:23 pm
Did anyone mentioned that Indexed Views are working in Enterprise Edition only in SQL Server 2000 (from production editions)? Developer has it too because it is development edition for Enterprise Edition
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx
Regards,Yelena Varsha
July 5, 2006 at 3:08 pm
Yelena: That's a typo from early documentation that hasn't been corrected everywhere.
Indexed views may be used in SQL 2000 Standard Edition by specifying the NOEXPAND index hint. All editions allow creation of indexed view, but if you select from an indexed view in standard edition, it treats the view like a normal non-indexed view, as in it just reads the view definition like a query. If you include the NOEXPAND hint, however, then the query plan will use your view's indexes:
SELECT Col1, Col2
FROM dbo.MyIndexedView WITH (NOEXPAND)
WHERE Col1 > 1000
The real key to getting indexed view to work is ensuring that all of the SET options are properly set on the view, the underlying tables, and the database.
-Eddie
Eddie Wuerch
MCM: SQL
July 6, 2006 at 2:47 am
The advice of tuning the underlying tables, and using a dummy entry (eg -1) so you can convert your outer joins to inner joins is good. If you still do not have the performance you need, some of the ideas below may help.
1) A GROUP BY causes a sort to be done. Your GROUP BY include fields called 'descriptive_text', which are probably many characters wide. If you can get the results you want by using r.rating_id instead of r.descriptive_text (etc) in your GROUP BY, you will sort FAR less data and you sort will run much faster.
Note that if you think using descriptive_text will give your results in alphabetic sequence this is not correct. You must use ORDER BY to guarantee ordering of results.
2) You will need to check this at your site, but I think a lot of your query time is taken producing the SUM(MED.xxx) results. You could consider an indexed view on MecicRecs only, with an index on Rating_id,Providers_id,Locale_id. (Take into account what has been said about IUD processing on MedicRecs!)
create view MedSummary as select Sum(MED.ConsultTime) as CallDuration,MED.VisitSRN,MED.CheckedSRN,Sum(MED.charge) as Cost,MED.rating_id,MED.Providers_ID,locale_id,MedicPRoviders_id from MedicRecs MED Group By VisitSRN,CheckedSRN,rating_id,Providers_ID,locale_id,MedicPRoviders_id
You could then have another view that does the LOJs with your dimension tables to get the descriptions you want in the final results. This final view would still need a GROUP BY to get the count() information you want from Providers, but the work needed when you select from the final view would be far less than your original posting.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 7, 2006 at 11:12 am
To Eddie Wuerch:
Thanks for the explanation. I did not know about that NoExpand option. I will try on my Standard Edition.
Yelena
Regards,Yelena Varsha
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply