Performance - User defined query / filter to search data

Question

What is the best way to design a system where users can create their own criterias to search data ? By "design" i mean, data storage, data access layer and search structure.

We will actually refactor an existing application which is written in C# and ASP .NET and we don't want to change the infrastructure. Our main issue is performance and we use MSSQL and DevExpress to build queries. Some queries run in 4-5 minutes and all the columns included in the queries have indexes.

When i check queries, i see that DevExpress builds too many "exists" clauses and i'm not happy with that because i have doubts that some of these queries skip some indexes.

What may be the alternatives to DevExpress? NHibernate or Entity Framework? Can we build dynamic criteria system and store these to database in both of them ?

And also do we need any alternative storage like a lucene index or OLAP database?

Answer 1

This question has been asked before, including here.

The problem is that if your queries take 4-5 minutes now, imagine how much quicker they will run when users are creating the queries themselves (/sarcasm). I suggest you put together some pre-defined, well tuned, queries and allow users to add parameters or filters, but don't let them roll their own queries. You will regret it, not just for performance reasons, but because they won't understand how to correctly limit their queries, and they will get the wrong answers.