Search, filter and group across all columns in a tall and wide dataset quickly We have certain datasets which may span 80 to 300 fields, across 300,000 to 35 million rows.  

For each dataset, we want to produce a web based analysis tool (a web grid) which would allow our users to pick the fields they want, and perform filtering on any of these fields. In addition, they should also be able to group by any number of these fields (nested groups), sort by any of these fields, and perform aggregations across some of the grouped fields (sum, count, avg, min, dates).  

Our source data is spread across several tables in SQL Server and while the above is possible by dynamically generating the SQL in code, it is slow. Even if we generate a precomputed table with all of the columns in it (example: 80 columns, 300K rows), performing analysis on this is slow; specifically grouping by multiple columns is slow, and adding another column to group by can add a few seconds to the query time.

Since we do not know ahead of time which fields would be queried on the most, we cannot put indexes on specific columns. On the other hand, (correct me) I don't believe it's best to place 80 indexes on a table.

In what way can I accomplish this? 

To be specific, we will be building an interactive grid in AngularJS which calls out to an API with the fields the user wants to query, group, filter, sort on. The API is in C# and its current backend is SQL Server, but we can look at something else if needed. It would be nice to have the query results return in less than 30 seconds.  Because it is web based, there will be concurrent users.
Even if we generate a precomputed table with all of the columns in it (example: 80 columns, 300K rows), performing analysis on this is slow [...] Since we do not know ahead of time which fields would be queried on the most, we cannot put indexes on specific columns. On the other hand, (correct me) I don't believe it's best to place 80 indexes on a table.


From a SQL Server point of view, the most obviously applicable technology solution is to create a clustered or nonclustered columnstore index (on all columns) on the 'precomputed' table.

Nonclustered columnstore indexes are available from SQL Server 2012 onward, though the table is then read-only. Given the 'precomputed' word, I assume this would be acceptable. Updatable and clustered columnstore indexes were added in SQL Server 2014. Both require Enterprise Edition.

Assuming your table data types are compatible, and enough memory is available, a columnstore solution ought to provide you with very fast results for the unpredictable select, project, and group by queries you describe.

For further details, see the excellent SQL Server Columnstore Index FAQ and Columnstore Indexes Described on MSDN.