0

I have some devices which will log data to table each seconds.Each device will have 16 records per second, As the number of devices grow large the table will have billions of records, Now I’m using sql server, some times a simple record count query itself takes seconds to execute.

There are situation where we need historical data mostly as average of data in hour so we were processing large data each hour and converting it into hourly data so there will be only 16 records for a device in an hour but now there is a requirement to get all records between some time ranges and process it so we need to access big data.

currently I use sql server, Can you please suggest some alternative methods or how to deal with big data in sql server or some other db.

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Shijith MC
  • 129
  • 3
  • 13
  • You may need to consider ways to improve query rather than trying to switch DB..look out this answer for more details:http://stackoverflow.com/questions/2794736/best-data-store-for-billions-of-rows/2794983#2794983 – TheGameiswar Jan 24 '17 at 14:19
  • Thanks for editing and link, Is there any particular version of Sql server which is specially build for big data. – Shijith MC Jan 24 '17 at 14:29
  • 1
    Woof! Complex topic. What's your current limiting factor? Reads per second? Disc space? Read time? Down time? – Xedni Jan 24 '17 at 14:44
  • Read Time is the first concern, Next disk space. – Shijith MC Jan 24 '17 at 14:47
  • 2
    You might look at table partitioning. I did something similar. Partitioning splits the table over multiple "logical" tables and/or drives. The advantage is you have a "current" partition where most of your IO happens, which you can keep nice and responsive. Then you keep a historical partition where you can run slow rollup queries to your hearts desire. It's a tricky subject, but if done right can be immensly helpful. This is a good place to start https://www.brentozar.com/sql/table-partitioning-resources/ – Xedni Jan 24 '17 at 14:51

1 Answers1

0

I don't think that's too much for SQL Server. For starters, please see the links below.

https://msdn.microsoft.com/en-us/library/ff647793.aspx?f=255&MSPPError=-2147217396

http://sqlmag.com/sql-server-2008/top-10-sql-server-performance-tuning-tips

http://www.tgdaily.com/enterprise/172441-12-tuning-tips-for-better-sql-server-performance

Make sure your queries are tuned properly and make sure the tables are indexed properly.

ASH
  • 20,759
  • 19
  • 87
  • 200