Index making sql query too slow

Question

the DBMS in question is SQL Server

In my system, we have a table that stores book releases. The table has a considerable amount of records, currently (around 1.2million).

Has an index clustered over a sequential numerator, which is the primary key of the table (no business rule, just a sequential number).

Has a Codclient column. Recently an index was created on it, to use in queries filtering by it.

However, with this, the consultation was extremely slow. Something "wrong" happened.. with the data already in memory, the query took 1 or 2 seconds dropping the Dice... and with the index created again, it takes several minutes!

The consultation was slow in 2 users I tested. I took a backup of one of them, and tested in my environment, the same behavior was identified (ie: should be the content really)

Detail: in this query has "Codcliente not in (subconsulta)..." Because it has the index, it may have changed so much because of this?

Can an Indice slow down a query in this case?

The insertion in the table, it is normal, the only problem was that in the delay of the query, ended up blocking the accounting table and gave timeout in many Insert in it - many same.

Consultation:

Select distinct CT.CodCliente,ct.codacesso FROM CT_Contabilidade CT  (nolock)
Inner join CD_PlanoContas PC ON CT.CodAcesso = PC.Cod  AND CT.CodPlano = PC.CodPlano 
WHERE (CT.CodCliente NOT IN (
    SELECT codcliente FROM ct_contabilidade SI (nolock)
    WHERE SI.CodPlano = CT.CodPlano And SI.codcliente > 0  And SI.CodAcesso = CT.CodAcesso And Year(SI.Data) = 2016
    AND month(SI.data) = 4 And ( SI.CodEmpresa = 1) )
    ) 
And CT.CodPlano = 1 AND MONTH(CT.Data) < 4 AND YEAR(CT.Data) = 2016 AND PC.subgrupo = 'C' 
And ( CT.CodEmpresa = 1 )  And ct.codCliente > 0 

Below I will put the execution plan and the client statistics generated in the execution when THERE is the index: (I was going to put, but I could not put more than 2 links p/ images. It was only the customer statistics)

And in the sequence, the implementation plan and statistics generated by the execution of the query when DO NOT HAVE the index (much faster): (I was going to put, but I could not put more than 2 links p/ images. It was only the customer statistics)

Someone might give some light, what might have happened?

Answer 1

As it was not informed the whole script of creating the tables I will leave for recommendations in the index creations and the query.

Regarding the field of Data: The functions used in under the field causes the SQL Engine to ignore any index, so prefer the between

Join with the table CD_PlanoContas: The table size was not reported, but it would be the case to create 2 indices One on the table itself CD_PlanoContas with the fields (Cod, Codplano) and the other in CT_Contabilidade in fields(Codacesso, Codplano)

For the consultation itself: In the case of consultation I have 2 solutions

1 - creation of an input in the `Ct_accounting with the fields: (SI.codplano, SI.codcliente, SI.Codacesso, Data, Codempresa) include(codacesso)

2 - Remove the subquery and turn it into a temporary table. The Script would look like this:

SELECT CodPlano,codcliente into #TMP FROM ct_contabilidade SI (nolock) WHERE SI.codcliente > 0  And Year(SI.Data) = 2016 AND month(SI.data) = 4 And ( SI.CodEmpresa = 1)

 Select distinct CT.CodCliente,ct.codacesso 
    FROM CT_Contabilidade CT  (nolock)
    Inner join CD_PlanoContas PC ON CT.CodAcesso = PC.Cod  AND CT.CodPlano = PC.CodPlano 
WHERE (CT.CodCliente NOT IN (
    SELECT codcliente FROM #TMP SI (nolock)
    WHERE SI.CodPlano = CT.CodPlano  And SI.CodAcesso = CT.CodAcesso )
    ) 
And CT.CodPlano = 1 AND MONTH(CT.Data) < 4 AND YEAR(CT.Data) = 2016 AND PC.subgrupo = 'C' 
And ( CT.CodEmpresa = 1 )  And ct.codCliente > 0

See which of the tips you can apply and if there is improvement