4

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

enter image description here

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?

  • Why do you need a bigint for a FK? – paparazzo Jan 5 '17 at 18:59
  • 1
    None of your indexes cover the query. Posting the plan would be helpful. It's either using the ix_mcon_data and doing a lookup for each row to evaluate the con_codigo_fk, looking at the con_condigo_fk index and doing a lookup for the mcon_data, or doing a clustered index scan on the PK and filtering out results. Ideally you'd cover the query with an index on mcon_data and con_codigo_fk. You'd have to look at perf with that for insert/update though. – Nic Jan 5 '17 at 19:00
  • My boss decided to be bigint. He said because the range is bigger. I cannot change that. I agree with you. Everywhere is bigint. – Murilo Jan 5 '17 at 19:02
6

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).

  • For option one, should I remove the existing index on con_codigo_fk and mcon_data? – Murilo Jan 5 '17 at 19:19
  • Yes for the 1st one and no for the 2nd. – ypercubeᵀᴹ Jan 5 '17 at 19:23
  • I'm really dummy on this. In SQL Management Studio shoud I add the columns on "Index key columns" or "included columns" tabs? – Murilo Jan 5 '17 at 19:24
  • 2
    Added statement for creating index. – ypercubeᵀᴹ Jan 5 '17 at 19:28
  • 1
    It's still a bit high. What is the clustered index on the table? Can you edit the question and add the whole CREATE TABLE script (and all the indexes)? – ypercubeᵀᴹ Jan 5 '17 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.