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