针对数据库表实现具有多个可选参数的搜索功能 [英] Implementing search functionality with multiple optional parameters against database table

问题描述

I would like to check if there is a preferred design pattern for implementing search functionality with multiple optional parameters against database table where the access to the database should be only via stored procedures.

The targeted platform is .Net with SQL 2005, 2008 backend, but I think this is pretty generic problem.

For example, we have customer table and we want to provide search functionality to the UI for different parameters, like customer Type, customer State, customer Zip, etc., and all of them are optional and can be selected in any combinations. In other words, the user can search by customerType only or by customerType, customerZIp or any other possible combinations. There are several available design approaches, but all of them have some disadvantages and I would like to ask if there is a preferred design among them or if there is another approach.

1. Generate sql where clause sql statement dynamically in the business tier, based on the search request from the UI, and pass it to a stored procedure as parameter. Something like @Where = ‘where CustomerZip = 111111’ Inside the stored procedure generate dynamic sql statement and execute it with sp_executesql. Disadvantage: dynamic sql, sql injection

2. Implement a stored procedure with multiple input parameters, representing the search fields from the UI, and use the following construction for selecting the records only for the requested fields in the where statement.

WHERE

        (CustomerType = @CustomerType OR @CustomerType is null )

AND      (CustomerZip = @CustomerZip OR @CustomerZip is null )

AND   …………………………………………

Disadvantage: possible performance issue for the sql.

3.Implement separate stored procedure for each search parameter combinations. Disadvantage: The number of stored procedures will increase rapidly with the increase of the search parameters, repeated code.

解决方案

I posted this as a comment, but I realized it should probably be an answer.

It's not good to write predicates as WHERE @Param IS NULL OR Column = @Param, because the optimizer usually considers it to be non-sargable.

Try this experiment: Take your most populated table, and try to query just for the Primary Key field, which should be your clustered index:

DECLARE @PrimaryKey int
SET @PrimaryKey = 1

SELECT CoveredColumn
FROM Table
WHERE @PrimaryKey IS NULL
OR PrimaryKeyColumn = @PrimaryKey

SELECT CoveredColumn
FROM Table
WHERE PrimaryKeyColumn >= ISNULL(@PrimaryKey, 0)
AND PrimaryKeyColumn <= ISNULL(@PrimaryKey, 2147483647)

Both of these SELECT statements will produce identical results, assuming that the PK column is a non-negative int. But pull up the execution plan for this and you'll see a huge difference in cost. The first SELECT does a full index scan and typically takes up about 90% of the query cost.

When you want to have optional search conditions in SQL, and you can't use dynamic SQL, it's best for performance if you can turn it into a range query instead using ISNULL. Even if the range is huge (literally half the range of an int here), the optimizer will still figure it out when the optional parameter is used.

这篇关于针对数据库表实现具有多个可选参数的搜索功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！