where子句令人震惊的行为 [英] Shocking behavior of where clause

问题描述

当我运行此脚本时，需要39秒 -

When i run this script , it takes 39 seconds-

select distinct PolNum 'Policy/Quote' from table1(nolock) where ndate between DATEADD(month,-1,GETDATE())and GETDATE() 

它只获取9163条记录



但是下面只需要3秒

It fetches only 9163 records

but the below one take only 3 seconds

select distinct PolNum 'Policy/Quote' from table1(nolock)

它获取385416条记录



据我所知，where子句是matc用日期条件记录所有记录并在那里花些时间。但我对答案不满意。



有谁可以帮我理解原因？

it fetches 385416 records

I understand, the where clause is matching all the records with date condition and taking some time there. But i am not satisfied with the answer.

Can anyone, please help me to understand the reason?

推荐答案

一点都不令人震惊。



我举个例子。有100只猫。如果你必须找到猫的总数，那么很明显它将是一个快速反应。如果你不得不找到白猫，那么你需要时间才能找到它。



以类似的方式，通过放置where子句你是要求SQL获取所选记录。



我希望你能得到它。





另外，完成此操作 [ ^ ]





干杯

Hi,

Its not at all shocking.

Let me give you an example. There are 100 cats. If you have to find total no of cats then obviously it will be a quick response. And if you have to find no of white cat then it will take time to you to find it out.

In the similar way, by putting a where clause you are asking SQL to fetch selected records.

I hope you got it.


Also, Go Through this[^]


Cheers

至少 - 几乎没有 - 查询执行速度慢，数据结构不良，没有索引，索引太多等等的原因很少。



我建议读这个：如何：优化SQL查询 [ ^ ]和此：查询性能 [ ^ ]

There are - at least - few reasons of slow query execution, such bad data structure, no indexes, too much indexes, etc.

I'd suggest to read this: How To: Optimize SQL Queries[^] and this: Query Performance[^]

也许你可以尝试在你的ndate列上放一个索引？它已经有吗？

Maybe you could try to put an index on your ndate column? Does it have one already?

这篇关于where子句令人震惊的行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！