A co-worker recently ran into a situation where a query to look up security permissions was taking ~15 seconds to run using an = comparison on UserID (which is a UNIQUEIDENTIFIER). Needless to say, the users were less than impressed.
最近，一位同事遇到了这样一种情况：查询安全权限需要大约15秒才能使用= UserID（这是一个UNIQUEIDENTIFIER）进行比较。毋庸置疑，用户对此印象不足。
Out of frustration, my co-worker changed the = comparison to use a LIKE and the query sped up to under 1 second.
出于沮丧，我的同事改变了=比较以使用LIKE并且查询加速到1秒以下。
Without knowing anything about the data schema (I don't have access to the database or execution plans), what could potentially cause this change in performance?
如果不了解数据模式（我无权访问数据库或执行计划），可能会导致性能发生这种变化？
(Broad and vague question, I know)
（广泛而含糊的问题，我知道）
5 个解决方案
#1
8
It may have just been a poor execution plan that had been cached; Changing to the LIKE statement then just caused a new execution plan to be generated. The same speedup may have been noticed if the person had run sp_recompile on the table in question and then re-run the = query.
它可能只是一个缓存的糟糕的执行计划;更改为LIKE语句然后只是生成了一个新的执行计划。如果此人已在相关表上运行sp_recompile然后重新运行=查询，则可能已注意到相同的加速。
#2
8
The other possibility is that this is a complex query and a type conversion is taking place across the = operator for every row. LIKE changes the semantics somewhat so that the type conversion does not have to weigh as heavily in execution planning. I would suggest that your coworker take a look at the execution plan with the = in place and see if there is something like
另一种可能性是这是一个复杂的查询，并且每行都在=运算符之间进行类型转换。 LIKE稍微改变了语义，因此类型转换不必在执行计划中占据重要地位。我建议你的同事看看执行计划中的=到位，看看是否有类似的东西
CONVERT(varchar, variable) = othervariable
in the execution step. In the wrong circumstances, a single typecast can slow a query by two orders of magnitude.
在执行步骤中。在错误的情况下，单个类型转换可以使查询减慢两个数量级。
#3
1
In some cases, LIKE can be faster than an equivalent function like SUBSTRING when an index can be utilized.
在某些情况下，当可以使用索引时，LIKE可以比SUBSTRING之类的等效函数更快。
Can you give the exact SQL?
你能给出确切的SQL吗？
Sometimes functions can stop the optimizer from being able to use an index.
有时函数可以阻止优化器使用索引。
Compare the execution plans.
比较执行计划。
#4
1
Well, if he ran the two queries one after the other, then it is quite likely that the data had to read from the disk for the first query, but was still in the RDBMS data cache for the second one...
好吧，如果他一个接一个地运行这两个查询，那么很可能数据必须从磁盘读取第一个查询，但仍然在第二个查询的RDBMS数据缓存中......
If this is what happened, then if he ran them in the opposite order he would have seen the opposite results... If he used like with an exact value (no wildcards) then the query plan should have been identical..
如果发生了这种情况，那么如果他以相反的顺序运行它们，他会看到相反的结果......如果他使用了精确值（没有通配符）那么查询计划应该是相同的..
#5
0
Have you tried updating the statistics on this table/database? Might be worth a try.
您是否尝试更新此表/数据库的统计信息？也许值得尝试一下。
#1
8
It may have just been a poor execution plan that had been cached; Changing to the LIKE statement then just caused a new execution plan to be generated. The same speedup may have been noticed if the person had run sp_recompile on the table in question and then re-run the = query.
它可能只是一个缓存的糟糕的执行计划;更改为LIKE语句然后只是生成了一个新的执行计划。如果此人已在相关表上运行sp_recompile然后重新运行=查询，则可能已注意到相同的加速。
#2
8
The other possibility is that this is a complex query and a type conversion is taking place across the = operator for every row. LIKE changes the semantics somewhat so that the type conversion does not have to weigh as heavily in execution planning. I would suggest that your coworker take a look at the execution plan with the = in place and see if there is something like
另一种可能性是这是一个复杂的查询，并且每行都在=运算符之间进行类型转换。 LIKE稍微改变了语义，因此类型转换不必在执行计划中占据重要地位。我建议你的同事看看执行计划中的=到位，看看是否有类似的东西
CONVERT(varchar, variable) = othervariable
in the execution step. In the wrong circumstances, a single typecast can slow a query by two orders of magnitude.
在执行步骤中。在错误的情况下，单个类型转换可以使查询减慢两个数量级。
#3
1
In some cases, LIKE can be faster than an equivalent function like SUBSTRING when an index can be utilized.
在某些情况下，当可以使用索引时，LIKE可以比SUBSTRING之类的等效函数更快。
Can you give the exact SQL?
你能给出确切的SQL吗？
Sometimes functions can stop the optimizer from being able to use an index.
有时函数可以阻止优化器使用索引。
Compare the execution plans.
比较执行计划。
#4
1
Well, if he ran the two queries one after the other, then it is quite likely that the data had to read from the disk for the first query, but was still in the RDBMS data cache for the second one...
好吧，如果他一个接一个地运行这两个查询，那么很可能数据必须从磁盘读取第一个查询，但仍然在第二个查询的RDBMS数据缓存中......
If this is what happened, then if he ran them in the opposite order he would have seen the opposite results... If he used like with an exact value (no wildcards) then the query plan should have been identical..
如果发生了这种情况，那么如果他以相反的顺序运行它们，他会看到相反的结果......如果他使用了精确值（没有通配符）那么查询计划应该是相同的..
#5
0
Have you tried updating the statistics on this table/database? Might be worth a try.
您是否尝试更新此表/数据库的统计信息？也许值得尝试一下。