CODEWITHSUNDEEP
sql.netsql-servertime-series
sumeet kumar
sumeet kumar

Reputation: 2648

SQL Server Time Series Modelling Huge datacollection

I have to implement data collection for replay for electrical parameters for 100-1000's of devices with at least 20 parameters to monitor. This amounts to huge data collection as it will be based very similar to time series.I have to support resolution for 1 second. thinking about 1 year [365*24*60*60*1000]=31536000000 rows. I did my research but still have few questions

  1. As data will be huge is it good to keep data in same table or should the tables be spitted. [data structure is same] or i should rely on indexes?

  2. Data inserts also will be very frequent but i can batch them still what is the best way? Is it directly writing to same database or using a temporary database for write and sync with it?

  3. Does SQL Server has a specific schema recommendation to do time series optimization for select,update and inserts? any out of box helps for day average ? or specific common aggregate functions i can write my own but just to know as this a standard problem so they might have some best practices and samples out of box.**

please let me know any help is appreciated, thanks in advance

Upvotes: 2

Views: 464

Answers (2)

paparazzo
paparazzo

Reputation: 45096

That is a big number but I would start with one table see if it hold up. If you split it in multiple tables it is still the same amount of data.

Do you ever need to search across devices? If not you can have a separate table for each device.

I have some audit tables that are not that big but still big and have not had any problems. If the data is loaded in time order then make date the first (or only) column of the clustered index.

If the the PK is date, device then fine but if you can get two reading in the same seconds you cannot do that. If this is the PK then if you can load the data by that sort. Even if you have to stage each second and load. You just cannot afford to fragment a table that big. If you cannot load by the sort then take a fill factor of 50%.

If you cannot have a PK then just use date as clustered index but not as PK and put a non clustered index on device.

I have some tables of 3,000,000,000 and I have the luxury of loading by PK with no other indexes. There is no measurable degradation in insert from row 1 to row 3,000,000,000.

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

1) You probably want to explore the use of partitions. This will allow very effective inserts (its a meta operation if you do the partitioning correctly) and very fast (2). You may want to explore columnstore indexes because the data (once collected) will never change and you will have very large data sets. Partitioning and columnstore require a learning curve but its very doable. There are lots of code on the internet describing the use of date functions in SQL Server.

Upvotes: 2

Related Questions