CODEWITHSUNDEEP
sql-serverdatetimeoptimizationindexingvarchar
LiuYan 刘研
LiuYan 刘研

Reputation: 1624

Indexing on DateTime and VARCHAR fields in SQL Server 2000, which one is more effectient?

We have a CallLog table in Microsoft SQL Server 2000. The table contains CallEndTime field whose type is DATETIME, and it's an index column.

We usually delete free-charge calls and generate monthly fee statistics report and call detail record report, all the SQLs use CallEndTime as query condition in WHERE clause. Due to a lot of records exist in CallLog table, the queries are slow, so we want to optimize it starting from indexing.

Question

Will it more effictient if query upon an extra indexed VARCHAR column CallEndDate ? Such as

-- DATETIME based query
SELECT COUNT(*) FROM CallLog WHERE CallEndTime BETWEEN '2011-06-01 00:00:00' AND '2011-06-30 23:59:59'

-- VARCHAR based queries
SELECT COUNT(*) FROM CallLog WHERE CallEndDate BETWEEN '2011-06-01' AND '2011-06-30'
SELECT COUNT(*) FROM CallLog WHERE CallEndDate LIKE '2011-06%'
SELECT COUNT(*) FROM CallLog WHERE CallEndMonth = '2011-06'

Upvotes: 0

Views: 869

Answers (2)

Maziar Taheri
Maziar Taheri

Reputation: 2338

  1. I think comparing DateTime is much faster than LIKE operator.
  2. I agree with DoctorMick on Spliting your DateTime as persisted columns Year, Month, Day
  3. for your query which selects COUNT(*), check if in the execution plan there is a Table LookUp node. if so, this might be because your CallEndTime column is nullable. because you said that you have a [nonclustered] index on CallEndTime column. if you make your column NOT NULL and rebuild that index, counting it would be a INDEX SCAN which is not so slow.and I think you will get much faster results.

Upvotes: 1

DoctorMick
DoctorMick

Reputation: 6793

It has to be the datetime. Dates are essentially stored as a number in the database so it is relatively quick to see if the value is between two numbers.

If I were you, I'd consider splitting the data over multiple tables (by month, year of whatever) and creating a view to combine the data from all those tables. That way, any functionality which needs to entire data set can use the view and anything which only needs a months worth of data can access the specific table which will be a lot quicker as it will contain much less data.

Upvotes: 1

Related Questions