title: Dynamic query optimization
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Any suggestions?</p>
  
  <p>Thank you in advance!</p>