Index rendering sql query too slow

Question

Index rendering sql query too slow

Navigation

#1 by (1 votes)

1

The DBMS in question is SQL Server

On my system, we have a table that stores the journal entries. The table has a considerable amount of records, currently (around 1.2miles).

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

You have a CustomerCode column. Recently an index was created on it, to use in queries filtering through it.

However, the query became extremely slow. Something "wrong" happened .. with the data already in memory, the query took 1 or 2 seconds to drop the index ... and with the created index again, it takes several minutes!

The query was slow on 2 users that I tested. I took a backup of one of them, and tested it on my environment, the same behavior was identified (ie: it should be the index really)

Detail: In this query you have "CodClient not in (subquery) ..." Because you have the index, you may have changed so much because of this?

Can an index leave a query slower in this case?

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

Inquiry:

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 generated client statistics in execution when the index EXISTS: (I was going to put, but I could not put more than 2 links to images.)

Andinsequence,theexecutionplanandstatisticsgeneratedbythequeryexecutionwhenitdoesNOThavetheindex(muchfaster):(Iwasgoingtoput,butIcouldnotputmorethan2linkstoimages.)

Can anyone give some light, what could have happened?

sql-server

asked by anonymous 18.05.2016 / 16:06

1 answer

jquery dynamic validate Send data by Angular

score 1 · Answer 1

As I was not informed of all the script creation of the tables I will start with recommendations in index and query creations.

Regarding the Data field: The functions used in the field make the SQL Engine ignore any indexes, so prefer the between

Join with the CD_PlanoContas table: The size of the table was not informed, but it would be the case to create 2 indices One in the table itself CD_PlanoContas with the fields (Cod, CodPlano) and the other in the CT_Contabilidade in the fields (CodAcesso, CodPlano)

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

1 - creation of an index in 'CT_Contabilidade com campos: (SI.CodPlano, SI.codcliente, SI.CodAcesso, Data, CodEmpresa) include (codacesso)

2 - Remove the subquery and transform it into a temporary table. 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