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 |
|
cshah1
Constraint Violating Yak Guru
347 Posts |
 Posted - 2008-02-20 : 14:44:51
|
| SELECT Column1,Column2,Column3 ....FROM vwMyViewORDER BY CreatedDT View has about 10000 rows, If I remove order by query runs faster but adding an order by cause query to timeout..All tables have clustered index based on the primary key of the table..(a) Should I create an index view with CreatedDT as non clustered index?(b) Or create a non clustered index on CreatedDT column on the underlying table?I can provide DDL but if something obvious I am missingThanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-20 : 15:16:38
|
| Yes you should add an index to CreatedDT. I wouldn't bother with an indexed view, just put it on the table.You may even want to make it the clustered index and change the PK to be non-clustered, just depends how often you order by CreatedDT.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-20 : 15:19:51
|
| Nice to see Tara's Reply after long time:Here are the requirements for index views:1) view has to be schema- bounded.2) Enterprise and Developer edition only supports index view.3) sometime you have to force to use index on views. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-02-20 : 15:54:42
|
| Brett, based on information retrieved from 10000 rows I generate XML (in a web application) to draw a graph |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Slow query when you add an order by