Fast dynamic queryable in-memory data structure?

Hi all,

We have a web application that today when a user “press a button” creates an adhoc SQL server #temptable with say normally approx. 10 000 records, 15-20 columns, then executes around 200-300 small sql statements on the temptable to populate more or less an array of data on the web-server. Depending on what the users selects, the #temptable may contain different number of columns.

This takes time. Too much time. We are now considering to move all of this logic from the database server up to the app-server and perform these operations in memory. The question is what technology to use? The requirements are;

-Fast – from the user click the button to the final result is visible on screen

-Dynamic – the number of columns is not fixed

-Queryable (+with dynamic queries) – using SQL, linq, lambda or similar

-Running on the app-server (we want to be able to execute the “first select query” also from Oracle and other databases in the future)

We have been looking at a few different approaches such as;

-Query ADO datasets

-In-memory databases (SQLite in-memory mode)

-Lightweight database on app-server (SQL Compact, Express etc)

-Using Dapper to fast create dynamic objects in memory, and then linq

-Hash, dictionarys, binary search, Sorted Lists etc

Do you have any advice for what way to go in a scenario such ours? Pros and cons?

/Erik