SQL 性能方面，哪个更好:IF...ELSE 子句还是 WHERE LIKE 子句? [英] SQL Performance-wise, what's better: an IF...ELSE clause, or WHERE LIKE clause?

问题描述

我有一个存储过程，它有一个可选参数，@UserID VARCHAR(50).问题是，有两种方法可以使用它:

I have a stored procedure that has a optional parameter, @UserID VARCHAR(50). The thing is, there are two ways to work with it:

1. 给它一个 NULL 的默认值，有一个 IF...ELSE 子句，它执行两个不同的 SELECT 查询，一个使用 'WHERE UserID = @UserID' 而没有 where.
2. 给它一个默认值 '%' 然后让 where 子句使用 'WHERE UserID LIKE @UserID'.在调用代码中，不会使用%"，因此只会找到完全匹配的内容.

1. Give it a default value of NULL, the have an IF...ELSE clause, that performs two different SELECT queries, one with 'WHERE UserID = @UserID' and without the where.
2. Give it a default value of '%' and then just have the where clause use 'WHERE UserID LIKE @UserID'. In the calling code, the '%' wont be used, so only exact matches will be found.

问题是:哪个选项更快?随着表的增长，哪个选项提供更好的性能?请注意，UserID 列是外键，未编入索引.

The question is: Which option is faster? Which option provides better performance as the table grows? Be aware that the UserID column is a foreign key and is not indexed.

我想根据一些答案添加一些内容:@UserID 参数不是(必然)唯一的 可选 参数正在通过.在某些情况下，有多达 4 或 5 个可选参数.

Something I want to add, based on some answers: The @UserID parameter is not (necessarily) the only optional parameter being passed. In some cases there are as many as 4 or 5 optional parameters.

推荐答案

我通常做的事情是这样的

What I typically do is something like

WHERE ( @UserID IS NULL OR UserID = @UserID )

为什么它没有被索引?索引 FK 通常是一种很好的形式，因为您经常加入它们......

And why isn't it indexed? It's generally good form to index FKs, since you often join on them...

如果您担心查询计划存储，只需执行以下操作:创建过程...重新编译

If you're worried about query plan storage, simply do: CREATE PROCEDURE ... WITH RECOMPILE

这篇关于SQL 性能方面，哪个更好:IF...ELSE 子句还是 WHERE LIKE 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！