Reducing the duration of this COUNT() query

Question

I have a table with 2,161,524 rows. I think the counting query is taking too long.

select count(mcon_codigo_pk) from tbMovimentoConta
-- count = 2,161,524
-- time = 9 seconds

select count(1) from tbMovimentoConta 
where con_codigo_fk = 1 
and mcon_data between '2015-01-05' and '2016-01-06'
-- count = 1,034,729 
-- time = 13 seconds

Details:

• The column con_codigo_fk is bigint and has a foreign key index (non-clustered)
• The column mcon_data is datetime and has index (non-clustered)
• The table has an auto-increment PK (clustered index)
• There's more three foreign that has index (all indexes was created by Entity Framework)

Index creating script

CREATE NONCLUSTERED INDEX [ix_mcon_data] ON [dbo].[tbMovimentoConta]
(
    [mcon_data] ASC
)
WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
);
GO

My computer has 8GB RAM and a Core i7 running Windows 10.

Are my queries really taking a long time or they are on the average expected time? If they are taking a long time, what can I do to make them faster?

Answer 1

There are 2 possibilities for a covering index of the query:

• a composite index on (con_codigo_fk, mcon_data). This would cover all similar queries. If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data) can be used by different queries, so I wouldn't remove it.

  Code to add the index:

  CREATE INDEX ix__con_codigo_fk__mcon_data       -- choose a name
    ON dbo.tbMovimentoConta
    (con_codigo_fk, mcon_data) ;
  

• a filtered index on (mcon_data) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).
  The use cases for such indexes are much more rare so it's probably not useful to you.

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?

---

Another issue - that the index isn't trying to address - is that while the time needed for the query with the WHERE condition might be explained by a bad plan and lack of appropriate index, the time for the 1st query, the whole table count (9 seconds) sounds a bit too much. (and the 1 second for the query after the index added is high, too).

I would be speculating without further details as the explanation might be from numerous different reasons (general/memory SQL Server settings, high load on the server, wide clustered index, etc) so I suggest you either add more details on the question or post a new question (with the CREATE TABLE script if the issue is only for queries involving this table).