Recently SCS was approached by a client and presented with an interesting problem: frequently, their data management application – a critical piece of their business model – would grind to a halt. Searches would take many minutes to complete, if they completed at all. Important reports which normally took less than a minute to run could end up taking half an hour. The problem? The database server was often running at 100% CPU utilization.
A little bit of time investigating the code base and running diagnostics revealed the cause: searching was being driven by a 3,000 line, dynamic SQL generating stored procedure. The search supported over sixty different parameters, included some important reporting logic, and changing the parameters or logic was not an option. Aside from crippling the server, everything about the search was fine. A highly customizable search with many possible criteria is an excellent use case for dynamic SQL. So why was the performance so terrible? The implementation neglected some key considerations, but a few straightforward structural changes resulted in sizeable performance gains.
First and foremost, the search was not making good use of parameters. The dynamically generated SQL had criteria values directly inserted in the statement, rather than using parameters. For example, say the search requested customer data for a particular customer, specified by ID. It would generate statements of the form:
SELECT Name,Address,Phone FROM Customer WHERE CustomerID = 1000
Rather than:
SELECT Name,Address,Phone FROM Customer WHERE CustomerID = @CustomerID
This negatively impacts the query in a number of ways. Writing the criteria values directly in to the SQL statement causes every search to be treated as separate query. So, a search for customer #1000 and one for customer #1001 would not be able to share cached query execution plans. SQL server does have an automatic parameterization feature to handle simple cases such as this example, but the actual search was far too complex for it to be useful. Not being able to share execution plans means that searches will end up being compiled every time, unless the exact same search ran recently enough to have a plan in the cache. Recompiling a complex search on every execution in a high traffic environment put a significant and unnecessary load on the server’s CPU. Considering also that directly inserting values in the query string opens the door to SQL injection attacks, using parameters is clearly the better design.
The second issue with the search procedure was that it did entirely too much processing. This was caused by the fact that the search had evolved over many years, with many different developers working on it. Most of the parameters were supplied as concatenated within a single string parameter, which the procedure then split, parsed, and assigned to local variables. Results were gathered and filtered in stages, requiring the use of several temporary tables, which did not have indexes and were not dropped at the end of the procedure. User defined functions were fairly often employed in WHERE clauses. Refactoring the search procedure to make each parameter an actual parameter, eliminating the need for a large – and therefore, costly – amount of up front string manipulation. The load put on the server’s tempdb was eased by condensing the result selection down to a single dynamic query using one properly indexed temporary table, and dropping that table at the end of the procedure. Removing, in-lining, or moving calls to UDFs enabled the query optimizer to get a better picture of what was happening, and eliminated expensive per-row function executions.
These relatively simple architectural changes had a dramatic effect on the query’s performance. Controlled testing showed that, at a minimum, the new version was ten times faster. The refactor also allowed for elimination of some costly corner cases, which were being frequently hit. This resulted in a speed improvement of over one hundred fold in these instances. Not only did these changes prevent the server from slowing to a standstill, but they made it much less difficult to update the SQL in the future. The end result: a huge boost in performance, easy maintenance, and an ecstatic client.
Superior Consulting Services has been offering solutions like this to our clients for over 15 years. If you or someone you know is in need of assistance, our team is here to guide you. Please visit our website at https://www.teamscs.com/ and discover how we can help you!
