Results 1 to 4 of 4

Thread: Order by for timeSeries data (MS SQL)

  1. #1
    Join Date
    Dec 2012
    Location
    Virginia, US
    Posts
    2

    Order by for timeSeries data (MS SQL)

    Does it make sense to order time series data by time in a mssql table? I guess can it be done?

    I noticed that even if I do order by date and time, then select rows from a piece of time the database looks at the entire table even if the records requested
    are at the beginning of the time series.

    The table I have was created from an ordered time series .txt flat file, however sql doesn't save it in order, and when you search for something, the entire table is searched.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Does table have clustered index? Does data in text file have same order as clustered index column?

  3. #3
    Join Date
    Dec 2012
    Location
    Virginia, US
    Posts
    2
    No its not indexed, the size of the data grow on me recently and I am now trying to make the queries more efficient.

    I am looking at several possibilities the first being partitioning the tables. I am also looking at multi-dimensional data solutions. I anticipate my data will grow to 100GB+ in the next few months and I need better ways of managing my queries. The data consists of a couple hundred very long tables of exactly the same data fields. These tables can be cut to monthly partitions. To be more clear the data is temperature reading from different locations each table is one local. The type of queries I run compare changes in one locality to another or to several other locations. What I would like to have is something similar to putting the tables on top if each other aligning them exactly on top of each other by time, so when I want a particular minute or period of time from different locations I would slice across the aligned layers of data as if you have a knife that can cut through several pages of a book with pages that are aligned. The cut would result in the data from the different localities for exactly the same time period.

    I will start with creating indices, test out performance gains, and then move on to partitioning and multi-dimensional data. I just started reading about partitioning and multi-dimensional data. For now I am wondering what is the difference? as you partition a table you would be creating multi-dimensions, so it seems like they are two names for the same thing.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    They are two different things, partitioning is splitting table based ob partition key while multi-dimensional is multiple tables for olap processing usually.

Posting Permissions

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