CODEWITHSUNDEEP
sql-servert-sql
user3548593
user3548593

Reputation: 499

Optimize query performance in SQL Server

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?

Upvotes: 0

Views: 92

Answers (1)

Smileek
Smileek

Reputation: 2782

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.

Upvotes: 1

Related Questions