Improve slow query performance in MS SQL SERVER

Navigation
2

Are the indexes incorrect?

I'm having a hard time optimizing a query. I had another question for a simpler case that resolved, but in that case I'm not getting a better result. The following query is taking 19 seconds: 

select top 12
c.crec_codigo_pk, -- bigint
c.crec_dataInclusao, -- datetime
c.crec_dataVencimento, -- datetime
c.crec_dataPagamento, -- datetime
c.crec_restanteFinal, -- decimal
c.crec_valorPrincipal, -- decimal
c.crec_multaRestante, -- decimal
c.crec_jurosRestante, -- decimal
c.crec_acrescimoRestante, -- decimal
c.crec_desconto -- decimal
from tbContaReceber c
-- crec_isMovimentoConta  é bit
where crec_isMovimentoConta = 0 and crec_dataExclusao is null and
      crec_dataVencimento >= '2015-01-01' and crec_dataVencimento <= '2016-01-01' and
      crec_restanteFinal = 0 and pes_codigo_fk = 834

This query will search for accounts receivable for a period of time, filtering those that have not been deleted, as well as those that are account-type, that have no remaining value, filtering the accounts of a specific customer / vendor .

Indice: 

CREATE NONCLUSTERED INDEX [ix_consulta_1] ON [dbo].[tbContaReceber]
(
    [crec_isMovimentoConta] ASC,
    [crec_dataExclusao] ASC,
    [crec_dataVencimento] ASC,
    [crec_restanteFinal] ASC,
    [pes_codigo_fk] ASC,
    [fpag_codigo_fk] ASC,
    [crec_documento] ASC
)
INCLUDE (   
    [crec_codigo_pk],
    [crec_dataInclusao],
    [crec_dataPagamento],
    [crec_valorPrincipal],
    [crec_multaRestante],
    [crec_jurosRestante],
    [crec_acrescimoRestante],
    [crec_desconto]) 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

Comments that might be useful:

  • There are just over 5 million records in the table
  • this table has more fields (about 50)
  • most crec_dataExclusion records will have null value - on this test basis 100%
  • In this test 100% of crec_isMovimentoConta registers are value "0". In production it will be 50% wax.
  • field pes_codigo_fk has about 1894 distinct values distributed to 5 million records.
  • you may still have two more query criteria, but I have not put them in the where yet and it's taking 19 seconds
  • This delay occurs on the first query. However, restarting the server, or running DBCC DROPCLEANBUFFERS will delay the next query.

I really do not know if it is failure to structure this database, fault of repeated data, or failure of the index itself.

    
asked by anonymous 09.01.2017 / 20:03

1 answer

4

You try to change the where clause to the example below. Czech improves something: 

where 
pes_codigo_fk = 834
and crec_dataVencimento BETWEEN '2015-01-01' and '2016-01-01' 
and crec_dataExclusao is null 
and crec_isMovimentoConta = 0 
and crec_restanteFinal = 0

In addition, create a new item with the fields used in the above clause, in the order they are found.

    
09.01.2017 / 20:16
What's the difference between using SwingUtilities.invokeLater (Runnable) and EventQueue.invokeLater (Runnable) when invoking a window? JSON.parse error: Unexpected end of JSON input