Optimize query performance in SQL Server

Question

I have this query :

SELECT 
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[SOCIETE],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[DATE_COMPTABILISATION],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[TYPE_DOCUMENT],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[NUM_DOCUMENT],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[DESIGNATION],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[CODE_JOURNAL],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[MONTANT],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[MONTANT_DEBIT],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[MONTANT_CREDIT],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[TYPE_ORIGINE],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[NUM_ORIGINE],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[MONTANT_TVA],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[GROUPE_COMPTA_PRODUIT],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[GROUPE_COMPTA_MARCHE],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[LETTRE],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[TYPE_COMPTA_TVA],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[NUM_TRANSACTION],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[NUM_SEQUENCE],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[NUM_COMPTE],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[GROUPE_COMPTA_MARCHE_TVA],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[GROUPE_COMPTA_PRODUIT_TVA],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[CODE_BUDGET],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[ID],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[Dimension Value Code],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[CENTRE_COUT],
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[TYPE_COMPTE],
    CODE_TS
FROM 
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE] WITH(INDEX(DATE))
WHERE 
    [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[DATE_COMPTABILISATION] >='01/05/2014' 
AND [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[DATE_COMPTABILISATION] < '01/06/2014'

The query works fine but it is much too slow. Is there a way to optimize query execution time?

Does it work faster without forcing it to use the index? If the returned row count is big, adding all the fields in the query as included columns will probably help a lot, but that can have a big cost for all the updates — James Z
– James Z, Commented Jul 23, 2015 at 8:57
Is there an index on the [DATE_COMPTABILISATION] column? What does the query execution plan tell you? — marc_s
– marc_s, Commented Jul 23, 2015 at 9:04
And how many rows does this query select - out of how many rows total?? Is this returning half the table, or only a few rows? — marc_s
– marc_s, Commented Jul 23, 2015 at 9:06
Does your index include other columns? Most likely not, so it probably does a Key Lookup to fetch other columns in your result set and it's quite a heavy operation. — Evaldas Buinauskas
– Evaldas Buinauskas, Commented Jul 23, 2015 at 9:06

Smileek · Accepted Answer · 2015-07-23 09:19:43Z

1

The query is not so complicated, but there is at least one certain way to optimize it.

When you compare data of different types, sql server converts it all the time, on every check. You should use CAST or CONVERT to perform conversion one time in advance:

...
WHERE [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[DATE_COMPTABILISATION] >= CONVERT('01/05/2014', DATETIME2(7), 103)
    AND [dbo].[T_COMPTA_ECRITURE_COMPTABLE].[DATE_COMPTABILISATION] < CONVERT('01/06/2014', DATETIME2(7), 103)

I used DATETIME2(7) in this example - you'll prefer the very same type that DATE_COMPTABILISATION column has. 103 value at CONVERT params is the dd/mm/yyyy format. More on this here.

answered Jul 23, 2015 at 9:19

Smileek

2,9421 gold badge26 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimize query performance in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related