如何通过从where子句中删除sql函数来提高性能？ [英] How can improve performance by removing sql function from where clause?

问题描述

你好，



我读过很多关于sql查询性能提升的文章。



他们中的许多人都解释了从whare条款中删除sql函数



我几乎已经表现过......



我在查询以下运行，

Hello,

I have read many of articals about performance Improvement of sql queries.

Many of them explain the "remove sql function from whare clause"

I had perform practically.....

I have run below query,

select * from HumanResources.Employee
where JobTitle not like '%dev%'


select * from HumanResources.Employee
where CHARINDEX('dev',JobTitle,0) = 0

< br / >
并检查执行计划，



我发现每个查询需要50％的时间来执行.....



我们怎么说这会提高性能呢？



我知道我错了，请告诉我..



非常非常感谢。

and examine execution plan,

I found that each query takes 50% time to execuite.....

Than how we can say that this will improve performance?

I f I am going wrong than please tell me..

Very Very Thanks.

推荐答案

如果您在JobTitle上有一个索引，但是将它包装到一个函数中，例如作为CHARINDEX（），SQL通常不会在字段上实际使用索引。



函数导致数据不确定，这意味着SQL不能确定结果会提前是什么，所以它不能利用一些其性能增强。

If you have an index on JobTitle but you wrap that into a function, such as CHARINDEX(), SQL will generally not actually use the index on the field.

Function cause data to be non-deterministic, meaning SQL isn''t sure what the result will be ahead of time so it can''t take advantage of some of its performance enhancements.

这篇关于如何通过从where子句中删除sql函数来提高性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！