IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

[Optimisation] Utilisation de l'index avec une date


Sujet :

Développement SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Homme Profil pro
    Inscrit en
    Octobre 2009
    Messages
    118
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Octobre 2009
    Messages : 118
    Par défaut [Optimisation] Utilisation de l'index avec une date
    Bonjour,

    J'utilise SQL Server 2005 Ent ed 64 bits pour notre base de données d'archives. Le serveur est en SP3.

    Nous avons dans cette base deux tables définies comme ci après :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
     
    USE [TPGD_Arc]
    CREATE TABLE [dbo].[CBL](
    	[TRIEUR] [tinyint] NOT NULL,
    	[FICHIER_N7] [int] NOT NULL,
    	[RANG] [int] NOT NULL,
    	[LONGUEUR] [smallint] NULL,
    	[UTILITE] [smallint] NOT NULL,
    	[CODE_POSTAL_POS] [smallint] NULL,
    	[CODE_POSTAL_LEN] [smallint] NULL,
    	[POIDS_POS] [smallint] NULL,
    	[POIDS_LEN] [smallint] NULL,
    	[POIDS_COEF] [smallint] NULL,
    	[TABLE_TRI] [char](2) NULL,
    	[ALGO] [smallint] NULL,
    	[LIEN_POS] [smallint] NULL,
    	[LIEN_LEN] [smallint] NULL,
    	[CODE_CLIENT_POS] [smallint] NULL,
    	[CODE_CLIENT_LEN] [smallint] NULL,
    	[NUMERO_COLIS_POS] [smallint] NULL,
    	[NUMERO_COLIS_LEN] [smallint] NULL,
    	[PARTIE_FIXE_POS] [smallint] NULL,
    	[PARTIE_FIXE_LEN] [smallint] NULL,
     CONSTRAINT [PK_CBL] PRIMARY KEY CLUSTERED 
    (
    	[TRIEUR] ASC,
    	[FICHIER_N7] ASC,
    	[RANG] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    Pas d'index supplémentaires sur cette table. La seconde :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    CREATE TABLE [dbo].[FICHIER_N7](
    	[TRIEUR] [tinyint] NOT NULL,
    	[FICHIER_N7] [int] NOT NULL,
    	[NOM] [varchar](50) NOT NULL,
    	[DATE_INTEGRATION] [datetime] NOT NULL,
     CONSTRAINT [PK_FICHIER_N7] PRIMARY KEY CLUSTERED 
    (
    	[TRIEUR] ASC,
    	[FICHIER_N7] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 75) ON [PRIMARY]
    ) ON [PRIMARY]
    Cette table possède un index en plus de la clef :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    CREATE NONCLUSTERED INDEX [IX_FICHIER_N7_DATE_INTEGRATION] ON [dbo].[FICHIER_N7] 
    (
    	[TRIEUR] ASC,
    	[FICHIER_N7] ASC,
    	[DATE_INTEGRATION] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 75) ON [PRIMARY]
    Mon problème est que les indexs ne sont pas utilisés correctement lorsque j'exécute la requête suivante :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
     
    DECLARE @DUREE AS INT
    SET @DUREE = 13
     
    SELECT
    	CBL.FICHIER_N7
    FROM
    	dbo.CBL CBL	
    JOIN
    	dbo.FICHIER_N7 FI7
    ON
    	CBL.TRIEUR = FI7.TRIEUR
    	AND CBL.FICHIER_N7 = FI7.FICHIER_N7
    	AND FI7.DATE_INTEGRATION < DATEADD( MONTH, -@DUREE, GETDATE() )
    Il me fait un index scan sur les deux tables.
    Si j'effectue un select sur une seule table à la fois avec des valeurs fixes, alors il fait un seek. Le pire c'est que si je remplace la variable @duree dans le code ci dessus par un valeur "en dur" alors il fait un index seek sur la table CBL.

    Bref je comprend pas bien comment il utilise les indexs avec une date.

    J'ai le même problème dans une table de 35M de lignes et un between sur une colonne indexée avec un index couvrant.


    Merci pour votre aide !!

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 998
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 998
    Billets dans le blog
    6
    Par défaut
    Tout dépend de la dispersion des données et du volume des données retournées. Sachant qu'il est plus complexe de parcourir un index (seek) que de lire une table (scan) ou un index, si le volume attendu en sortie est supérieur à un certain seuil (entre 10 et 30%) alors le seek ne vaut pas le "coût" et l'optimiseur passe au scan.

    Pour vérifier cette hypothese, vous pouvez analyser les statistiques d'exécution de la requête brute (SET STATISTICS IO ON / SET STATISTICS TIME ON) ou en lui forçant l'index :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    JOIN
    	dbo.FICHIER_N7 FI7 WITH(INDEX (IX_FICHIER_N7_DATE_INTEGRATION))
    Sachez enfin qui est rare que l'optimiseur est tort et que SQL Server se trompe !

    Au passage avez vous forcé la paramétrisation des requêtes pour cette base ?
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER DATABASE ??? SET PARAMETRIZATION FORCED
    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre confirmé
    Homme Profil pro
    Inscrit en
    Octobre 2009
    Messages
    118
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Octobre 2009
    Messages : 118
    Par défaut
    Tout d'abord merci pour ta réponse rapide !

    Le fait de lui forcer l'index ou pas ne change rien. Il l'utilise dans les deux cas mais simplement il le scanne complètement.
    Mais j'ai bien compris que cela dépend surtout du nombre de lignes impactées. Dans mon cas il n'y a pas de lignes la plupart du temps ( c'est une requête de delete qui purge les lignes antérieures à X mois). Donc il scanne.

    Ta réponse explique aussi pourquoi lorsque j'effectue une somme sur 50% des lignes d'une table de 34 millions de lignes, alors j'aurais beau mettre tous les indexes que je veux, il fait systématiquement un scan.
    (et donc c'est un peu long mais sans plus : environ 15 secondes )

    Edit : Je ne sais pas (encore) ce que signifie la "parametrisation" des requêtes. Je regarde cela tout de suite.

  4. #4
    Membre confirmé
    Homme Profil pro
    Inscrit en
    Octobre 2009
    Messages
    118
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Octobre 2009
    Messages : 118
    Par défaut
    Ok alors pour la parametrisation, j'ai compris que cela indique au moteur que les requêtes sont toutes paramétrées. Cela lui permet de les mettre dans le cache et donc de ne pas avoir a les recompiler a chaque fois.

    Nous n'utilisons JAMAIS de requêtes directes. Uniquement des procédures stockées. Alors dans ce cas, cela me semble utile de forcer ce paramètre car il semble améliorer les performance dans notre cas.

    Mais il doit bien y avoir un revers de la médaille. Pourquoi n'est il pas actif par défaut alors ??

    PS : cette base est abonnée à un serveur de production qui push les insert/update mais pas les delete selon unmode transactionnel. Je ne sais pas si cela a une importance.

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 998
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 998
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par Batou69 Voir le message
    Mais il doit bien y avoir un revers de la médaille. Pourquoi n'est il pas actif par défaut alors ??
    Parce que cela nécessite un peu plus de travail si aucune des requêtes passées ne sont pré paramétrées....

    Ta réponse explique aussi pourquoi lorsque j'effectue une somme sur 50% des lignes d'une table de 34 millions de lignes, alors j'aurais beau mettre tous les indexes que je veux, il fait systématiquement un scan.
    (et donc c'est un peu long mais sans plus : environ 15 secondes )
    Il n'y a de toutes façons pas d'autre moyen pour calculer une somme une moyenne ou un count. Si c'était un max ou min l'index serait utilisé en SEEK.
    Si la plage était plus réduite (en dessous de 19% alors il ferait peut être un seek).

    Mais pour un tel cas de figure sur une table de ce volume il y a longtemps que j'aurais mis en place une vue indexée !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  6. #6
    Membre confirmé
    Homme Profil pro
    Inscrit en
    Octobre 2009
    Messages
    118
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Octobre 2009
    Messages : 118
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Mais pour un tel cas de figure sur une table de ce volume il y a longtemps que j'aurais mis en place une vue indexée !
    Certes cela est plus efficaces dans bien des cas.
    Mais la table dont je parle est une table d'historique glissant sur un an. Il y a par jour environ 300 000 inserts (et donc 300 000 deletes pour les lignes agées de plus d'un an)
    La requete que je dois optimiser fait un agregat du nombre de lignes sur les six derniers mois selon trois groupement différents.

    Si je veux faire une vue, alors il m'en faut non pas une mais trois ce qui pourrait prendre une place conséquente non ? ( je n'ai pas idée de la volumétrie nécessaire). D'autre part, pour faire le groupement sur les six dernier mois, il me faudra faire une clause where contenant le getdate(). Je ne suis pas un expert mais il me semble que l'actualisation de la vue sera alors très couteuse.
    Enfin lorsque la requête qui me pose problème est exécutée dix fois par jour c'est déjà le bout du monde. Cela n'empeche pas qu'elle doit être rapide au moment où l'on en besoin....

    En tout dernier recours je pourrais construire une table de consolidation par jour que je remplirait tous les jours avec un batch, mais cela perd un peu en précision par rapport au getdate()

    Que pouvez vous me conseiller dans ce cas ?

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Utiliser Match avec une date
    Par c.piette dans le forum Macros et VBA Excel
    Réponses: 1
    Dernier message: 20/05/2015, 11h55
  2. probleme avec une date
    Par somatino dans le forum Requêtes
    Réponses: 3
    Dernier message: 29/03/2006, 15h56
  3. [JDBC] Requête avec une date sous la forme dd/MM/yyyy
    Par sylviefrfr dans le forum JDBC
    Réponses: 6
    Dernier message: 12/11/2005, 09h35
  4. Réponses: 2
    Dernier message: 16/08/2005, 15h33
  5. probléme avec une date/string dans un CommandText
    Par critok dans le forum Bases de données
    Réponses: 5
    Dernier message: 09/02/2005, 15h30

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo