优化多列LIKE SQL查询的最佳实践有哪些？

I have a search query that I'm inheriting and attempting to optimize. I am curious to hear if anyone has any best practices and recommendations for such. The production server is still SQL Server 2000 also.

我有一个我正在继承并尝试优化的搜索查询。我很想知道是否有人有这方面的最佳做法和建议。生产服务器仍然是SQL Server 2000。

The query is an advanced customer search stored procedure that accepts 5 different search criteria parameters (i.e. first name, last name, address, phone, etc.) to search a multi-million record table. There are indexes on all joined columns and columns in the WHERE clause. In addition, the initial query dumps the records into a table variable for paging capacity.

该查询是高级客户搜索存储过程,其接受5个不同的搜索标准参数(即,名字,姓氏,地址,电话等)以搜索数百万个记录表。 WHERE子句中的所有连接列和列都有索引。此外,初始查询将记录转储到用于分页容量的表变量中。

INSERT INTO   @tempCustTable (CustomerID, FirstName, LastName, City, StateProvince, Zip, PhoneNumber)
SELECT  DISTINCT cu.CustomerID, cu.FirstName, cu.LastName, a.City,
a.StateProvince, a.Zip, p.PhoneNumber
FROM Customer cu WITH(NOLOCK)
LEFT OUTER JOIN Address a WITH(NOLOCK) ON cu.CustomerID = a.CustomerID
LEFT OUTER JOIN Phone p WITH(NOLOCK) ON cu.CustomerID = p.CustomerID
WHERE  (cu.LastName = @LastName OR cu.LastName LIKE @LastName + '%') 
AND (@FirstName IS NULL OR cu.FirstName = @FirstName OR cu.FirstName LIKE @FirstName + '%')
AND (@StateProvince = '' OR a.StateProvince LIKE @StateProvince)
AND (@City = '' OR a.City LIKE @City + '%')
AND (@Zip = '' OR a.Zip = @Zip OR a.Zip LIKE @Zip + '%')
ORDER BY cu.LastName, cu.FirstName

Does anyone have any recommendations on how I could improve the performance of the query?

有没有人对如何提高查询性能有任何建议?

5 个解决方案

#1

isn't this whole line

不是这整行

AND (@Zip = '' OR a.Zip = @Zip OR a.Zip LIKE @Zip + '%')

the same as this

与此相同

AND (a.Zip LIKE @Zip + '%')

for sure

AND (a.Zip LIKE @Zip + '%')

it is the same as

它是一样的

a.Zip = @Zip OR a.Zip LIKE @Zip + '%'

#2

You can definitely clean up a lot of the redundancy in your code as SQLMenace pointed out as a start.

您可以在SQLMenace指出的开始时清理代码中的大量冗余。

Another thing is, ORDER BY shouldn't be used with an INSERT..SELECT. ORDER BY is meaningless in this context. People occasionally use it to force an IDENTITY column to behave a certain way, but that's a bad habit IMO.

另一件事是,ORDER BY不应该与INSERT..SELECT一起使用。在这种情况下,ORDER BY毫无意义。人们偶尔会使用它强制IDENTITY列以某种方式运行,但这是一个坏习惯IMO。

I don't know if this will help in your situation, but one thing that I came across recently was that in stored procedures SQL Server (I'm using 2005, but probably true for 2000 as well) will not short-circuit an OR condition in many cases. For example, when you use:

我不知道这对你的情况是否有帮助,但我最近遇到的一件事是在存储过程中SQL Server(我使用的是2005,但2000年可能也是如此)不会使OR短路在许多情况下的情况。例如,当您使用时:

@my_parameter IS NULL OR my_column = @my_parameter

it will still evaluate the second half even if you pass in a NULL value for @my_parameter. This happened even when I set the stored procedure to recompile (and the SELECT). The trick was to force a short-circuit through the use of a CASE statement. Using that trick (and removing some redundancy) your statement would look like this:

即使你传入@my_parameter的NULL值,它仍将评估下半部分。即使我将存储过程设置为重新编译(和SELECT),也会发生这种情况。诀窍是通过使用CASE语句强制短路。使用该技巧(并删除一些冗余),您的语句将如下所示:

INSERT INTO @tempCustTable
(
     CustomerID,
     FirstName,
     LastName,
     City,
     StateProvince,
     Zip,
     PhoneNumber
)
SELECT DISTINCT
     cu.CustomerID,
     cu.FirstName,
     cu.LastName,
     a.City,
     a.StateProvince,
     a.Zip,
     p.PhoneNumber
FROM Customer cu WITH(NOLOCK)
LEFT OUTER JOIN Address a WITH(NOLOCK) ON cu.CustomerID = a.CustomerID
LEFT OUTER JOIN Phone p WITH(NOLOCK) ON cu.CustomerID = p.CustomerID
WHERE
     (cu.LastName LIKE @LastName + '%') AND
     (1 =
          CASE
               WHEN @FirstName IS NULL THEN 1
               WHEN cu.FirstName LIKE @FirstName + '%' THEN 1
               ELSE 0
          END
     ) AND
     (1 =
          CASE
               WHEN @StateProvince = '' THEN 1
               WHEN a.StateProvince = @StateProvince THEN 1
               ELSE 0
          END
     ) AND
     (1 = CASE
               WHEN @City = '' THEN 1
               WHEN a.City LIKE @City + '%' THEN 1
               ELSE 0
          END
     ) AND
     (1 = CASE
               WHEN @Zip = '' THEN 1
               WHEN a.Zip LIKE @Zip + '%' THEN 1
               ELSE 0
          END
     )

It makes the query longer, and possibly a little more complex, but it may be worth it for better performance. This is particularly true if your criteria includes a subquery that could otherwise be short-circuited.

它使查询更长,并且可能更复杂,但是为了获得更好的性能可能是值得的。如果您的标准包含可能会被短路的子查询,则尤其如此。

Finally... be consistent with your parameters. For @FirstName you check for a NULL value to determine if it's used or not, but for the others you are checking for empty strings. Basic coding 101 here that you need to be careful about.

最后......与您的参数保持一致。对于@FirstName,您检查一个NULL值以确定它是否已被使用,但对于其他正在检查空字符串的其他值。这里基本编码101你需要注意。

#3

I would try to not have my sql code add the '%' but instead expect the parameter to already have it, this of course, after you have validated it in your application! Then don't include '=' comparisons, use LIKE all the time:

我会尝试不让我的sql代码添加'%',而是期望参数已经拥有它,当然,这是在你的应用程序中验证它之后!然后不要包含'='比较,一直使用LIKE:

WHERE (cu.LastName LIKE @LastName)

WHERE(cu.LastName LIKE @LastName)

instead of:

WHERE (cu.LastName = @LastName OR cu.LastName LIKE @LastName + '%')

WHERE(cu.LastName = @LastName或cu.LastName LIKE @LastName +'%')

#4

Avoid "OR"s - they in general prevent the use of indexes

避免使用“OR” - 它们通常会阻止使用索引

Never put a "%" on the left side. - same reason.

切勿在左侧放置“%”。 - 同样的道理。

#5

You could build up the query with dynamic sql. That would get rid of most of your ORs and would also mean you would only need to include in the WHERE statement lines for the parameters the user did actually enter.

您可以使用动态sql构建查询。这将消除大多数OR,也意味着您只需要在WHERE语句行中包含用户实际输入的参数。

If you do this, be sure to use sp_executesql rather than exec so you can parameterize the dynamic sql so the query plan can be cached.

如果这样做,请确保使用sp_executesql而不是exec,以便可以参数化动态sql,以便可以缓存查询计划。

#1