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 WHERE sur date


Sujet :

Développement SQL Server

  1. #1
    Membre émérite
    Profil pro
    Mangeur de gauffre
    Inscrit en
    Octobre 2007
    Messages
    4 413
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations professionnelles :
    Activité : Mangeur de gauffre

    Informations forums :
    Inscription : Octobre 2007
    Messages : 4 413
    Points : 2 498
    Points
    2 498
    Par défaut Optimisation WHERE sur date
    Bonjour

    Pour faire des selection sur dates, j'ai pris l'habiture d'effectuer un
    pour ignorer les HH:MM:SS
    Et d'exprimer la date sous forme 'yyyyMMdd'

    Exemple

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT
    *
    FROM
      dbo.Tbl
      WHERE DATE is not null
      AND CAST(DATE AS DATE) <>'18991230'
      AND CAST(DATE AS DATE) >='20100101';
    Mais on me signale que la formule ci-apres serait beaucoup plus performante

    Est-ce exact ?
    Merci de votre avis

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT
    *
    FROM
      dbo.Tbl
      WHERE DATE is not null
      AND Date <>'1899-12-30'
      AND Date >='2010-01-01';
    « Ils ne savaient pas que c'était impossible, alors ils l'ont fait ». (Twain)

  2. #2
    Membre éprouvé
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2009
    Messages
    623
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Août 2009
    Messages : 623
    Points : 1 049
    Points
    1 049
    Par défaut
    pose un index sur la colonne Date, joue tes 2 requêtes avec le plan d'exécution réel et compare la différence La première requête ne devrait pas utiliser l'index, la seconde devrait l'utiliser (si j'ai bien tout lu)
    Blog Perso | Kankuru (logiciel gratuit pour SQL Server)

  3. #3
    Membre émérite
    Profil pro
    Mangeur de gauffre
    Inscrit en
    Octobre 2007
    Messages
    4 413
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations professionnelles :
    Activité : Mangeur de gauffre

    Informations forums :
    Inscription : Octobre 2007
    Messages : 4 413
    Points : 2 498
    Points
    2 498
    Par défaut
    Merci

    J'aurais effectivement du tester avant de m'interroger sur des sornettes

    Je pense que le gars qui a prétendu ca avait fumé une mauvaise moquette !
    « Ils ne savaient pas que c'était impossible, alors ils l'ont fait ». (Twain)

  4. #4
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Bonjour,

    Je pense que vous avez mal compris la réponse de darkelend.

    La deuxième requête est en effet préférable, car elle pourra utiliser un index sur la colonne date s'il existe. La première non.

  5. #5
    Membre expérimenté

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Points : 1 668
    Points
    1 668
    Billets dans le blog
    8
    Par défaut
    darkelend et aieeeuuuuu ont eu bien raison sur l'utilisation ou non des indexes pour chacune des 2 requêtes.

    Les opérateurs comme NOT, <>, NOT EXISTS, NOT IN, NOT LIKE et les fonctions utilisateurs (voire même les fonctions intégrées comme IsNull()) sont considérés comme NON-SARG

    Pour ces expressions NON-SARG, l'optimiseur, pour des raisons que l'on peut facilement deviner, compte tenu de la manière dont les indexes sont utilisés pour optimiser l'accès aux données, ne peut pas utiliser, ni bénéficier des indexes.

    Pour revenir à votre requête, La clause comprenant 3 lignes :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    WHERE DATE is not null
     AND Date <>'1899-12-30'
     AND Date >='2010-01-01';
    peut être aisément transformée en une et une seule ligne !
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE DATE >= '2010-01-01';
    En effet :
    - Le résultat sera exactement identique ! En effet, certaines applications génèrent la date '1899-12-30' lorsque la date n'est pas renseignée par l'application (par l'IHM)
    - Les dates considérées sémantiquement renseignées sont les dates qui sont strictement supérieure à '1899-12-30' (date > '1899-12-30')
    - Par ailleurs, une date NULL ne sera jamais supérieure ou égale à '2010-01-01' !
    - Et comme '2010-01-01' > '1899-12-30', on évite autant que peu la tautologie ! Donc CQFD.

    En revanche, la clause NON-SARG
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    WHERE DATE is not null
     AND Date <>'1899-12-30'

    Peut être avantageusement remplacée par une clause SARG, sans altérer le résultat, par :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE DATE > '1899-12-30'
    et ce pour presque les mêmes raisons que j'ai énumérées ci-dessus.

    A+
    "Une idée mal écrite est une idée fausse !"
    http://hamid-mira.blogspot.com

  6. #6
    Membre émérite
    Profil pro
    Mangeur de gauffre
    Inscrit en
    Octobre 2007
    Messages
    4 413
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations professionnelles :
    Activité : Mangeur de gauffre

    Informations forums :
    Inscription : Octobre 2007
    Messages : 4 413
    Points : 2 498
    Points
    2 498
    Par défaut
    Merci hmira

    Tes explications sont toujours tres claire et détaillées
    Je comprends parfaitement ta demonstration et il est vrai que mon exemple etait un peu barbare !

    Mais ca ne me donne pas encore une explication claire sur l'exploitation ou non d'un index

    Si je traduis ce que je pense avoir compris

    L'expression suivante
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE DATE BETWEEN '20100101' AND '20100102'
    Sera plus performante que

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE CAST(DATE AS DATE)='20100101'
    Pourtant je ne vois pas en quoi ce CAST pourrait empecher l'utilisation de l'index

    C'est comme si j'avais un index sur Nom,Prenom et que je fesais une recherche WHERE NOM='hmira' il utilisera bien mon index ... non ?
    « Ils ne savaient pas que c'était impossible, alors ils l'ont fait ». (Twain)

  7. #7
    Membre émérite
    Profil pro
    Mangeur de gauffre
    Inscrit en
    Octobre 2007
    Messages
    4 413
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations professionnelles :
    Activité : Mangeur de gauffre

    Informations forums :
    Inscription : Octobre 2007
    Messages : 4 413
    Points : 2 498
    Points
    2 498
    Par défaut
    Helas je confirme

    Le cast tue !

    Mais neanmoins je reste convaincu qu'un Engine un peu malin voyant qu'il y a un index pourait optimiser ce cast pour le traduire en between sur l'index !
    « Ils ne savaient pas que c'était impossible, alors ils l'ont fait ». (Twain)

  8. #8
    Membre expérimenté

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Points : 1 668
    Points
    1 668
    Billets dans le blog
    8
    Par défaut
    Citation Envoyé par olibara Voir le message
    Mais ca ne me donne pas encore une explication claire sur l'exploitation ou non d'un index
    L'index organisé en B-arbre ainsi que les statistiques sur les colonnes de l'indexes sont basés sur des vraies valeurs des dites colonnes. Ils ne sont nullement basées sur une quelconque fonction (y compris le transtypage implicite ou explique) appliquée à ces valeurs.

    Dès lors que vous appliquez une fonction ou un transtypage implicite ou explicite sur une colonne, l'optimiseur n'a plus aucun repère ni aucune information pouvant le guider pour élaborer un chemin optimal.

    Prenez par exemple un annuaire téléphonique en papier, et au lieu de rechercher un nom en particulier en clair exemple
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE Nom = 'Emile Zola'
    vous disposez uniquement de la la fonction MD5('Emile Zola') soit 7b54184483ad1f362d6b85ca1a1aa69d. votre clause WHERE sera schématiquement exprimée ainsi
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE Md5(Nom) = '7b54184483ad1f362d6b85ca1a1aa69d'
    Faites en l'expérience avec un vrai annuaire en papier, même avec quelques milliers de personnes, et vous comprendrez vite votre calvaire pour trouver M. Emile Zola. Vous serez même tenté de dire "J'accuse !" les expressions NON-SARG responsables de tous les maux des DBA !

    Citation Envoyé par olibara Voir le message
    Mais néanmoins je reste convaincu qu'un Engine un peu malin voyant qu'il y a un index pourrait optimiser ce cast pour le traduire en between sur l'index !
    Le problème est beaucoup plus complexe et plus subtile que vous l'imaginez. En attendant un "Engine un peu malin" évitez autant que possible les expressions NON-SARG dans les clause WHERE.

    A+
    "Une idée mal écrite est une idée fausse !"
    http://hamid-mira.blogspot.com

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

Discussions similaires

  1. [2008R2] Optimisation VIEW sur DATE
    Par shakly dans le forum MS SQL Server
    Réponses: 7
    Dernier message: 07/10/2013, 11h20
  2. Réponses: 3
    Dernier message: 19/06/2007, 23h34
  3. [DTS] Problème avec clause WHERE sur Date
    Par bibou dans le forum MS SQL Server
    Réponses: 5
    Dernier message: 28/06/2006, 13h18
  4. Faire un where sur un champs date qui peut etre vide
    Par blueman dans le forum Oracle
    Réponses: 12
    Dernier message: 03/01/2006, 14h46
  5. Clause Where sur une Date
    Par Zebulonn dans le forum Installation
    Réponses: 31
    Dernier message: 20/10/2005, 12h56

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