Dynamic query optimization

Question

I have an assignment for a business which is basically just about extracting data from a database (Microsoft SQL Server 2008). In the process, the users will be able to choose which columns to select, choose which view to select from, and build the WHERE clause. Based on what the user chooses, the SQL query is constructed accordingly. A requirement is that the user may select ANY column from ANY view and filter by ANY column in the WHERE clause. The company does not want the solution to use data warehouse/OLAP, and wants to limit any third party software. So basically they just want a .NET Windows Forms application which dynamically constructs SQL queries based on a GUI and connects to a database.

My concern here is how to optimize the queries. I am by no means good at optimizing SQL queries yet, but my first thought was: what if the user chooses to filter by a column which has no index (in the WHERE clause)? By giving the user so much flexibility, they can potentially construct queries that are so ineffective that they will take a long time to execute.

I realize that the performance can never be good with a lot of data if they filter on columns that have no indices, but is there anything I can do to improve it? For sure I cannot just add indices to all columns.

I am not necessarily just looking for query optimization, but I am also thinking if there are any server tweaks that I can do, such as caching? Basically I am all ears and looking for any advice that can help me improve the performance.

Any suggestions?

Thank you in advance!

Answer 1

You really cannot do much except forseeing what users are likely going to do. You are in the good position to have the SQL Server optimizer do the hard work for you (imagine building this on a key-value store!).

I would create indexes on the most likely columns that will be filtered or sorted on. You should try filtering those indexes to non-null values which will reduce storage cost (assuming users will not filter for null values).

You can also try to precompute common joins and aggregations using indexed views. If you are willing to throw insane amounts of RAM at this problem and are willing to have slow writes you can index and materialize the hell out of this database.

Finally, you can offload users queries on a read-only log-shipping target or the like. This will sandbox their horrible queries.

For your queries, you need to dparameterize them, but you do not need to cache them in all cases. If your queries tend to have a big cost (so compilation times are inconsequential) you will want to run them WITH OPTION RECOMPILE so SQL Server can adapt to the exact runtime values of all parameters.

You should also monitor all queries and review them to look for patterns. Your users are likely to run very similar queries all the time. Index for them.

Run sp_updatestats regularly.

Finally, I want to say that there is no very effective solution to this because if there were SQL Server would implement them itself so everyone could benefit.