Shocking behavior of where clause

Question

1.00/5 (3 votes)

See more:

When i run this script , it takes 39 seconds-

SQL

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

It fetches only 9163 records

but the below one take only 3 seconds

SQL

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

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?

Posted 8-Mar-15 9:56am

Member 4228925

Updated 9-Mar-15 1:20am

Magic Wonder

v3

Add a Solution

3 solutions

Solution 1

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

Posted 8-Mar-15 9:59am

phil.o

Comments

Member 4228925 8-Mar-15 16:02pm

There is no Index right now and cant put that as well for several reason. At this moment i want to know exactly why fetching much less record using where clause is taking much longer time.

phil.o 8-Mar-15 16:09pm

Obviously because filtering is an expensive process, especially if there is no index on the column used for filtering.
Selecting without filtering is just reading data, and it's very fast.

phil.o 8-Mar-15 16:33pm

Oh, and thanks for the 1-vote by the way. I'll stop answering you from now.

Santosh K. Tripathi 8-Mar-15 23:57pm

5+ from me :)

phil.o 9-Mar-15 7:07am

Thanks for your support :)

Maciej Los 9-Mar-15 6:58am

Adding index to the table should help. Good suggestion. +5!

phil.o 9-Mar-15 7:07am

Thanks for your support Maciej :)

Member 4228925 21-Mar-15 11:47am

Thank you everyone..i was guessing the reason like the CAT example given above..thank you!!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Accepted Answer · 2015-03-08T23:31:00

Solution 2

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

Posted 8-Mar-15 23:31pm

Magic Wonder

Updated 8-Mar-15 23:36pm

v2

Comments

Member 4228925 21-Mar-15 11:47am

Got it..thank you

Magic Wonder 23-Mar-15 1:42am

Welcome...!!!

Maciej Los · Accepted Answer · 2015-03-09T01:07:00

Solution 3

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[^]

Posted 9-Mar-15 1:07am

Maciej Los