CODEWITHSUNDEEP
sqlsql-serverperformance
abidkhan303
abidkhan303

Reputation: 1791

SQL query is slow comparing with time stamp

Guys i have very strange behavior. I have one table with few fields and one is time stamp. When i run 

select top 10 * from mytable

it works fine but when i execute 

select top 10 * from mytable where TIMESTAMP > '2013-07-1'

it becomes veryyyyyyyyyy slow.

Thanks in advance..

Upvotes: 0

Views: 1606

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Your queries are selecting the first ten rows encountered that meet the conditions of the query. The first version can take the first ten rows. This should be pretty fast.

When you add the where clause, the query has to search through all the rows until it finds the ones that match the where clause. In a large table, where few rows meet the condition, this could requires reading lots and lots of data. Taking lots and lots of time.

The solution is an index. If you add an index on TIMESTAMP then the query will run faster:

create index mytable_timestamp on mytable(timestamp);

The query engine can use the index to quickly find the rows that meet the where clause condition.

By the way, it is good to use "YYYY-MM-DD" formats for date constants, especially because these conform to an ISO standard for dates (and so is supported by most databases). The proper form would be '2013-07-01'.

Upvotes: 5

Related Questions