CODEWITHSUNDEEP
.netsql-serverdatabase
jmw
jmw

Reputation: 2864

Best way to store large number of datarow for querying

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?

Upvotes: 0

Views: 121

Answers (2)

MeTitus
MeTitus

Reputation: 3428

Why won't you rather have a couple of report tables, updated with triggers, it would be a lot more efficient. The same as view models in the CQRS world.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

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

Upvotes: 1

Related Questions