1

In the application I am working on, we have data grids that have the capability to display custom views of the data. As a point of reference, we modeled this feature using the concept of views as they exist in SharePoint.

The custom views should have the following capabilities:

  • Be able to define which subset of columns (of those that are available) should be displayed in the view.
  • Be able to define one or more filters for retrieving data. These filters are not constrained to use only the columns that are in the result set but must use one of the available columns. Standard logical conditions and operators apply to these filters. For example, ColumnA Equals Value1 or ColumnB >= Value2.
  • Be able to define a set of columns that the data will be sorted by. This set of columns can be one or more columns from the set of columns that will be returned in the result set.
  • Be able to define a set of columns that the data will be grouped by. This set of columns can be one or more columns from the set of columns that will be returned in the result set.

I have application code that will dynamically generate the necessary SQL to retrieve the appropriate set of data. However, it appears to perform poorly. When I run across a poorly performing query, my first thought is to determine where indexes might help. The problem here is that I won't necessarily know which indexes need to be created as the underlying query could retrieve data in many different ways.

Essentially, the SQL that is currently being used does the following:

  • Creates a temporary table variable to hold the filtered data. This table contains a column for each column that should be returned in the result set.
  • Inserts data that matches the filter into the table variable.
  • Queries the table variable to determine the total number of rows of data.
  • If requested, determines the grouping values of the data in the table variable using the specified grouping columns.
  • Returns the requested page of the requested page size of data from the table variable, sorted by any specified sort columns.

My question is what are some ways that I may improve this process? For example, one idea I had was to have my table variable only contain the columns of data that are used to group and sort and then join in the source table at the end to get the rest of the displayed data. I am not sure if this would make any difference which is the reason for this post.

I need to support versions 2014, 2016 and 2017 of SQL Server in addition to SQL Azure. Essentially, I will not be able to use a specific feature of an edition of SQL Server unless that feature is available in all of the aforementioned platforms.

Jason Richmeier
  • 1,595
  • 3
  • 19
  • 38
  • ever considered using LINQ for operations such as this? It is a layer designed to generate SQL from an object model that you build. You can apply all of the conditions that you need from the user up front, then execute to retrieve the results you want. check out https://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library or check out many options in this question https://stackoverflow.com/questions/9505189/dynamically-generate-linq-queries – Jeremy Jan 09 '18 at 20:57
  • I would think about generating regular SQL queries including all filtering, grouping, and paging. Then introduce a separate process (perhaps manual at first) to identify the expensive queries and introduce supporting indexes. At least on SQL 2014 you'll need different indexes per-database. On SQL 2016+ you could use a Nonclustered Columnstore Index which might be good enough for all the query options.. And on Azure SQL you can have automatic index management. – David Browne - Microsoft Jan 09 '18 at 21:14
  • @DavidBrowne-Microsoft - The part that I find most alarming is that, currently, we are only using SQL Azure and I believe we do have automatic index management enabled. Either that, or it at least informs us when it thinks we would benefit from adding an index. So far, nothing has been reported with regard to this feature from this mechanism. – Jason Richmeier Jan 11 '18 at 21:20
  • Well, might just use a Non Clustered Columnstore Index on SQL 2016+, and just let it be more expensive on 2014. – David Browne - Microsoft Jan 11 '18 at 21:30

1 Answers1

0

(This is not really an "answer" - I just can't add comments yet because my reputation score isn't high enough yet.)

I think your general approach is fine - essentially you are making a GUI generator for SQL. However a few things:

  1. This type of feature is best suited for a warehouse or read only replica database. Do not build this on a live production transactional database. There are permutations that you haven't thought of that your users will find that will kill your database (it's also true from a warehouse standpoint, but they usually don't have response time expectations as a transactional database)
  2. The method you described for doing paging is not efficient from a database standpoint. You are essentially querying, filtering, grouping, and sorting the same exact dataset multiple times just to cherry pick a few rows each time. If you have the data cached, that might be ok, but you shouldn't make that assumption. If you have the know how, figure out how to snapshot the entire final data set with an extra column to keep the data physically sorted in the order the user requested. That way you can quickly query the results for your paging.
  3. If you have a Repository/DAL layer, design your solution so that in the future certain combinations of tables/columns can utilize hardcoded queries/stored procedures. There will inevitably be certain queries that pop up that cause you performance issues and you may have to build a custom solution for specific queries in order to get the desired performance that can't be obtained by your dynamic sql
ColdSolstice
  • 435
  • 2
  • 9