Post reply

Using date in order by

  • 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

  • August 1, 2008 at 12:28 pm

    #852101

    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

  • August 1, 2008 at 12:33 pm

    #852115

    index on date.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 5, 2008 at 7:36 am

    #853174

    Index on (id, date)

  • August 6, 2008 at 8:31 am

    #853853

    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

  • August 6, 2008 at 8:33 am

    #853856

    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)
    Intro to Tally Tables and Functions

  • August 6, 2008 at 8:55 am

    #853886

    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

  • August 6, 2008 at 9:07 am

    #853894

    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

  • August 6, 2008 at 9:15 am

    #853905

    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

  • August 6, 2008 at 9:41 am

    #853927

    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

  • August 6, 2008 at 10:07 am

    #853964

    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

  • August 6, 2008 at 5:59 pm

    #854177

    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)
    Intro to Tally Tables and Functions

  • August 6, 2008 at 6:02 pm

    #854178

    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)
    Intro to Tally Tables and Functions

  • August 6, 2008 at 6:45 pm

    #854184

    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?

  • August 13, 2008 at 4:15 am

    #857265

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

    karthik

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

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