Publicité
+ Répondre à la discussion
Page 1 sur 2 12 DernièreDernière
Affichage des résultats 1 à 20 sur 22
  1. #1
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut [2008] Optimisation par Index filtré sur existant

    Bonjour.

    Hypothèse :
    Avec la possibilité de filtré les index, il devient facile d'optimiser les insertions.
    En créant une colonne "INDEXED" de type BIT et en filtrant les index sur "INDEXED = 1", on peut ainsi faire des insert oµ simplement en mettant INDEXED à 0, on évite le recalcule de l'index.

    Imaginez le pseudo code suivant :
    Code :
    1
    2
    3
    4
    5
    FAIRE 1000x
    INSERT TBL (DATA, Indexed) VALUE('Test', 0)
    FIN_FAIRE 1000x
     
    UPDATE TBL SET INDEXED = 1 WHERE INDEXED = 0
    Cela irait plus vite (en fonction du nombre d'indexes existants) que si les index n'étaient pas filtrées et recalculée à chaque INSERT.


    Maintenant, ma question :

    Est-ce que je me trompe ?
    Most Valued Pas mvp

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 274
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 274
    Points : 27 268
    Points
    27 268

    Par défaut

    Pas forcément.... En effet avec Arian Papillon dans le cadre des journées SQL Days Chez MS les 11 et 12 décembre, nous allons démontrer l'inverse !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut

    Est-ce que vous pourriez déjà m'en dire d'avantage, svp ?
    Most Valued Pas mvp

  4. #4
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 274
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 274
    Points : 27 268
    Points
    27 268

    Par défaut

    Et dévoilez tout nos petits secrets maintenant... !

    Pas question. Mais relancez moi après la conférence si vous y avez pas assisté !

    En tout cas, laissez faire les index et mettez les toujours à bon escient. Prévoyez toujours un fill factor d'au moins 90% voire 80...

    Enfin, lisez ceci : http://sqlpro.developpez.com/cours/s...ivation-index/


    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  5. #5
    Membre Expert
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    2 798
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 2 798
    Points : 2 396
    Points
    2 396

    Par défaut

    Un lien pour des infos sur la conférence ?

    Google ne semble pas être mon ami sur ce coup-là... (je trouve des résultat pour novembre )

    Ah, j'ai p-e trouvé par ici.

    Mais c'est en fait le 10 et le 11 alors (pour ça que je ne trouvais pas *siffle*)
    Kropernic (anciennement Griftou).

  6. #6
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut

    Jusqu'à présent, tous les tests que j'ai faits me donnent des résultats qui traduisent bel et bien un gain de performance dans les insertions.
    Most Valued Pas mvp

  7. #7
    Membre Expert
    Inscrit en
    août 2009
    Messages
    1 041
    Détails du profil
    Informations forums :
    Inscription : août 2009
    Messages : 1 041
    Points : 1 601
    Points
    1 601

    Par défaut

    Un gain de performance dans les insertions, peut-être.
    Mais ensuite il faut :
    1) Considérer l'update.
    2) Modifier toutes les requêtes pour rajouter la condition "indexed = 1", sans quoi l'index ne sera jamais utilisé ...

    Ce qui revient à peu près (mais en plus compliqué, et porteur à mon sens de problèmes si on se retrouve avec des lignes qui resteraient à indexed = 0) à avoir une table temporaire dans laquelle on fait toutes les insertions avant de faire l'insert final en une fois.

  8. #8
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 274
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 274
    Points : 27 268
    Points
    27 268

    Par défaut

    Les développeurs oublient toujours qu'il ne sont pas seuls.... Il ne pense pas à ce qui se passe au niveau de la concurrence !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  9. #9
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    Les développeurs oublient toujours qu'il ne sont pas seuls.... Il ne pense pas à ce qui se passe au niveau de la concurrence !

    A +
    Oui, oui, les praticiens ont énormément de lacunes que n'ont évidemment pas les théoriciens.
    Cependant, de quelle concurrence parlez-vous ?
    Most Valued Pas mvp

  10. #10
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut

    Citation Envoyé par Rei Ichido Voir le message
    Un gain de performance dans les insertions, peut-être.
    Mais ensuite il faut :
    1) Considérer l'update.
    2) Modifier toutes les requêtes pour rajouter la condition "indexed = 1", sans quoi l'index ne sera jamais utilisé ...

    Ce qui revient à peu près (mais en plus compliqué, et porteur à mon sens de problèmes si on se retrouve avec des lignes qui resteraient à indexed = 0) à avoir une table temporaire dans laquelle on fait toutes les insertions avant de faire l'insert final en une fois.
    Vous avez vu juste.
    Ce que vous dîtes est en fait la raison qui me font allez à tâtons sur ce terrain.

    Néanmoins, faire l'update d'une colonne prends moins de temps de la recopie (et suppression) de l'ensemble des données.
    Donc l'insertion reste plus rapide.

    Donc, c'est en effet, surtout la modification d'un schéma existant et le bouleversement total que cela implique sur les liaison et utilisation d'indexes qui me freinent.


    Enfin, je vais quand même rester attentif à ce qu'SQL Pro aura dit sur le sujet après ses conférences. En espérant que ces dernières ne se basent pas trop sur des cas biaisés et ne travestissent pas tous les contre arguments (cette fois).
    Most Valued Pas mvp

  11. #11
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 274
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 274
    Points : 27 268
    Points
    27 268

    Par défaut

    Citation Envoyé par Sergejack Voir le message
    ...de quelle concurrence parlez-vous ?
    Des miliers d'utilisateurs qui en parallèle à votre requête veulent eux aussi avoir accès à la même table pour d'autres raisons... lectures, écritures.

    En fait vous regardez votre arbre, alors que c'est la forêt qu'il faut contempler dans son ensemble !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  12. #12
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    Des miliers d'utilisateurs qui en parallèle à votre requête veulent eux aussi avoir accès à la même table pour d'autres raisons... lectures, écritures.

    En fait vous regardez votre arbre, alors que c'est la forêt qu'il faut contempler dans son ensemble !

    A +
    Vous avez super bien cerné ma méthode !
    Pour tout vous dire, ce n'est même pas l'arbre que je regard, mais l'ombre de l’écureuil.



    C'est sarcastique évidemment.
    Mais pour reprendre votre métaphore, sachez qu'il m'arrive de réfléchir à la nature de l'arbre pour planifier mes forêts.
    Most Valued Pas mvp

  13. #13
    Membre Expert
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    2 798
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 2 798
    Points : 2 396
    Points
    2 396

    Par défaut

    Au risque de me faire taper sur les doigts, je pense qu'il faut aussi prendre en compte le contexte pour lequel on développe.

    Développez quand on s'attend plusieurs milliers d'accès concurrents, ce n'est pas pareil que si on s'attend à quelques uns non ?

    J'imagine que certaines pratiques sont avantageuses dans un cas et pas dans l'autre.
    Kropernic (anciennement Griftou).

  14. #14
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut

    SQL Pro, pouvez-vous maintenant divulguer vos secrets de couple ?
    Most Valued Pas mvp

  15. #15
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    Et dévoilez tout nos petits secrets maintenant... !

    Pas question. Mais relancez moi après la conférence si vous y avez pas assisté !

    En tout cas, laissez faire les index et mettez les toujours à bon escient. Prévoyez toujours un fill factor d'au moins 90% voire 80...

    Enfin, lisez ceci : http://sqlpro.developpez.com/cours/s...ivation-index/


    A +
    Et donc... qu'avez-vous finalement à nous apprendre sur le sujet ?
    Most Valued Pas mvp

  16. #16
    Membre Expert
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    2 798
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 2 798
    Points : 2 396
    Points
    2 396

    Par défaut

    Trop déçu de na pas avoir pu y aller
    Kropernic (anciennement Griftou).

  17. #17
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut

    Bon, on a eu droit à une joli coup de pub, un bel effet d'annonce.
    Maintenant, pour apporter un véritable début de réponse :

    Code :
    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
    31
    32
    33
    34
    35
    36
     
    CREATE TABLE tTstFIdx (
    	ID INT NOT NULL IDENTITY
    	, DATA DATETIME NULL
    	, XDATA NVARCHAR(50) NULL
    )
    GO
     
    CREATE NONCLUSTERED INDEX IDX_tTstFIdx_DATE ON tTstFIdx(DATA) WHERE DATA <> '2010-01-01'
    GO
     
    SET NOCOUNT ON
    SET STATISTICS IO ON
     
    INSERT tTstFIdx (DATA, XDATA)
    SELECT
    	DATEADD(dd, n, '2010-01-01')
    	, X.v
    FROM tTally AS N
    CROSS JOIN (
    	VALUES('A'), ('B')
    ) AS X(v)
    WHERE N.n BETWEEN 1 AND 1000
     
     
    INSERT tTstFIdx (DATA, XDATA)
    SELECT
    	DATEADD(dd, 0, '2010-01-01')
    	, X.v
    FROM tTally AS N
    CROSS JOIN (
    	VALUES('A'), ('B')
    ) AS X(v)
    WHERE N.n BETWEEN 1 AND 1000
     
    SET STATISTICS IO OFF
    Table 'tTstFIdx'. Scan count 0, logical reads 6303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'tTally'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'tTstFIdx'. Scan count 0, logical reads 2006, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'tTally'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Après plusieurs essaie du même acabits, le résultat attendu reste toujours le résultat obtenu :

    Si vous faites une insertion dans la table avec des valeurs excluent (filtrées) de certains indexes, ces derniers ne seront pas mis à jour (c'est logique).
    Cela fait donc un beau gain de performance SURTOUT si vous faites plein de petite insertions plutôt que quelques grosses.

    Cette approche est donc idéale quand vous travaillez avec une couche programmatique qui ne vous permet/facilite pas le regroupement d'opérations d'insertion.
    Most Valued Pas mvp

  18. #18
    Membre Expert
    Inscrit en
    août 2009
    Messages
    1 041
    Détails du profil
    Informations forums :
    Inscription : août 2009
    Messages : 1 041
    Points : 1 601
    Points
    1 601

    Par défaut

    Oui mais non.

    Pour que votre index soit utilisé, il faut maintenant que la colonne indexed soit mise à jour à 1 ; vous allez donc certainement modifier un certain nombre de requêtes (mais peut-être pas toutes) pour inclure ce filtre. Rien que là, on voit que votre application risque de gros soucis :
    - parce que certaines requêtes n'auront pas ce filtre supplémentaire, et donc n'utiliseront pas l'index
    - parce que certaines requêtes auront ce filtre, et du coup ne ramèneront pas de lignes là où elles le devraient (index "pas à jour"), générant des bugs parce que considérant qu'il n'y a pas d'objet associé. Par exemple, vous avez une Table "Fil de discussion" et une Table "Posts". Le fil de discussion est créé, et indexé. Un post est créé, mais pas indexé. Ah zut, votre requête qui fait le lien ne vous ramène plus le post ... fort risque de plantage quand vous allez vouloir supprimer le fil de discussion (considéré comme vide) ou faire une insertion d'un "premier post".

    À la limite le vrai gain de performance serait de supprimer l'index ! Parce que là, de toute façon votre façon de procéder n'a de sens que si vous gérez de temps en temps votre index (avec la mise à jour de indexed vers 1).
    Vous pourriez tout autant et de façon nettement plus claire, ne pas rajouter cette colonne, désactiver l'index dans le cas général, et le reconstruire avant vos gros traitements.

  19. #19
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 274
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 274
    Points : 27 268
    Points
    27 268

    Par défaut

    En fait le problème est simple : sans index adéquat, une insertion nécessite un verrou préalable de table (scan complet) pour pouvoir placer la ligne au bon endroit... Donc la table est bloquée intégralement un certain temps, ce qui contribue à diminuer drastiquement la concurrence !
    Avec un index adéquat, il n'y a pas de verrou de table posé, mais directement un verrou de ligne, voir de plage de clef....

    Le problème est que les développeurs oublient toujours deux éléments fondamentaux des SGBDR dans leur mode de pensée :
    1) la concurrence (ils se croient seul au monde à lancer le même bout de code...)
    2) les aspects ensemblistes des requêtes (parallélisme et pas d'ordre natif...)

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  20. #20
    Membre émérite

    Inscrit en
    juillet 2006
    Messages
    1 400
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 400
    Points : 951
    Points
    951

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    En fait le problème est simple : sans index adéquat, une insertion nécessite un verrou préalable de table (scan complet) pour pouvoir placer la ligne au bon endroit...
    Vous êtes sérieux, là... ?
    Qui a dit qu'il n'y avait pas de PK (non filtrée XD) ?

    Bah... dans le genre foireux, prendre une table sans PK pour faire la critique des indexes filtrés, c'est encore du triple A.
    Most Valued Pas mvp

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •