Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    3

    Red face Unanswered: Ordering by date

    Hi,

    I'm streaming data monitoring histories of components into a database table with the following three columns: Hour (DateTime), Id (Int64), and Value (Int32). The Value entry is an aggregate of all values sent from component Id during, and 59 minutes and 59 seconds after, the time listed in the Hour column.

    I had rather not have to sort the queries after pulling them from the database by date. So I tried to index the DB by the Hour column. Any column will inevitably have duplicates, since the uniqueness depends on a combination of Hour and PortId. But Indexing the Hour column doesn't result in INSERTs being in order as I had expected. Instead, every entry is listed in order of insertion.

    So. . .how can I keep such a table ordered by date on the disk? I'm afraid this will become very inefficient if this isn't nipped in the bud right now.

    Thanks so much for your help!

    -Brandon
    Last edited by brandon.arnold; 06-07-07 at 13:20.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    the order of the data in a database has no meaning. use "order by" in your select statement to return the data to the client in the order that you wish. otherwise sql server will most likely return the data in the order it was retrieved from the disk and there is no real way to change this.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2007
    Posts
    3
    Thanks pal.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You really need an index on the column or columns that you want to use to order the data by when you retrieve it, but Sean makes an excellent point... There isn't any meaningful "order" of data inside a database, and trying to "enforce" an order is at best a waste of time, and more often it is outright counter productive.

    A database engine should be able to sort data any way that you want it presented in a result set. For database engines that use SQL as their query language, the ORDER BY clause will do this for you, and the specifications for the SELECT statement explicitly say that the database engine is free to return rows in any order it pleases if you do not specify the order you want in the ORDER BY clause.

    You can (and should) create indexes to make it quick and easy for the database engine to return the rows ordered however you want them... As an observation, you can create an index that will actually enforce a given order of rows on the disk, but until you understand more about how the SQL engine works enforcing an order is more likely to hurt performance than it is likely to help it!

    -PatP

  5. #5
    Join Date
    Jun 2007
    Posts
    3
    Pat,

    I mentioned that an index was enacted on the Hour column with this--"enforced ordering" on the disk--in mind, and after testing, I recognized that they weren't ordered correctly on SELECT statements without an ORDER BY clause. In fact, the order of a generic SELECT statement has little correlation with the order its entries were inserted. If I understand you correctly, however, the index will have improved the efficiency of a post-SELECT ordering by the indexed column, and that is useful to know.

    However, and I didn't make it that clear, this is a table that keeps streaming history. There could be tens or hundreds of Ids with hourly Values. The end result of a SELECT statement will always be a single Id's Hour-ordered Value history, to be used as in discrete analysis or printing visual charts. It seemed useful in this if there were a way to efficiently insert them in order -- and I have solutions for making positive that they're always inserted in order -- that the order on the disk could be maintained efficiently by SQL Server and would likely solve a bottleneck or two in the future.

    I've had a bit of trouble finding any text on database stream handling, so it is new to me. Despite the drawbacks in most cases with this pre-ordering, I'm pretty sure it or something different is likely going to be necessary.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •