Whats the best way to store a large number (several millions) of records used to create reports on? The nature of the application demands that each record that matches a search is sent to the application for processing so both the query-execution speed and the query-result transfer speed is big factors for us.
Currently our application stores records in one straight MSSQL table heavily indexed for query performance. Does anybody have any alternative storage ideas or is an relational database a good fit for this even though we can only store records in one table since the data is not relational in it self?
The SQL solution gives us pretty good performance but I am intrested if there are other better backend alternatives, for example is NoSQL databases a valid solution to start looking in to?
Our queries are made against a small number of columns but the results can vary in size (number of row needed for each execution depending on period and some other parameters).
Thanks in advance for helping me to get some new perspectives on this.
Since we are a .NET shop any solutions/ideas that fits good with .NET and Windows servers are a big plus for us but I am appreciating all input I can get on this. And by solutions I mean some other backend than MSSQL or other relational-dbs?
The query efficiency is based on query and the indexes
For transferring the data to the client:
- Just a straight up DataReader is very efficient
- Drapper is also fast but I have not used it
I had a valid interpretation that the query results should be saved to be re-run
The query is only run once
Data int ID iden varchar Value1 varchar Value2 SavedQuery int ID iden varchar name SavedQueryResults int QueryID PK int DataID PK Select [Data].[Value1], [Data].[Value2] From [Data] Join [SavedQueryResults] on [SavedQueryResults].[DataID] = [Data].[ID] and [SavedQueryResults].[QueryID] = x
With the PK on SavedQueryResults this should result in an index seek and cannot do better than that.
When you create the SavedQueryResults use order by DataID in the insert to keep fragmentation down