Query about query performance with date range

Question

Query about query performance with date range

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

Assuming that I have a database table with 10000 data and each record is registered with dates between 2013 and 2014.

A query like this:

SELECT CAMPO_DATA FROM TABELA WHERE CAMPO_DATA BETWEEN '2013-01-01' AND '2014-12-31'

will be faster than:

SELECT CAMPO_DATA FROM TABELA WHERE CAMPO_DATA BETWEEN '2000-01-01' AND '2100-12-31'

?

If I use the second form this can greatly affect the speed of the query if the table has many items?

I'm in doubt as to whether sql first sees the range of dates that the table has or whether it traverses according to the limits I've set.

sql sql-server

asked by anonymous 28.07.2014 / 23:00

2 answers

0

If all DB data is between those dates, either one way or another, now if that's the case, I do not see the need for this where

28.07.2014 / 23:14

Capturing events on a button Put active link with javascript?

score 2 · Accepted Answer

Case 1: CAMPO_DATA has no index

The execution plan will be on top of the entire table ( TABLE SCAN ). For both queries, the time will be the same.

Case 2: CAMPO_DATA has index

The execution plan may either be on top of the index or not. It all depends on statistics already calculated from the database.

If it is above index, the first sentence is faster, because the execution plan will be on top of a table partition mapped by the index, not the entire table.

If it is not above index, the times again will be the same.