none
Select with datetime on a million rows table RRS feed

  • Question

  • I usually deal with data must less, adding index would be just fine.

    But this time, my science project generate 15 sample a second each channel, a node has 4 channels. and there are many nodes, so 15*60*60*24*4 is number of data for one node a day. 

    There will be many nodes (20~50) and will run 24*7 for at least a year. the table would grow more than 37,843,200,000.

    My table after normalization is like "Id","datetime","Value","ChannelId", my query is mostly against datetime

    "select * from table where datetime between t1 and t2"

    I have only loaded 1 day data of 1 node into it, and this select took me about 3s. I think it would get a lot worse after all data are loaded.

    I don't have the SQL analyzer, what could I do to improve select performance?

    Tuesday, March 6, 2012 1:55 AM

Answers

All replies

  • Since you are doing a range select primarily on the datetime column, you can try making that the clustered index column. Also, you should seriously look into partitioning the table if you are going to have that kind of volume.

    Also, are your queries concerned with just the date, just the time, or both the date and time? 


    Dave Frommer - BI Architect - Independent

    Tuesday, March 6, 2012 2:11 AM
  • Since you are doing a range select primarily on the datetime column, you can try making that the clustered index column. Also, you should seriously look into partitioning the table if you are going to have that kind of volume.

    Also, are your queries concerned with just the date, just the time, or both the date and time? 


    Dave Frommer - BI Architect - Independent

    This is only valid if the DATETIME column contains unique values.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Tuesday, March 6, 2012 2:31 AM
  • Note I said clustered index, not unique clustered index


    Dave Frommer - BI Architect - Independent

    Tuesday, March 6, 2012 2:41 AM
  • When you create a clustered index in SQL Server it's automatically created as a unique index therefore you should advise the OP to create a non-unique CI.

    Whether the OP should use a non-unique clustered index is up for debate as there are additional overheads asociated with using them.  The OP should do some research on using non-unique CI's before they implement them and I suggest they start here http://www.sqlskills.com/BLOGS/KIMBERLY/post/Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    • Proposed as answer by Naomi NModerator Tuesday, March 6, 2012 3:09 AM
    • Marked as answer by Maxi Wu Tuesday, March 6, 2012 6:11 AM
    Tuesday, March 6, 2012 2:49 AM
  • Really...

    First of all, I am aware of no way to specify a Non-Unique clustered index. If you create a CI on a table over non-unique data, it will work just fine. Just remembering that you can only have one Clustered index at a time. 

    If you WANT it unique, you have to specify it that way.

    Respectfully, perhaps you are thinking of a Primary Key, Mr. W...


    duncan davenport . data engineer and architect

    Tuesday, March 6, 2012 3:08 AM
  • Yes, my bad.  I should have clarified like I asked you too :-).

    With regards to them working just fine, yes they do however there are overheads associated with creating non-unique clustered indexes.


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Tuesday, March 6, 2012 3:11 AM
  • I will look into partitioning, but I haven't done that before.

    My query will always be continuous records in a time range, both date and time are concerned.

    I do not understand clustered index column, I have added an ascending index to time-stamp column, how to make it clustered? 

    Tuesday, March 6, 2012 6:09 AM
  • unfortunately, time-stamp isn't unique, but time-stamp+channelId is unique, is that when clustered index come in place?
    Tuesday, March 6, 2012 6:10 AM
  • If you don't have clustered index in that table already, then simply specify the word CLUSTERED in the INDEX command. If you already have clustered index, you have to remove it / make non-clustered before you will be able to add a new one.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 6, 2012 3:31 PM
    Moderator