How optimise dynamic search store procedure.

I want to share the approach that I lead, with the application developers, in optimising a feature within our application.

This feature was the search functionality, which almost every application has and gives SQL Server database a bad reputation, that finds data by selected attribute(s) and limits the rows output based upon the application rows and page selectors.

The problems were not the usual lousy T-SQL code approach to optional search predicates:

1 2	where (table.column1 = @pColum1PredicateValue Or @pColum1PredicateValue is null) AND (table.column2 = @pColum2PredicateValue Or @pColum2PredicateValue is null)

The main problem was with the implementation of the store procedure to build the dynamic T-SQL string. The string was initialised with a select statement, containing all fields required for the application output, and extra table joins to cover all possible predicates. The where clause only had the predicates that were select by the user.

Another issue was the way the total number of rows calculated. It would run the same dynamic T-SQL string to get a row count. This dynamic T-SQL string had some poorly implementation select expression.

The final issue we identified was the method the store procedure took to meet the application need to display reference data, which are rows in reference tables, in a comma-separated list. The store procedure would create a temporary table for all reference keys and the comma-separated list ignoring the predicates and page rows selectors.

My approach that led was to re-write the store procedure in the following ways:

• Changing the way dynamic T-SQL string was being generated and used.
• Use a different method to send the require the data to the application.

Changing the way dynamic T-SQL string was being generated and used.

I got the developers to break up dynamic T-SQL string into three parts: Select clause; from clause and where clause and join at the end when need to run them. The select clause would contain only fields need for how the dynamic T-SQL string was going to use for: either count (PrimaryKey) or select of columns required. The from clause would only contain the table joins required for the select clause and required predicates, as for the where clause continued as before.

We then used dynamic strings if different ways:

• The first usage was to get a total row count from the query. So that application could work out the total number of pages need to display all records from the query.
• The second usage was to populate a temporary table with the primary keys of the root application table, that met the requirement of query where clause and based on the order were within page and number of row selectors.

Change the method the require data was and sent to the application.

To get the data sent to the application we wrote a static SQL statement that outputted the columns needed for the application with required tables joins and filter the rows by the use the temporary table that dynamic strings populated. We also use the temporary table to limit the creation of reference data in a comma-separated list.

Conclusion

The reason for sharing this post is because my application developers were very sceptical on taking this approach. I demo my approach with by implementing a few of the parameters include the defaults and show the running times against old.

With the defaults and few of the required predicates selected the new approach outperformed the old method but 80% when all the predicates were selected then performance was less because the dynamic string was looking that same.  We also had the same pattern of dynamic T-SQL use in other parts of the application which convert use this new approach.

When these store procedure went into production, our customers were noticing the improvement and were sending feedback that applications were running much quicker.