Quand créer les index ? [Résolu]

[WebPac]

Bonjour tout le monde.

Depuis fort longtemps, je pratique ainsi : je crée la table et les champs, je crée les index, puis j'insère l'ensemble des données. Ainsi elles sont indexées.

J'ai entendu dire qu'il valait mieux créer les index après avoir inséré les données, car ainsi l'insertion est beaucoup plus rapide.
Mais je me pose une question, si on crée les index après l'insertion, est-ce que les données sont bien indexées ?

Merci pour vos éclairsissements sur la question.

[Xo]

Effectivement, un index accelère la lecture (si la clause WHERE de la requête porte sur un index), mais ralentit un peu l'insertion.

Dans la plupart des applicatifs, les index sont gérés soit dès la conception, soit après coup si l'on s'aperçoit qu'ils sont utiles : mais on attend pas d'avoir "toutes" les données avant de les créer, c'est pourquoi je me demande quel est ton cas de figure, pour insérer les données (massivement, j'imagine) avant de les indexer ?

En tout cas, je pense pouvoir affirmer sans trop dire de bêtises qu'un index est pleinement "fonctionnel" dès sa création.

Un petit complément : Les index

[WebPac]

Voici les précisions que j'aurrais dû donner dès le départ :
* C'est moi-même qui crée les tables et les index.
* Il n'y a pas de clé primaire dans les tables.
* Lorsque je crée la table, de suite après, je fais une insertion massive de données.
* Après l'insertion massive de données, il n'y plus d'autre insertion de données.
* Après l'insertion massive de données, la table est uniquement utilisée en lecture seule (juste des SELECT).

La création des index permet d'optimiser les temps de requêtage sur la table, il n'y a pas de soucis sur ce point là.
Mon problème est le temps d'insertion des données, qui peut suivant le cas dépasser plusieurs heures (plusieurs millions d'enregistrement voire plusieurs dizaines de millions).

C'est pourquoi, je voudrais savoir si je dois créer les index avant l'insertion des données afin qu'ils soient opérationnels lors des requêtages ou est-il possible de ne les créer qu'après l'insertion des données afin d'optimiser l'insertion ?

En fait, je me demande si la création des index après insertion des données rend le requêtage sur la table aussi rapide que si on les crée au tout début.

[makowski]

Envoyé par WebPac

En fait, je me demande si la création des index après insertion des données rend le requêtage sur la table aussi rapide que si on les crée au tout début.

normalement oui, ou alors change de SGBD

[WebPac]

Envoyé par makowski

normalement oui, ou alors change de SGBD

Merci pour la réponse. Et pour info, j'ai pas de SGBD atitré, ça peut être Firebird, SQL Serveur, Oracle ou MySQL, c'est l'utilisateur qui le choisit.

[Emmanuel Lecoester]

Si tu parles d'une inseration massive de données, il vaut mieux utiliser les loader de chaque base (sql loader pour Oracle) qui sont bien plus rapide que des inserts.

Ensuite c'est vrai qu'avec des millions de lignes on désactivait l'index, on insérait les données et on faisait un rebuild de l'index.

Autre point intersssant : si ton fichier en entrée est trié sur la PK de ta table çà aide.

[WebPac]

Envoyé par elecoest

Si tu parles d'une inseration massive de données, il vaut mieux utiliser les loader de chaque base (sql loader pour Oracle) qui sont bien plus rapide que des inserts.

Ensuite c'est vrai qu'avec des millions de lignes on désactivait l'index, on insérait les données et on faisait un rebuild de l'index.

Autre point intersssant : si ton fichier en entrée est trié sur la PK de ta table çà aide.

Oui, à terme, je compte utiliser les procédures des bases de données, mais ça demande du développement spécifique pour chaque base, donc pas à l'ordre du jour.

Merci pour l'info sur les clés primaires, c'est pas bête. Je vais voir ce que je vais pouvoir faire.

[keskidi]

Bonjour,

vous êtes sûr de votre coup ?
Il y a une dizaine d'années, c'est effectivement ce que j'avais fait (load sans index puis creation de l'index), mais j'ai lu qu'aujourd'hui c'était une connerie (du moins sur DB2), en gros temps(load sans index) + temps(rebuild) > temps (load avec index), autrement dit le temps qu'on gagne au load est perdu au rebuild.
Par contre, si on a plusieurs index, il vaut mieux effectivement les créer par la suite.

Y a t il des tests en ligne sur ce type de chargement (j'ai plusieurs milliards de lignes à charger) et pour le moment ça rame sec (estimation 6 jours pour le load) ?
Quand je vois que l'extraction du VSAM se fait en 1 heure, ça craint.

[WebPac]

Salut,

à l'époque, j'avais fait des tests de performance en interne sur Firebird et peut-être sur Oracle, SQL Serveur ou MySQL et les temps étaient meilleurs en indexant après le load.

Je n'avais pas trouvé de document sur le net qui expliquait la théorie ou le principe de fonctionnement sur le sujet qui donnerait les bonnes pratiques, donc les décisions se sont basées sur des données empiriques et non théoriques.

Si tu trouves de nouveaux éléments, je suis preneur.

[SQLpro]

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...ivation-index/

A +

[keskidi]

Envoyé par SQLpro

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...ivation-index/

A +

C'est bien ce qu'il me semblait, désactiver les primary key ne sert pas à grand chose, bon bien sûr, DB2 sur MVS, c'est surement pas Microsoft sql.
Donc en gros, prendre la table pour soi tout seul, débrancher le journal, supprimer les index, charger dans l'ordre des clés primaires et attendre - faudrait voir aussi du côté du freespace, je pense.
En gros, faut tester ...

[WebPac]

Le problème est le nombre d'intermédiaires et non uniquement un problème d'index.

En effet, je me doute que tu as dû coder une application qui réalise un outil d'ETLs, elle fait donc une requête sur une base source et un insert dans une autre base.
Les données doivent donc sortir de la première base, entrer dans ton application, être transformées et traitées puis envoyées dans la base destination.

Cela fait beaucoup d'intermédiaires qui font que les temps ne seront jamais comparables à ce que fera une base de données qui travaillera seule en elle-même avec moins d'intermédiaire et du code plus natif.

Mon application arrive en vitesse de pointe à insérer les données à 4000 lignes/seconde, ce qui donne des valeurs comme toi, cad en théorie 5 jours pour 2 milliards d'enregistrement (jamais réalisé, le plus gros utilisateur ne dépasse pas les 60 millions d'enregistrement).
Il paraît qu'un utilisateur avec une machine ad hoc a réussi à dépasser les 8000 lignes/seconde, mais je ne l'ai pas vu par moi-même pour le constater.

Une autre voix de recherche peut aussi être le commit, cad, ne pas faire de commit automatique mais n'en faire que tous les 1000 enregistrements par exemple ou moins souvent.

Sinon, il faut essayer de déporter ce travail au niveau de la base de données destination, que ce soit elle qui requête et insère les données, ça te permettra de zapper des intermédiaires.

Bon courage en tout cas.

[CinePhil]

Pour en revenir à la question de départ :

Envoyé par WebPac

J'ai entendu dire qu'il valait mieux créer les index après avoir inséré les données, car ainsi l'insertion est beaucoup plus rapide.
Mais je me pose une question, si on crée les index après l'insertion, est-ce que les données sont bien indexées ?

Évidemment oui !

Voir dans cette partie de discussion l'explication de fsmrel sur la manière dont sont construits les index (dans DB2 mais ça doit être similaire dans les autres SGBD).

Un truc simple à retenir :
Les données d'une table ne sont pas triées au fur et à mesure où elles sont insérées. Seuls les index permettent de faire des tris et des recherches efficaces.

Après, vous gagnerez peut-être quelques micro-secondes par ci par là si vos données se retrouvent physiquement stockées sur le disque dans un certain ordonnancement mais ce que vous gagnerez sur une requête par la lecture de moins de pages sera peut-être perdu par une autre qui demandera les données triées sur un critère différent.

[keskidi]

Envoyé par WebPac

En effet, je me doute que tu as dû coder une application qui réalise un outil d'ETLs, elle fait donc une requête sur une base source et un insert dans une autre base.
Les données doivent donc sortir de la première base, entrer dans ton application, être transformées et traitées puis envoyées dans la base destination.

C'est pas vraiment ça, j'ai une base actuelle en KSDS VSAM, et demain je veux qu'elle soit en DB2, même données, même applicatifs, mais avec la possibilité d'utiliser des outils de requêtes user-friendly, l'extraction VSAM est très rapide, mais le chargement DB2 très lent (en comparaison de KSDS => fichier LOAD), étant donné les volumes, toutes les options sont bonnes à prendre, j'épluche un max de doc, mais pour le moment, je ne trouve pas vraiment d'informations plus pertinentes que ce qui est fait à l'heure actuelle.