Discussion :

[BigFoot69]

Bonjour à tous,

Petite question lors de la conception d'une BDD d'une application ayant un traffic et un volume de données importants.
Cette application gère des petites annonces de véhicules de type différents (voiture, voiture sans permis, moto, vélo, trotinette, brouette , etc).
L'accès principales aux annonces se fait via une recherche qui portera soit sur tous les véhicules peu importe le type, soit sur un type particulier de véhicule.
La recherche se fait uniquement sur le nom du véhicule.
Donc soit je décide de rechercher Honda dans Tous les véhicules, soit Honda dans Moto par exemple.

Je me demande au niveau de la BDD si je dois faire un niveau d'abstraction du style :
1 table véhicule qui contiendra le type (VOITURE, MOTO, etc), le nom pour faire la recherche, les infos communes aux véhicules (nb de places etc) et une FK pour récupérer les infos des tables spécifiques ("Voiture", "Moto", "Vélo" qui contiendront les informations spécifiques à la catégorie).
Ou si je pars sur 1 table pour chaque type (1 table voiture, 1 table moto) avec pour chaque table une colonne nom, avec une vue ayant le nom de tous les véhicules pour faciliter ma recherche par nom toutes catégories confondues.
Ou si j'utilise la notion d'héritage présente dans PGSQL (INHERITS).

Générer une notion d'héritage au niveau du modèle est-il pénalisant pour les performances de lecture/écriture ?

Soyons fous (mais trop quand même), en disant que chaque table fille contient 100k enreg, ce qui donne notre table Vehicule à 1M enreg (partons sur 10 tables filles donc).
L'objectif principal est d'avoir une recherche multi catégories, et une recherche par catégorie.
Dans l'appli j'ai ma recherche multi catégorie qui prend donc en paramètre "nomVehicule" qui va me donner une query qui va fouiller les 1M d'enreg du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT id FROM Vehicule v WHERE v.nom ILIKE '%nomVehicule%'

ou par catégorie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT id FROM Vehicule v WHERE v.nom ILIKE '%nomVehicule%' and v.type = 'VOITURE'

Est-ce suffisamment performant ou faut-il envisager les autres solutions décrites plus haut ?

Merci pour vos lumières

[SQLpro]

Générer une notion d'héritage au niveau du modèle est-il pénalisant pour les performances de lecture/écriture ?

Bien au contraire !!!!

Les écritures étant bloquantes, la mise à jour dans une table unique bloque tout accès; Avec n tables, lors d'une écriture, vous laissez n-1 tables non bloquée...

Pour les lectures, moins je lit de données, plus je vais vite.... et moins je bloque les écritures.
par exemple si j'ai besoin de chercher des motos, quel est l'intérêt de ramener la couleur du toit, chose qui n'existe que pour les voitures ?

A +

[BigFoot69]

Merci pour votre réponse.

Je précise un peu le contexte car j'ai été un brin succinct.
La recherche sera en temps réel, donc si on tape 'Hon' dans la recherche multi catégorie, une liste déroulante sera affichée en proposant les 5 premiers résultats soit par exemple : 'Honda CBR (moto)', 'Honda Civic (voiture)', 'Honda CRZ (voiture)', etc.
Cette recherche se doit d'être performante pour ne pas ramer à proposer des choix à l'utilisateur.

J'estime la répartition des actions sur ces tables comme suit :
95% de select (via la recherche sur le nom par catégorie ou multicatégorie puis select unitaire sur l'id quand la personne sélectionne un des choix proposés)
1% d'update
4% d'insert

A priori on élimine donc la grosse table qui contient tout avec un champ 'type'.
Il me reste 3 solutions :
- gestion d'un héritage automatique (via INHERITS de postgre) ce qui donne le schéma suivant :

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
CREATE TYPE type_vehicule AS ENUM ('voiture', 'moto');
 
CREATE TABLE vehicule (
	id BIGSERIAL PRIMARY KEY,
	nom CHARACTER VARYING(255) NOT NULL,
	type type_vehicule,
	constructeur CHARACTER VARYING(255) NOT NULL,
	nbPlaces SMALLINT NOT NULL
);
 
CREATE TABLE voiture (
	nbCV SMALLINT NOT NULL,
	nbPortes SMALLINT NOT NULL
) INHERITS (vehicule);
 
CREATE TABLE moto (
	cm3 SMALLINT NOT NULL
) INHERITS (vehicule);

- gestion de l'héritage manuellement ce qui donne le schéma suivant :

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
CREATE TYPE type_vehicule AS ENUM ('voiture', 'moto');
 
CREATE TABLE vehicule (
	id BIGSERIAL PRIMARY KEY,
	nom CHARACTER VARYING(255) NOT NULL,
	type type_vehicule,
	constructeur CHARACTER VARYING(255) NOT NULL,
	nbPlaces SMALLINT NOT NULL
);
 
CREATE TABLE voiture (
	id BIGSERIAL PRIMARY KEY,
	vehicule_id BIGINT NOT NULL REFERENCES vehicule (id),
	nbCV SMALLINT NOT NULL,
	nbPortes SMALLINT NOT NULL
);
 
CREATE TABLE moto (
	id BIGSERIAL PRIMARY KEY,
	vehicule_id BIGINT NOT NULL REFERENCES vehicule (id),
	cm3 SMALLINT NOT NULL
);

- pas de notion d'héritage dans le modèle mais utilisation d'une vue (à voir s'il faut la matérialiser ou pas, sachant que la fréquence d'insert sera relativement rare et sur un nombre faibles d'enregistrement...)

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
31
32
33
34
CREATE TYPE type_vehicule AS ENUM ('voiture', 'moto');
 
CREATE TABLE voiture (
	id BIGSERIAL PRIMARY KEY,
	nom CHARACTER VARYING(255) NOT NULL,
	constructeur CHARACTER VARYING(255) NOT NULL,
	nbPlaces SMALLINT NOT NULL,
	nbCV SMALLINT NOT NULL,
	nbPortes SMALLINT NOT NULL
);
 
CREATE TABLE moto (
	id BIGSERIAL PRIMARY KEY,
	nom CHARACTER VARYING(255) NOT NULL,
	constructeur CHARACTER VARYING(255) NOT NULL,
	nbPlaces SMALLINT NOT NULL,
	cm3 SMALLINT NOT NULL
);
 
CREATE VIEW vehicule (id, type, nom) AS
	SELECT
		v.id,
		'voiture',
		v.nom
	FROM
		voiture v
	UNION
	SELECT
		m.id,
		'moto',
		m.nom
	FROM
		moto m
;

Merci d'avance pour vos avis et s'il y a des précisions à apporter n'hésitez pas à demander !

[Edit]
Je rebondis sur une partie de votre réponse concernant la lecture.
Il me semblait que les performances de lecture étaient identiques sur un table avec 2 champs ou avec 20 champs à partir du moment où on fait le select sur les 2 champs ?
Pour prendre un exemple, imaginons 2 cas :
- dans le premier ma table véhicule ne contient que 2 champs
- dans le deuxième ma table véhicule contient disons 50 champs
Le temps d'exécution de cette query va vraiment varier dans les 2 cas sur 1M d'enreg ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT id, nom FROM vehicule

Merci

[SQLpro]

2 choses à dire :

1) modélisation
On ne modélise pas une base pour un SGBDR particulier.
On modélise pour des besoins d'information. Pensez tout de suite au SGBDR Cible est une aberration. On ne modélise pas pour un SGBDR particulier.... C'est pourquoi on fait unMCD... or ce que vous faites ce sont des tables.... Rien à voir avec un modèle de données !
Commencez votre exploitation sur PG est une bonne choses par ce que c'est gratuit. Néanmoins vous pouvez avoir besoin de passer à un autre SGBDR si vous connaissez le succès. Certes les limites de PG sont assez loin, mais réelle...
Le savoir faire d'Oracle ou SQL Server en matière d'optimisation est à quelques années lumière de ce que fait PG aujourd'hui... Par exemple PG commence à se vautrer à partir de 12 jointures... Il ne dispose pas d'un optimiseur sémantique... etc; tant que votre volume reste modeste, et que vous avez pas trop d’utilisateurs, cela ne posera pas de problème.... Mais si vous visez fnac.com ou cdiscount... alors là vous serez piégé... par exemple leboncoin qui travaille sous PG est obligé d’arrêter ses serveurs PG (ou plusieurs car PG a du mal à absorber une volumétrie de plusieurs TO...) la nuit pour maintenance, alors que cela est plus que rare pour SQL Server ou oracle qui absorbe des dizaines de To sans broncher...
Sans parler des bugs... par exemple sous PG le nombre de bugs déclaré est de l'ordre de 1000 par an (907 exactement pour 2013). Tandis que sous MS SQL Server qui comporte à peu près 20 fois plus de code (BI en sus de l'OLTP, outils de diagnostics et outils clients à foison..) on compte à peu près 700 bugs pas an, soit un ratio de 25...
Un petit exemple est le bug que j'ai signalé dans PG et qui n'est pas une priorité pour le staff de PG... À lire : http://www.postgresql.org/message-id...postgresql.org

2) Tous les types non relationnels souffrent des mêmes problématiques congénitales : les performances...
il en est ainsi des types ENUM, ARRAY, ROW... etc.
En effet il n'existe aucune méthode conne pour les indexer contreventement. Or une base suppose du volume. Il faut donc revenir à l’essentiel. LE MCD !
Vous vous êtes précipiter sur le MPD. C'est l'erreur à ne pas faire.
C'est comme si un architecte vous proposait d'ériger une maison sans fondations... Vous signez ?

Il me semblait que les performances de lecture étaient identiques sur un table avec 2 champs ou avec 20 champs à partir du moment où on fait le select sur les 2 champs ?

Pas du tout... Les données des tables sont lues par pages. Les pages contiennent des lignes; Si vous avez une table T1 de 50 colonnes et une autre T2 de 5 colonnes et que votre requête ne porte que sur les 5 colonnes communes, alors T2 ira juste 10 fois plus vite à lire !
Révisez votre savoir sur l'architecture des SGBDR !


A +

[BigFoot69]

Merci pour votre réponse... même si j'en prends pour mon grade

Pour ce problème particulier je pensais avoir déjà en tête un MCD clair que j'essaie justement de transposer en MPD optimisé pour PG.
Dans mon MCD, il y a forcément cette notion d'héritage avec Véhicule et ses filles Voiture, Moto, Bateau.

Ma question est en fait de comment transposer cette notion d'héritage techniquement sous PG pour optimiser les performances.
Cela dit, j'ai peut être réellement loupé qqchose au niveau du MCD !
Je n'ai rien de formalisé pour le MCD car le modèle me semble relativement simple, quelques entités, des relations simples, et cette notion d'héritage (désolé si je n'utilise pas le bon wording, pas taper ).

J'ai pris bonne note du problème d'indexation causé dans mon cas par le type ENUM, auquel je devrais sans douter préférer une table Type_vehicule avec id et utilisation de cette id en FK de ma table Vehicule (mince me revoilà en mode MPD ).
J'ai pris également bonne note du choix du SGBDR, je me suis dirigé vers PG car c'est pour moi le plus performant du gratuit !
Il est évident qu'en cas de gros succès une migration vers un autre SGBDR sera nécessaire mais je n'en suis pas encore là (dommage d'ailleurs) !
D'ailleurs le INHERITS de PG est également présent chez Oracle a priori (mais comme l'avez souligné, probablement mieux géré que sous PG).

Pas du tout... Les données des tables sont lues par pages. Les pages contiennent des lignes; Si vous avez une table T1 de 50 colonnes et une autre T2 de 5 colonnes et que votre requête ne porte que sur les 5 colonnes communes, alors T2 ira juste 10 fois plus vite à lire !
Révisez votre savoir sur l'architecture des SGBDR !

Pour réviser encore faut-il connaître ! Je vais passer par un apprentissage, plutôt qu'une révision, des principes de base des SGBDR et de leur mode de fonctionnement.
D'ailleurs si vous avez une référence web à me conseiller pour une meilleure compréhension du fonctionnement interne des SGBDR je suis preneur !
Je m'étais laissé dire qu'une des opérations lors de l'exécution d'une query était que le SGBDR filtrait les colonnes sur lesquelles travailler (PK, FK, et colonnes récupérées ou utilisées dans les différentes clauses SELECT, WHERE, ORDER BY etc) pour ne pas avoir justement à travailler sur 50 colonnes mais plus probablement sur les colonnes dont il est question mais visiblement j'aurai dû vérifier et ne pas croire sur paroles !

Ceci étant dit, oserais-je vous redemander votre avis sur les 3 façons d'implémenter l'héritage sous PG ?
J'ai tendance à me diriger naturellement vers le INHERITS car :
- vis à vis de l'héritage bricolé géré manuellement, j'imagine que la feature doit être plus efficace car native.
- vis à vis de ne pas intégrer l'héritage, ma table Vehicule contiendra du coup moins de colonnes et les temps de lecture ne devraient s'en trouver que meilleur en me basant sur votre remarque précédente.

J'ai bon ou me renvoyez-vous à mes chères études ?
Blague à part je suis preneur de tout conseil.

[aieeeuuuuu]

Je m'étais laissé dire qu'une des opérations lors de l'exécution d'une query était que le SGBDR filtrait les colonnes sur lesquelles travailler (PK, FK, et colonnes récupérées ou utilisées dans les différentes clauses SELECT, WHERE, ORDER BY etc) pour ne pas avoir justement à travailler sur 50 colonnes mais plus probablement sur les colonnes dont il est question mais visiblement j'aurai dû vérifier et ne pas croire sur paroles !

Ce n'est pas complètement faux non plus...
Pour reprendre votre avec une table de 5 colonne versus un table de 50 colonnes :

Votre requête SELECT id, nom FROM vehicule sera en effet plus rapide si la table n'a que 5 colonnes. Mais si la table de 50 colonnes possède un index sur (id, nom), alors celui-ci "couvrira" la requête : la table ne sera pas utilisée, seul l'index le sera. Comme la table ne sera pas lue, les 50 colonnes ne pénaliseront pas cette requête précise.

[BigFoot69]

Votre requête SELECT id, nom FROM vehicule sera en effet plus rapide si la table n'a que 5 colonnes. Mais si la table de 50 colonnes possède un index sur (id, nom), alors celui-ci "couvrira" la requête : la table ne sera pas utilisée, seul l'index le sera. Comme la table ne sera pas lue, les 50 colonnes ne pénaliseront pas cette requête précise.

Tout s'explique alors !
Un dernier point de détail à ce sujet :
Si je prends la requête SELECT id, nom, type FROM vehicule WHERE nom ILIKE '%un_nom%', que j'ai un index sur id et nom (pas sur type) et que ma table a 50 colonnes.
Dans un premier temps l'index sera utilisé pour récupérer la liste des lignes correspondant à ma clause WHERE, et si j'ai 3 match, à ce moment là seulement les 3 lignes complètes seront récupérées puis "filtrées" pour ne conserver que les 3 colonnes du SELECT sur les 50 colonnes existantes ?

Si jamais vous avez un avis sur mon petit soucis de départ je suis preneur également

[SQLpro]

Pour ce problème particulier je pensais avoir déjà en tête un MCD clair que j'essaie justement de transposer en MPD optimisé pour PG.

Optimiser suppose de connaître vos problèmes de performances. À ce stade, vous n'en avez aucune, puisque le système n'est pas en exploitation. Le meilleur moyen de ne jamais en avoir est de respecter à la lettre l'art de la modélisation, car il se trouve que les SGBD Relationnel on été spécialement conçu pour gérer... des relations !

Dans mon MCD, il y a forcément cette notion d'héritage avec Véhicule et ses filles Voiture, Moto, Bateau.

peut être même plus : Véhicule -> terrestre, aériens, marins... avant même voiture et moto et d'un autre côté bateau...

Ma question est en fait de comment transposer cette notion d'héritage techniquement sous PG pour optimiser les performances.

1) charrue avant bœuf...
2) la modélisation d'un héritage au niveau du MCD existe depuis belle lurette !

Cela dit, j'ai peut être réellement loupé qqchose au niveau du MCD !

Lisez l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/m...tion/heritage/

Je n'ai rien de formalisé pour le MCD car le modèle me semble relativement simple, quelques entités, des relations simples, et cette notion d'héritage (désolé si je n'utilise pas le bon wording, pas taper ).

VOCABULAIRE !
Même simple, il est extrêmement facile de passer d'un MCD à un MPD Oracle, SQL Server, MySQL ou PG. Il est beacoup plus difficile de le faire directement sur le MPD, chaque SGBDR ayant ses particuarités !

J'ai pris bonne note du problème d'indexation causé dans mon cas par le type ENUM, auquel je devrais sans douter préférer une table Type_vehicule avec id et utilisation de cette id en FK de ma table Vehicule (mince me revoilà en mode MPD ).

La propension naturelle des développeurs à reverser dans un univers itératif (tableau, types énumérés, objets... et j'en passe) est inversement proporteionnelle à la simplicité des requêtes et à l'obtention des performances... EN fait les bases de données relationnelles ont été conçues pour gérer des relations (encore !!!) d eet dans ces relations on doit trouver trois choses fondamentales :
1) des données atomiques (dont JAMAIS de types ENUM, ARRAY et toutes ces crétineries)..
2) une clef
3) pas de NULL

J'ai pris également bonne note du choix du SGBDR, je me suis dirigé vers PG car c'est pour moi le plus performant du gratuit !

À ce stade le choix du SGBDR n'a aucun intérêt. Vous avez raison sur PG qui reste un bon SGBDR dans le libre, mais quand même avec certaines limites...

Il est évident qu'en cas de gros succès une migration vers un autre SGBDR sera nécessaire mais je n'en suis pas encore là (dommage d'ailleurs) !
D'ailleurs le INHERITS de PG est également présent chez Oracle a priori (mais comme l'avez souligné, probablement mieux géré que sous PG).

D'abord, c'est pas standard... et sauf que vous allez surement tousser quand vous allez découvrir le prix des licences Oracle... Aujourd'hui la plupart des entreprises passent sous SQL Server au détriment d'Oracle et cela est encore plus vrai avec SQL Server 2014 ou MS dépasse maintenant largement les performances d'Oracle, seul élément sur lequel il était à la traine...

Pour réviser encore faut-il connaître ! Je vais passer par un apprentissage, plutôt qu'une révision, des principes de base des SGBDR et de leur mode de fonctionnement.

Vous avez passé votre scolarité au flipper ? ;-)

D'ailleurs si vous avez une référence web à me conseiller pour une meilleure compréhension du fonctionnement interne des SGBDR je suis preneur !

Sur le web, y'a pas grand chose de terrible. C'est souvent farci de conneries. je me souvient il y a quelques années, donnant un cours sur la modélisation à l'ISEN, quelqu’un me pose une question sur une forme normale particulière... Je me propose alors de lui montrer par un exemple et vais sur wikipédia... J'étais sûr de mon coup, car j'avais en grande partie écrit l'article sur les formes normales.. Et là, patatras, je ne trouve plus cette forme normale particulière... le soir chez moi, je me demande de ce qui s'est passé... Je remonte l'historique et trouve les coordonnées du suppresseur. Je lui écrit, pour demander pourquoi il avait effacer cela et sa réponse a été :
"je n'ai rien trouvé d'autre sur le sujet et comme j'ai pas compris, j'ai supprimé" !

De plus vous remarquerez que les articles qui remontent en premier dans le ranking google, sont des articles très populaires. Or la popularité est généralement inversement proportionnelle à l'intérêt intellectuel et technique d'un contenu... Il est évidemment plus facile de comprendre Marine Le Pen, que Kant, Hegel ou marx !

Autrement dit, les meilleurs choses sont hélas, encore et toujours dans les livres :
Chris Date sur les SGBDR : http://www.amazon.fr/Introduction-ba...rds=chris+date
CONNOLY : http://www.amazon.fr/Syst%C3%A8mes-b...e+donn%C3%A9es
Au pire il y a le gardarin...

Je m'étais laissé dire qu'une des opérations lors de l'exécution d'une query était que le SGBDR filtrait les colonnes sur lesquelles travailler (PK, FK, et colonnes récupérées ou utilisées dans les différentes clauses SELECT, WHERE, ORDER BY etc) pour ne pas avoir justement à travailler sur 50 colonnes mais plus probablement sur les colonnes dont il est question mais visiblement j'aurai dû vérifier et ne pas croire sur paroles !

Les SGBDR travaillent exclusivement en mémoire... Les tables et les index sont stockées dans des pages (8 ko pour PG et SQL Server). L'unité minimale de lecture logique (en mémoire) est donc la page. Une page est constituée d'au moins une ligne. Plus il y a de lignes dans une page et plus je vais lire rapidement les données. Le fait de ne sélectionner que quelques colonnes d'une table, n'a aucune incident sur la métrique de lecture en mémoire, mais en as sur les opérations en aval, comme pas exemple un DISTINCT, un GROUP BY ou un tri... or le coût le plus important d'une commande SQL est généralement lié à la lecture primale !

Ceci étant dit, oserais-je vous redemander votre avis sur les 3 façons d'implémenter l'héritage sous PG ?

oui

J'ai tendance à me diriger naturellement vers le INHERITS car :
- vis à vis de l'héritage bricolé géré manuellement, j'imagine que la feature doit être plus efficace car native.
- vis à vis de ne pas intégrer l'héritage, ma table Vehicule contiendra du coup moins de colonnes et les temps de lecture ne devraient s'en trouver que meilleur en me basant sur votre remarque précédente.

Faites simple, respectez le modèle conceptuel. De plus ce qui n'est pas standard à tendance à changer. Ce n'est généralement pas le cas de qui est normalisé !

A +

[aieeeuuuuu]

Si je prends la requête SELECT id, nom, type FROM vehicule WHERE nom ILIKE '%un_nom%', que j'ai un index sur id et nom (pas sur type) et que ma table a 50 colonnes.
Dans un premier temps l'index sera utilisé pour récupérer la liste des lignes correspondant à ma clause WHERE, et si j'ai 3 match, à ce moment là seulement les 3 lignes complètes seront récupérées puis "filtrées" pour ne conserver que les 3 colonnes du SELECT sur les 50 colonnes existantes ?

Dans ce cas, le comportement de l'optimiseur est plus délicat à prédire :

L'index pourra en effet permettre de traiter un volume moindre de données pour identifier les lignes qui vont correspondre à la clause WHERE. En revanche, comme vous l'avez précisé, il y aura ensuite un "surcoût" pour récupérer la valeur de la colonne type pour chaque ligne "trouvée dans l'index". Passé un certains nombre de lignes, cela sera contre performant, et l'optimiseur préférera se passer de l'index. surtout qu'en l’occurrence, avec un LIKE '%...%' qui empêche une recherche dans l'index, c'est une analyse complète de l'index qui sera faite...

Concrètement, l’optimiseur va donc évaluer le nombre de lignes qui vont correspondre au filtre, et choisir l'une des deux solutions (utilisation de l'index ou non) en fonction de ce nombre. Il va calculer ce nombre en fonction de plusieurs critères, dont les statistiques dont il dispose sur la colonne nom, mais également la contenu de votre LIKE...
De plus, le "nombre seuil" à partir duquel l'utilisation de l'index va être préférée à une analyse de la table dépend également de différents critères.

C'est pourquoi, on ne peut faire que des suppositions qui demandent à être vérifiées (et qui pourront varier d'un SGBD à l'autre) :
1/ si la table ne tient qu'en peu de pages (car peu de lignes), l'index ne sera pas utilisé
2/ si le pattern du LIKE est très simple ( LIKE '%a%'), l'index sera utilisé (sauf cas 1). Si en revanche le pattern est complexe (et donc que peu de lignes peuvent matcher), alors l'index sera utilisé
...

[SQLpro]

Tout s'explique alors !
Un dernier point de détail à ce sujet :
Si je prends la requête SELECT id, nom, type FROM vehicule WHERE nom ILIKE '%un_nom%', que j'ai un index sur id et nom (pas sur type) et que ma table a 50 colonnes.
Dans un premier temps l'index sera utilisé pour récupérer la liste des lignes correspondant à ma clause WHERE, et si j'ai 3 match, à ce moment là seulement les 3 lignes complètes seront récupérées puis "filtrées" pour ne conserver que les 3 colonnes du SELECT sur les 50 colonnes existantes ?

Si jamais vous avez un avis sur mon petit soucis de départ je suis preneur également

le problème est plus complexe que cela :

1) plus il y a de colonnes, et plus il faudra d'index pertinent pour chaque recherche. or la combinatoire conduit à une explosion exponentielle du nombre d'index. Dans mon livre à paraître sur MS SQL Server je montre l'abaque suivante :

"
13.7.7.4 - Potentiel d’indexation pour une table

Le nombre d’index potentiel pour une table croit de façon exponentielle avec le nombre de colonnes de la table. Hormis le cas particulier de la clef primaire, une table dotée d’une seule colonne de données, n’a pas besoin de plus d’un index. Une table avec 2 colonnes aura besoin de 4 index...
Soit la table T dotée des colonnes a et b, la liste des index potentiel est :
• a, b
• ab, ba
N’oublions pas que dans un index les informations sont vectorisées, dans le sens ou l’ordre des éléments composant l’index, revêt une importance capitale , bien qu’ils conduisent in fine au même point... Autrement dit, les index ab et ba peuvent être utilisés différemment et sont potentiellement aussi utiles l’un que l’autre !
Avec 3 colonnes dans la table T (a, b, c), les combinaisons sont les suivantes :
• a, b, c
• ab, ac, ba, bc, ca, cb
• abc, acb, bac, bca, cab, cba
Soit 15 index potentiels, sans compter les possibilités du tri ascendant ou descendant (qui multiple de façon quadatique le nombre des possibilités déjà calculées), ni la clause INCLUDE des index couvrants (un calcul de combinaisons) et porteraient ainsi le nombre des possibilités à 16 pour deux colonnes et 120 pour 3... Et nous avons encore ignoré la clause WHERE !

Voici un tableau résumant le nombre potentiel d’index pour une table dotée de n colonnes, sans tenir compte de l’ordre de tri des colonnes ni de la clause INCLUDE :

nombre de colonne nombre d’index potentiel
1 1
2 4
3 15
4 64
5 325
6 1 956
7 13 699
8 109 600
9 986 409
10 9 864 100
11 108 505 111
12 1 302 061 344
13 16 926 797 485
14 236 975 164 804
15 3 554 627 472 075
16 56 874 039 553 216
17 966 858 672 404 689
18 17 403 456 103 284 420
19 330 665 665 962 403 999
20 6 613 313 319 248 080 000
Tableau 13.16 - Nombre potentiel d’index en fonction du nombre de colonnes d’une table

La formule étant la suivante (Figure 13.52) :

Figure 13.52 : nombre potentiel d’index dans une table dotée de n colonnes (A : arrangements)

Comme on le voit, plus la table comporte de colonne et plus le nombre d’index potentiel devient gigantesque...

2) votre requête n'est pas pertinente. Un LIKE '%... n'est par "sargeable" l'index ne sera pas utilisé en recherche et peut être même pas du tout...
Sur la notion de sargeabilité : http://blog.developpez.com/sqlpro/p1...sql_sargable_c

A +

[SQLpro]

1/ si la table ne tient qu'en peu de pages (car peu de lignes), l'index ne sera pas utilisé

Rien n'est sûr... Si l'index est couvrant et que la table est bloquée alors la lecture de l'index sera privilégié à l'nstant T !

2/ si le pattern du LIKE est très simple ( LIKE '%a%'), l'index sera utilisé (sauf cas 1).

Rien n'est sûr (2) ! Sauf si l'index est bloqué et qu'un autre index candidat ou la table est libre...

Si en revanche le pattern est complexe (et donc que peu de lignes peuvent matcher), alors l'index sera utilisé...

Rien n'est sûr (3) ! Toujours pour des raisons de concurrence !

A +

[tatayo]

Bonjour,
Je me permets de m'insérer dans la discussion:

Le nombre d’index potentiel pour une table croit de façon exponentielle avec le nombre de colonnes de la table. Hormis le cas particulier de la clef primaire, une table dotée d’une seule colonne de données, n’a pas besoin de plus d’un index. Une table avec 2 colonnes aura besoin de 4 index...
Soit la table T dotée des colonnes a et b, la liste des index potentiel est :
• a, b
• ab, ba
N’oublions pas que dans un index les informations sont vectorisées, dans le sens ou l’ordre des éléments composant l’index, revêt une importance capitale , bien qu’ils conduisent in fine au même point... Autrement dit, les index ab et ba peuvent être utilisés différemment et sont potentiellement aussi utiles l’un que l’autre !
Avec 3 colonnes dans la table T (a, b, c), les combinaisons sont les suivantes :
• a, b, c
• ab, ac, ba, bc, ca, cb
• abc, acb, bac, bca, cab, cba
Soit 15 index potentiels, sans compter les possibilités du tri ascendant ou descendant (qui multiple de façon quadatique le nombre des possibilités déjà calculées), ni la clause INCLUDE des index couvrants (un calcul de combinaisons) et porteraient ainsi le nombre des possibilités à 16 pour deux colonnes et 120 pour 3... Et nous avons encore ignoré la clause WHERE !

Dans le cas des indexes a,ab,abc, qu'est-ce qui est le plus pertinent:

1. Créer seulement le dernier, qui couvre les deux autres
2. Créer les 3
3. Ca dépend (de quoi ?)

De mon côté j'ai tendance à opter pour la première solution, mais l'avis des experts est toujours bon à prendre

Tatayo.

[SQLpro]

Ce sont en effet, ce sue l'on appelle des "index inclus" j'ai d'ailleurs donné une requête pour les débusquer... http://blog.developpez.com/sqlpro/p9...s_index_anorma
Il n'est pas possible d'y répondre sans connaître les requêtes effectuées et la concurrence, ainsi que le taux de mise à jour des différentes colonnes des tables.
Par exemple en ne créant que l'index abc, une requête n'ayant besoin que de a devra patienter si l'index est occupé pour une mise à jour de la colonne b....

La problématique de bonne indexation est loin d'être simple... Dans mon livre sur SQL Server j'en parle dans un chapitre particulier qui fait près de 60 pages...

A +

[SQLpro]

Un dernier conseil : faire le choix du SGBDR après :
1) avoir réalisé le modèle de données
2) après avoir établis tous les besoins fonctionnels

En effet, tous les SGBDR ne disposent pas des mêmes outils pour répondre à vos besoins... Certains n'ont même pas les outils que vous pourriez avoir besoin.
Par exemple, vous pourriez avoir besoin de l'indexation textuelle, du spatial (pour la géolocalisation par exemple), de XML (pour l'échange de données), de BI... mais aussi d'outils de tuning, de diagnostic, de sécurité, de haute disponibilité...
Ce sera alors le moment de faire le choix... Sinon cela risque de vous couter beaucoup plus cher à l'arrivée !

A +

[aieeeuuuuu]

Rien n'est sûr... Si l'index est couvrant et que la table est bloquée alors la lecture de l'index sera privilégié à l'nstant T !

Oui, c'est pourquoi j'avais bien mis en gras qu'il s'agissait de supposition... Par ailleurs, je parlais bien du cas d'index non couvrant.
Cependant, Peut-tu préciser ta remarque : est-ce que les plans d'éxécution tiennent compte des tables/index vérouillés ???

Rien n'est sûr (2) ! Sauf si l'index est bloqué et qu'un autre index candidat ou la table est libre...

Oui, c'est pourquoi j'avais bien mis en gras qu'il s'agissait de supposition(2)

Rien n'est sûr (3) ! Toujours pour des raisons de concurrence !

Oui, c'est pourquoi j'avais bien mis en gras qu'il s'agissait de supposition(3)

[aieeeuuuuu]

Par exemple en ne créant que l'index abc, une requête n'ayant besoin que de a devra patienter si l'index est occupé pour une mise à jour de la colonne b....

En plus, on pourrait se retrouver dans la même situation que la table de 50 colonnes : si les colonnes b et c sont de taille importante, il faudra lire plus de page pour parcourir l'index...

[SQLpro]

Cependant, Peut-tu préciser ta remarque : est-ce que les plans d'éxécution tiennent compte des tables/index vérouillés ???

oui... Dans certains cas de lecture si plusieurs index sont candidats et que le meilleur est verrouillé, SQL Server cherche un autre index à lire... Exemple :

Une table de 6 colonnes avec 9000 lignes

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
CREATE TABLE T (K     INT NOT NULL PRIMARY KEY,
                C1    INT,
                C2    INT,
                C3    INT,
                C4    INT,
                C5    INT)
GO
 
DECLARE @I INT
SET  @I = 1000
WHILE @I < 10000
BEGIN
   INSERT INTO T VALUES (@I, 1, 2, 3, 4, 5)
   SET @I = @I + 1;
END;
GO

Un index sur les colonnes 2 et 1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE INDEX X21 ON T (C2, C1);
GO

Une transaction bloquante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
--> fenetre 1, requête 1
BEGIN TRANSACTION
UPDATE T SET C1 = 0

Une lecture :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
--> fenêtre 2, requête 2
SELECT C2    
FROM   T
OPTION (RECOMPILE)
--> pas bloquée... lecture de l'index de PK
--> voir avec plan

Déblocage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
--> retour fenêtre 1
ROLLBACK;

Relance de la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
--> retour fenêtre 2
SELECT C2    
FROM   T
OPTION (RECOMPILE)
--> pas bloquée... lecture de l'index de X21
--> voir avec plan

Autrement dit SQL Server a choisit le plus mauvais index pour la première lecture du fait du blocage de cet index par la mise à jour.

Dans un second temps, après libération de la transaction, SQL Server change de tactique et choit le meilleur index...

A +

[aieeeuuuuu]

Merci !

Mais... sur quelle édition/version cela est-il valable ?

Sur un SQL server 2008 R2 sp1 édition entreprise, la requête 2 reste bloquée..

Cependant, en l’occurrence, même l'index PK (cluster) est verrouillé.

J'ai donc laissé à SQL Server une chance au grattage en :

1/ créant un index non cluster, sans la colonne C1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
CREATE INDEX X2345 on T(C2,C3,C4,C5)

2/ diminuant le verrouillage de la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
BEGIN TRANSACTION;
UPDATE T SET C1 = 10
WHERE K = 9000;

3/ relancé la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT C2   
FROM   T 
OPTION (RECOMPILE)
--> bloquée !

Pourtant, l'index X2345 est bien disponible :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT C2   
FROM   T WITH(INDEX (X2345))
--> non bloquée