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?