Is Filtering on the basis of dates decreases the performance of query?????

[BobbyRajputDurg]

i have one query Where When i m filtering data on the basis of 2 dates it will take 2 min to execute
but when i remove that condition it takes 1 sec, only

[India777]

No. Using Dates in the Filter isn't affect the Performance. For Example the Following query take less than one minute to run. The Performance of the query is based on index then no of Rows in that table like this.

select * from AccountEmployee 
where convert(varchar,convert(datetime,CreatedOn,101),101) >= '05/04/2011'and
convert(varchar,convert(datetime,CreatedOn,101),101) <= '05/07/2011'

[Rabbit]

The short answer is yes, obviously your filter is affecting performance.

The long answer is no, using dates compared to any other data type does not result in longer query times.

The reason you're experiencing poor performance is most likely a result of poor design.

The next obvious question is, how do I fix the design? The answer is, no one knows, you've shared nothing with us about the design. We don't know what the query looks like, we don't know what the table structure looks like, we don't know what the data looks like, and we don't know what indexes you have.

[ck9663]

Actually, it will affect your performance.

In general, using a function to a column in a WHERE clause will, most of the time, will tell the SQL Server not to use an index seek but will use an index scan. This is essentially a table scan. You will feel this in a large table. Always try not to use a function in your WHERE clause.

Check this out.

Good Luck!!!


~~ CK

[Rabbit]

@ck, The poster never said they used a function on the date. The function you're seeing is a reply from someone else.

[ck9663]

Aahh yes, I miss that. Thanks Rabbit.


~~ CK