I was recently working on a performance tuning engagement for a client. They had a stored procedure that looked similar to the following sample code.
select {a bunch of columns}
from table
join table2 on table.column = table2.column
where table1.some_column = 'something'
and table1.account LIKE @account + '%'
and table1.address LIKE @address + '%'
and table1.city LIKE @city + '%'
and table1.state LIKE @state + '%'
and table1.zipcode LIKE @zipcode + '%'
This runs about as inconsistently as you would expect, given that it’s the same plan every time, no matter what values are being passed in. Getting this to perform better and consistanly requires some dynamic SQL changes that look similar to the following.
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Params NVARCHAR(MAX) = N’@account varchar(10), @address varchar(50), @city varchar(50), @state varchar(2), @zipcode varchar(5)’
SET @SQL = ‘select {a bunch of columns}
from table
join table2 on table.column = table2.column
where table1.some_column = ”something”’
IF @account = ”
SET @SQL = @SQL + ‘and table1.account LIKE @account + ”%”’
IF @account = ”
SET @SQL = @SQL + ‘ and table1.address LIKE @address + ”%”’
IF @account = ”
SET @SQL = @SQL + ‘ and table1.city LIKE @city + ”%”’
IF @account = ”
SET @SQL = @SQL + ‘ and table1.state LIKE @state + ”%”’
IF @account = ”
SET @SQL = @SQL + ‘ and table1.zipcode LIKE @zipcode + ”%”’
When this runs, it now executes the dynamic SQL and includes only the sections of the WHERE clause it needs. So now it comes to how to build the indexes for this. In order to decide what use cases I needed to focus on, I went back to the application. Fortunately, this customer has DynaTrace configured for their application. If you aren’t familiar with DynaTrace, it captures every call within the application, how long those calls take, and what parameters were passed to the application.
In this case, I was able to search within DynaTrace for the name of this stored procedure and pull up every execution of the stored procedure for a several-hour window. After reviewing each execution, I was able to find something interesting: the only parameter that was being passed in was @account. It appears that none of the other parameters were used (or if they were used, they were used VERY infrequently). This told me that it wouldn’t be worth the time to do any tuning if the other parameters had been passed in, as apparently they aren’t actually used by the business users of the application (in this case, the users were the customer service representatives).
While the dynamic SQL supports all the same parameters that could be passed in before, I didn’t tune for any of those sets since it appears (based on the actual usage of the application) that those parameters aren’t used. This allowed me to tune for what sets are being used and move on to the next stored procedure.
Denny