Improve Performance SQL

Question

When I ran the SQL query below, the performance is really slow.

There are over 400,000 rows in the table and I have index the CheckDate field.

CheckDate type is datetime

SELECT username, COUNT(*) AS TotalUser
FROM table
       WHERE DATE(CheckDate) = CURDATE() 

How to improve it?

Answer 1

1) Create a computed column DateCheckDate as DATE(CheckDate) and make it PERSISTED:

ALTER TABLE dbo.table ADD DateCheckDate AS (DATE(CheckDate)) PERSISTED;

2) create an index on DateCheckDate

3) change your query to

SELECT username, COUNT(*) AS TotalUser FROM table WHERE DATECheckDate = CURDATE()

More info at

https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15

Answer 2

Try using a range comparison instead of direct equality. In other words, rewrite your query as

SELECT username, COUNT(*) AS TotalUser
  FROM table
  WHERE CheckDate BETWEEN CURDATE() AND ADDDATE(CURDATE(), INTERVAL 1 DAY)

or perhaps

SELECT username, COUNT(*) AS TotalUser
  FROM table
  WHERE CheckDate >= CURDATE() AND 
        CheckDate <  ADDDATE(CURDATE(), INTERVAL 1 DAY)

See if that helps.

EDIT: Or the first query could be reworked as

SELECT username, COUNT(*) AS TotalUser
  FROM table
  WHERE CheckDate BETWEEN CURDATE() AND
                          ADDDATE(ADDDATE(CURDATE(), INTERVAL 1 DAY), INTERVAL -1 SECOND)

but that starts to get rather ugly, and I'm not sure if negative numbers are allowed in INTERVAL specifiers.

Anyways, the point is that when you find yourself wanting to truncate a date to see if it matches some other date a better option is often to use a ranged comparison. More generally, try to avoid the use of functions on column values in the WHERE clause if there's any way to avoid it.

Share and enjoy.

Answer 3

The index on the CheckDate field wont be used because of the DATE function.

Every time you use any function on a column, the indexes on that column wont be used.

This is a situation where you should consider creating an computed column on the table with the expression DATE(CheckDate) and then create the index on the computed column (yes it will persist it on the DB, but I'm sure the index will then be used.)

Answer 4

You're not working with a functional index. Your WHERE clause employs a function DATE() which will not use the index. Use your CheckDate column as a direct comparator.

That does not mean it will go faster, but would be my first start.