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.
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.