Order by and indexes

Last Post 20 Apr 2008 08:04 PM by trans53. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
19 Apr 2008 10:25 AM
Hi all,

I have orders table with 30M rows and trying to understand which index is better for Order by clause.

OrdersID int Primary key identity
CreateDate datetime
customerID int

OrdersId column defined as a clustered index and CreateDate is non-clustered index.
Both columns can be used to get the latest order from the customer.

When i issue statements below both produce exactly the same subtree cost and the the only difference is one is clustered index scan and another index scan.

SELECT TOP 1 OrdersID FROM Orders (NOLOCK)
ORDER BY OrdersID Desc

SELECT TOP 1 OrdersID FROM Orders (NOLOCK)
ORDER BY createdate Desc

My question is which way is preferrable for Order by clause? By clustered or non-clustered index?



The reason why is started to look into this is because above table did not have an index on CreateDate and one of the stored procedures had a peace of code like
where one customer in a customer table may have 600000 K orders and it took more than 30 sec to return 1 record.
As soon as modified the code to do an Order by OrdersID the record was returned instantly.
Is this because Order by didn't have an index?

-- No index on CreateDate column

-- before (slow)
From Customer c (NOLOCK)
inner join Orders O (NOLOCK) ON O.OrdersID = (SELECT TOP 1 p.OrdersID FROM Orders p (NOLOCK)
WHERE p.CustomerID = C.CustomerID ORDER BY p.CreateDate Desc)

-- after (instant)
From Customer c (NOLOCK)
inner join Orders O (NOLOCK) ON O.OrdersID = (SELECT TOP 1 p.OrdersID FROM Orders p (NOLOCK)
WHERE p.CustomerID = C.CustomerID ORDER BY p.OrdersID Desc)


Thanks
SQLUSA
New Member
New Member

--
19 Apr 2008 10:55 AM
ORDER BY is done on the internal result set after the SELECT has been joined & filtered.

It is not using indexes.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SQLUSA
New Member
New Member

--
19 Apr 2008 12:45 PM
quote:

Originally posted by: rm
But helps if index's sort order is same as result's sort order.



Agreed.
trans53
New Member
New Member

--
20 Apr 2008 08:04 PM
Thank you so much
You are not authorized to post a reply.

Acceptable Use Policy