Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
craigwg
Posting Yak Master
154 Posts |
 Posted - 2010-06-29 : 17:13:34
|
Hello to all,I have a rather large view with lots of columns (70+),few rows (less than 20), but there will be MANY rows in the future (thousands). It is currently taking 20 seconds to load the view but soon will be pushed to production. Currently there are NO Indexes of any kind and I have been tasked with adding indexes to make it faster.The view itself pulls from 10 tables across 3 databases and those tables all have Primary Key indexes for the most part.Now to my question. Do I want to create indexes on the tables or the view? I keep going back and forth on that. Based on my research I know I want to create indexes on columns used in the where statement and the join statements, but not sure how to pick which columns will exist in covering indexes or whether or not covering indexes should be used.The FROM and WHERE statements of the stored procedure that is pulling data from the view looks like this: FROM vw_operations o LEFT JOIN Reports_Dev..vw_info_cms c on o.GCO_001 = c.SubsidiaryName WHERE c.subsidiaryid = @subsidiaryid and MONTH(CONVERT(DATETIME, o.description))=@month and YEAR(CONVERT(DATETIME, o.description))=@year ORDER BY Revenue DESC, [Client Name] ASC As I said there are 70 or so columns being pulled in this stored procedure but I think it's NOT wise to use them in the query. Thanks for your help!Craig Greenwood |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2010-06-29 : 17:21:50
|
| Sorry. When I say "Load" the view I mean, run a select statement against the view. There are 20 rows NOW, but there are about to be thousands. It takes 20 seconds to run locally and when this is International (which it will be) it's going to take 6 minutes to use it in France which is unacceptable. That's why my goal is to surgically add these indexes so its exactly right. I'm not sure what a DDL is. Do you need more than the FROM statement?ThanksCraig Greenwood |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-06-29 : 17:23:07
|
| For the most part you shouldn't need an indexed view. However, there are a couple of times where I have used them to help performance on a faily high transaction system (couple billion transactions per day). That having been said, it might help in your case. But, if the querey you posted above is any indication of the code in the view, you'll want to optomize that first. Specifically, the use of fucntions on "o.description" are not sargable and will not index seek. |
|
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2010-06-29 : 18:17:24
|
| I ran the database tuning advisor and used my stored procedure as the workload. This suggested a few indexes and statistics which I created. I hope my approach was valid! I have run my stored procedure a few times and in dev it now runs 75% faster (4 seconds to 1 second). I ran it again and it took 4 seconds again. I think the statistics are just settling into place. Would love comments and feedback on this approach.Craig Greenwood |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-07-01 : 02:47:23
|
| You should really post your view definition instead...a lot of stuff like this will kill your performance easily: MONTH(CONVERT(DATETIME, o.description)) = @month and YEAR(CONVERT(DATETIME, o.description)) = @year- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
|
|
|
|
Adding indexes to a view