Dynamic SQL for custom pagination, filtering and sorting ... (without using string concatenation !) ...

Over the past few weeks, I spent time in optimizing existing applications and improving the performance. "Performance" is a big word and one can realize how long a "second" is while tuning an application for performance. I had optimized .NET code as well as SQL queries.

Developing web applications for performance and writing apps/solutions that can survive security require a lot of thinking and experience.

One of the interesting requirements that I spent time on very recently is related to SQL and the requirement is as follows:

Consider an application that presents the data in a grid (a table) where a user can sort the data, apply filters and page through the data pages. Here is a screenshot that better explains the concept visually:

Assuming that the stored procedure returns 1000 rows and only 10 records are shown per view in the grid. This means that there are about 100 pages and 10 records per page.

Now, there are a number of ways to bind data to a grid and here a few options:

Option # 1:
Get all 1000 rows from database server to the application server.

Perform sorting, filtering on the in-memory resultset and pick the 10 records that relevant to the current page in the grid.

The downside of this approach is that it caches 1000 records per request although the view requires just 10 records !

Option # 2:
Apply filtering, sorting at the database and return just 10 records that are relevant to the current view in the grid.

The advantages of this option are - less data transfer between db and app server & low memory footprint on the app server (as it holds just 10 records as opposed to 1000 records)

From the above justification, it is obvious that option # 2 is efficient w.r.t performance.

With this in mind, you start googling for a solution and you will find a solution that operate as follows:

Concatenate : SELECT Query + @SortColumn + @SortOrder + WHERE RecordID BETWEEN + @StartIndex + AND + @EndIndex
where @SortOrder, @SortColumn, @StartIndex and @EndIndex are variables/parameters to your stored proc.

The above approach works and is simple to code too but it is prone to "SQL Injection attack" !!

So, the problem I tried to solve is to avoid SQL String concatenation to build an SQL query and yet achieve the required dynamism :)

I am not going to explain the logic behind the SQL query as the script is self-explanatory. I have also developed a simple windows application in .NET that lets you generate most of the SQL query with placeholders that can be changed to suit your requirement. All that you have to do is define the columns for which the filtering and sorting logic is going to be dynamic.

Try the tool once and get back to me if you run into issues.