Governor Technology Blog
27 May 2010 by John Mannix
Something that crops up quite often when developing database
driven applications is the need to search a complex database, often
joining multiple tables in the process, using parameters supllied
by the user from a form where some of the search parameters are
optional.
In this case it is pretty common for the db developer to write a
where clause like this:
...
WHERE (@CompanyName IS NULL OR CompanyName LIKE ('%' + @CompanyName + '%'))
AND (@PostCode IS NULL OR PostCode=@PostCode)
AND (@Reference IS NULL OR Reference=@Reference)
...
The expectation is that SQL Server will optimise the query plan
for this so that the redundant elements of the where clause are
optimised out. However, this is not the case. In fact the query
plan for the stored procedure is compiled and cached when the
stored procedure is created, so SQL Server does not know what value
each parameter will have and cannot optimise the query.
If one of the elements of your where clause is someting like a
partial text match, which requires a table scan, then even if you
don't supply that parameter it will adversely affect performance.
In practice, with a reasonably sized database, this can mean a
query that should return results based on an index almost instantly
taking several seconds or longer.
One effective solution to this is to change your search stored
proc so that it constructs the query using dynamic SQL, only
joining tables and adding elements to the where clause as required
for the parameters that are supplied to the stored procedure. This
will look something like the following:
DECLARE @sql VARCHAR(4000)
SET @sql = "SELECT * FROM dbo.MainTable "
IF (@CompanyName IS NOT NULL) SET @sql=@sql + "JOIN dbo.Companies ON dbo.MainTable.CompanyID=dbo.Companies.CompanyID "
SET @sql = @sql + "WHERE 1=1 "
IF (@CompanyName IS NOT NULL) SET @sql=@sql + "AND dbo.Companies.CompanyName=@CompanyName "
IF (@PostCode IS NOT NULL) SET @sql=@sql + "AND dbo.MainTable.PostCode=@PostCode "
IF (@Reference IS NOT NULL) SET @sql=@sql + "AND dbo.MainTable.Reference=@Reference "
Using WHERE 1=1 allows you to append any combination of elements
to the where clause without worrying about whether to prepend an
AND or not.
Having constructed the minimum query necessary to return the
results you need for the given input parameters, you then run that
SQL as a parameterised query using sp_executesql like this:
SET @paramlist = '@CompanyName VARCHAR(255),
@PostCode VARCHAR(10),
@Reference VARCHAR(50)'
EXEC sp_executesql @sql, @paramlist, @CompanyName, @PostCode, @Reference
If you construct and execute your query in this way, SQL Server
will create an optimised query plan for each SQL statement you
construct and will cache the query plan for future reuse.
Because subsequent calls to the stored procedure will produce
the same SQL statement for a given combination of input parameters,
subsequent searches will find and use the pre-cached query
plan.
Supplying a parameter list to sp_executesql ensures that the SQL
statement executed is always the same for a given combination of
input parameters, even if the input values are different.
It is important to use fully qualified (dotted) table and field
names throughout your query to ensure that SQL Server will match
cached query plans accurately.
If you follow this advice then you should see your query times
for advanced search forms reduce dramatically!
Leave comment:
