SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Slow query when you add an order by
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 02/20/2008 :  14:44:51  Show Profile  Reply with Quote


SELECT Column1,Column2,Column3 ....
FROM vwMyView
ORDER 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 missing

Thanks





tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 02/20/2008 :  15:16:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/20/2008 :  15:19:51  Show Profile  Reply with Quote
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/20/2008 :  15:35:18  Show Profile  Reply with Quote
What are you going to do with 10k rows in the first place?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 02/20/2008 :  15:54:42  Show Profile  Reply with Quote
Brett, based on information retrieved from 10000 rows I generate XML (in a web application) to draw a graph
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/20/2008 :  16:46:38  Show Profile  Reply with Quote
Did you try the index?

You could probably add the column to an existing as well

how many rows does the table contain right now

Also, why not post your query

It's in a stored procedure correct?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000