Using date in order by

Question

Using date in order by

David Kranes

SSCrazy

Points: 2523
More actions
August 1, 2008 at 11:26 am

#130432

Hello, I have a query the runs very fast and is ordered by order_id. As soon as I add the order_date into the ordey by the query crawls. Are there any techniques to speed up queries sorted by date?
Thank you
-David
Best Regards,
~David

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Odds are your clustered index is on order_id so no sorting actually has to be done. When you add order_date then the server actually has to apply a sort and, if you do not have an index on order_date it will be even slower.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736193

August 1, 2008 at 12:33 pm

#852115

index on date.

Steve F. Mr or Mrs. 500 Points: 587 More actions · Answer 3

Steve F.

Mr or Mrs. 500

Points: 587

August 5, 2008 at 7:36 am

#853174

Index on (id, date)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Steve F. (8/5/2008)
Index on (id, date)

That is ALMOST the 'most correct' answer. 🙂 I think the best answer is to have a CLUSTERED index on order_id, order_date. That will allow for returning the data ordered by those two columns without an intermediate sort being required.

However, I will venture a guess that order_id is the Primary Key for this table, and obviously adding order_date to it may be inappropriate. It will also bloat the clustered index, which will bloat all non-clustered indexes.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

No problem... PK can be nonclustered.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Jeff Moden (8/6/2008)
No problem... PK can be nonclustered.

Yep. And then we have this situation:

Clustered index: order_id (assume int - 4 bytes), order_date (assume datetime - 8 bytes), uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI

non-clustered PK index: order_id (4 bytes), clustered index pointer (16 bytes)

Yucko, but functional. 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

TheSQLGuru (8/6/2008)
Jeff Moden (8/6/2008)
No problem... PK can be nonclustered.
Yep. And then we have this situation:
Clustered index: order_id (assume int - 4 bytes), order_date (assume datetime - 8 bytes), uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI
non-clustered PK index: order_id (4 bytes), clustered index pointer (16 bytes)
Yucko, but functional. 😀

In the is scenario you might as well have the CLustered Index be your PK and I don't think it would need a uniqueifier because order_id has to be unique since it is the PK so adding order_date would still keep the key unique.

I would test it with the order_id staying the PK and just adding a non-clustered index on order_date.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

In the is scenario you might as well have the CLustered Index be your PK and I don't think it would need a uniqueifier because order_id has to be unique since it is the PK so adding order_date would still keep the key unique.
I would test it with the order_id staying the PK and just adding a non-clustered index on order_date.

1) adding order_date to PK of order_id would allow these two rows in, which would clearly be incorrect:

Order_id Order_date

10 1/1/2008

10 1/2/2008

2) a non-clustered index on order_date will not help with avoiding a sort if you have a clustered index of just order_id and wish to sort on order_id, order_date (unless those are the only 2 columns in the output - a trivial case I am not considering applicable to the OP).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

David Kranes SSCrazy Points: 2523 More actions · Answer 9

The primary key is actually on order_id, retailer_id and transition_seq. The situation is orders are stored by stores and a sequence. for some reason order ids are NOT unique across stores. The transition sequence is an id that increments as the order transitions from one state to another. There is also an order_state column which is NOT part of the primary key.

What seems to have really sped this query up is first adding an order_date range of 1 month. That is more realistic than not limiting the range. Also, the application limits the result set to the first 1000 rows. By using select top 1000 in the query also made a huge difference. The full results are now returned in under 5 seconds. There is no reason for the client to select 300,000+ rows and then sort them only to post the top 1000 to the screen. Adding these filters has increased performance dramatically.

I tried fooling around with indexes but it made no noticeable improvement.

Thank you all for your input.

Best Regards,

~David

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Kewl! You just stumbled across the fasted IO possible - IO that isn't performed at all! :hehe:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

TheSQLGuru (8/6/2008)
Jeff Moden (8/6/2008)
No problem... PK can be nonclustered.
Yep. And then we have this situation:
Clustered index: order_id (assume int - 4 bytes), order_date (assume datetime - 8 bytes), uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI
non-clustered PK index: order_id (4 bytes), clustered index pointer (16 bytes)
Yucko, but functional. 😀

Not quite sure that's what I meant. Clustered Index on Order_ID and Order_Date should be Unique. That'll take out the 4 extra bytes.

Admittedly, I didn't consider the CI pointer on the non-clustered PK, though. Guess I'll have to do a test and see what happens. Thanks for the reminder.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

Heh... never mind... OP says the PK is actually 3 columns... really makes all of this a moot point. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

TheSQLGuru (8/6/2008)
Jeff Moden (8/6/2008)
No problem... PK can be nonclustered.
Yep. And then we have this situation:
Clustered index: order_id (assume int - 4 bytes), order_date (assume datetime - 8 bytes), uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI
non-clustered PK index: order_id (4 bytes), clustered index pointer (16 bytes)
Yucko, but functional. 😀

Actually - I don't think Jeff is implying not making the clustered index unique. He's simply talking about making the primary key based on a non-clustered (yes also unique) index.

There would be no need to have the extra junk in the clustered index.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

karthik M One Orange Chip Points: 29139 More actions · Answer 14

what do you mean by CI pointer ? Can anybody explain it ?

karthik