It takes time to count records the first time

Asked

Viewed 614 times

3

I’m doing a stress test on an app I’m developing. I added a few million records to a table, which is what it will have in about 5 years of use. I use Entity Framework. By counting the records of a query, It took a long time to occur. I thought it was an EF problem. However when making a trace in SQL Server and running the query directly in the database I noticed that the delay is in SQL Server itself.

Look at the following query example, similar to the one generated by the RU, but much simpler and still takes a long time:

select count(1) from tbMovimentoConta where con_codigo_fk = 1 and mcon_data >= '2017-01-01' and mcon_data <= '2018-01-01'

Result: 1,162,158 records

Time: 24 seconds

Repeating the query is instantaneous, I think because of the cache. But clearing the cache with the command DBCC DROPCLEANBUFFERS will take about 25 seconds again.

The field con_codigo_fk used in the where of the above query is a foreign key and has index.

The field mcon_data also used in where of the above query also has index.

This table has 4 more foreign key indexes, and two more indexes for the fields mcon_dataExclusao and mcon_dataInclusao which are used in research.

Script of the column index mcon_data to analyze and point out any possible flaw in it.

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

Is it normal to wait for this amount of records? It doesn’t seem so big to me. I don’t know if it’s the amount of indexes, or the way they were created. I’m investigating.

Computer Configuration: 8GB RAM and Core I7

database sql-server índices sql-server-2014

2 answers

3


  • I didn’t understand "Without a proper record is normal".

  • Didn’t you ask if it’s normal to be slow? Indexes make all the difference, if you don’t have a suitable one, it’s normal to be slow.

  • So. Indexes do, but apparently they’re not helping. I’m studying the other answers to see if it gives a light. It’s a complex subject. This table has a PK that is auto-increment. I will try to add the other fields in the key then. At these times there is a DBA specialization missing.

  • Your question does not show. If they are not helping they are inadequate, as I said.

1

As @Maniero said, there was something wrong with the current indexes. Searching on, and resorting to help on another website network, I reached the solution.

The solution was to create a composite index for the columns mcon_data and con_codigo_fk

CREATE INDEX ix__con_codigo_fk__mcon_data
ON dbo.tbMovimentoConta
(con_codigo_fk, mcon_data) ;

After the creation of this index, the query is so fast, that it does not take a second (my Management Studio does not show milliseconds).

  • I mean, you did exactly what I said :)

  • It is. Edita, put the creation code that I accept yours with greater pleasure, because you spoke of using the key, I think it confused me, I was thinking of key(PK).

  • Even removed my own.

  • I don’t even think it’s a case to remove. Indexes are created through keys. What you set up in it are keys.

Browser other questions tagged database sql-server índices sql-server-2014

You are not signed in. Login or sign up in order to post.