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
isbigint
and has a foreign key index (non-clustered) - The column
mcon_data
isdatetime
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?
Best Answer
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:
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:
@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).