Query performance with date range

Asked

Viewed 210 times

2

Assuming 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 way this can greatly affect the speed of the query if the table has many items?

I am in doubt because I do not know if sql first sees what range of dates the table has or if it goes through according to the limits I established.

sql sql-server

  • A small table like this the BD probably "eats with flour" but if the table is large (millions of records) then the figure changes. In the second case maybe it was the case of not even having Where condition but it can be a case of a parameterized Usta and then worth having a single SELECT.

2 answers

2


Case 1: CAMPO_DATA has no index

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

Case 2: CAMPO_DATA has index

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

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

If it is not on top of index, times again will be the same.

0

If all BD data is between these dates, either one way or another, now if that’s the case, I don’t see the need for this Where

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.