Utiliser un index avec des valeurs partielles

[camboui]

Supposons une table qui contienne un champ NAME et un index sur ce champ.

Code :

CREATE INDEX my_index ON my_table (NAME)

Une requête comme celle qui suit va utiliser l'index:

Code :

SELECT * FROM my_table WHERE NAME="Alexandre"

Maintenant j'aimerais faire des requêtes sur valeurs partielles (des préfixes pour être précis) mais je souhaite que l'index soit aussi utilisé, comme ceci par exemple,:

Code :

SELECT * FROM my_table WHERE NAME="Alex%"

En retour j'obtiendrais tous les Alex, Alexandre, Alexandra, etc.

Techniquement parlant, rien ne l'empêche. En C il suffirait d'utiliser la fonction strncmp() au lieu de strcmp() pour faire des comparaisons (et donc des recherches) sur valeur partielle.

Est-ce possible de forcer SQL d'en faire autant ?
Merci.

[mikedavem]

Pas de besoin de forcer SQL en réalité.
L'optimiseur pourra utiliser l'index dans ce cas (mais dans votre requête il faut utiliser l'opération LIKE) :

Exemple

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE name
(
 nom varchar(50)
)
 
INSERT name VALUES ('TOTO')
GO 100000
 
INSERT name VALUES ('alex')
INSERT name VALUES ('alexandre')
INSERT name VALUES ('alexandra')
 
CREATE CLUSTERED INDEX PK_name
ON dbo.name ( nom )
 
SELECT * FROM name WHERE nom LIKE 'alex%'

++

[camboui]

C'est bon à savoir que même avec LIKE les index peuvent être utilisés !
D'habitude je vérifie mes requêtes avec le "execution plan" (Ctrl-M dans le menu "Query", désolé j'ai des softs en anglais). Est-ce suffisant pour vérifier dans ce cas ?

Merci !

[mikedavem]

Vous pouvez le vérifier avec le plan d'exécution réel également ou encore en utilisant les options SET STATISTICS IO pour voir le nombre de pages lues pour votre recherche ou scan d'index selon le cas

++

[SQLpro]

ATTENTION : votre jeu d'essais n'étant pas consistant il est probable que votre index ne soit pas utilisé et qu'il en résulte un SCAN de table !

En effet, pour faire des essais d'utilisation d'index il faut un volume de données et une distribution des données proche de la réalité, c'est à dire au moins quelques dizaines de milliers de lignes au minimum !

A +

[camboui]

OK m3rci mikedaven

J'ai d'autres questions à propos d'index qui sont un peu en marge du sujet initial, mais je les pose ici afin d'éviter d'ouvrir un nouveau sujet.

Quelle différence entre une "table" et un "index" du point de vue de l'écriture sur disque ?
En fait je voudrais constater qu'il n'y en a pratiquement aucune. Exemple.

Code :

1
2
3
4
5
6
7
8
9
CREATE TABLE my_table
(
 id INT,
 name VARCHAR(50)
)
 
CREATE CLUSTERED INDEX idx1_id ON my_table (id)
 
CREATE NON CLUSTERED INDEX idx2_name ON my_table (name) INCLUDE (id)

Si j'ai bien compris, il y a 2 B+tree qui seront créés sur disque et qui contiendront les mêmes données à peu de choses près. Toutes les paires (id, name) seront stockées en double, une fois ordonnées selon 'id' et l'autre selon 'name'. A partir du moment où un index est aussi stocké comme une table, pourquoi ne pourrions nous pas faire une requête de ce genre afin de voir le contenu d'un index ?

Code :

SELECT * FROM idx2_name

[camboui]

SQLpro,
Je ne donnais qu'un exemple pour illustrer mes propos sur le forum.

Mais je n'ai pas compris ton intervention. Les index ne sont utilisés que s'il y de gros volumes de données, sinon jamais ?

[Sergejack]

Envoyé par camboui

Code :

Normalement dans ce scénario, id serait une clé primaire.
Comme ça ne l'est pas j'ai un léger doute mais je pense qu'il est inutile d'inclure (clause INCLUDE) id dans le second index.
Car celui-ci serait d'office inclu (puisque c'est LUI qui permet de retrouver physiquement la ligne sur le disque).

Concernant votre question, si vous n'employez (sélection et filtre) d'une table des colonnes qui appartiennent toutes à un index, sql server va lire uniquement l'index.
Il est donc inutile (et encombrant) d'avoir une syntax SELECT ... FROM INDEX...

[camboui]

Ok merci.

J'essaie de comprendre comment sont agencées les données sur disque, mais pour cela il faudrait des outils de plus bas niveau qui ne sont pas disponibles apparemment.

Pour peu qu'on conçoive une DB de manière simpliste, il y a risque d'avoir beaucoup de redondance sur disque, SQL Server n'effectuant aucune optimisation particulière.

Exemple

Code :

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE my_table
(
 id INT,
 name VARCHAR(50),
 gender VARCHAR(16)
)
 
CREATE CLUSTERED INDEX idx1_id ON my_table (id)
 
CREATE NON CLUSTERED INDEX idx2_name ON my_table (name) INCLUDE (id) 
 
CREATE NON CLUSTERED INDEX idx3_gender ON my_table (gender)

Supposons que le champ 'gender' ne prennent que 2 valeurs, 'masculin' et 'femimin'.
L'index idx3_gender contient autant de fois les mots entiers 'masculin' et 'femimin' qu'il y a d'enregistrements correspondants dans my_table. C'est une belle perte de place.
'masculin' et 'femimin' pourraient n'être stocké qu'une fois chacun avec la liste des numéros d'enregistrement correspondant.

[Jinroh77]

A confirmer, mais je ne suis pas sûr que l'idx3 contiennent autant de fois les valeurs qu'il y a de lignes dans la table.
Il contient plutôt les 2 valeurs puis des pointeurs qui mènent vers les pages de données de la tables pour les différentes valeurs.

[Sergejack]

Envoyé par camboui

Supposons que le champ 'gender' ne prennent que 2 valeurs, 'masculin' et 'femimin'.
L'index idx3_gender contient autant de fois les mots entiers 'masculin' et 'femimin' qu'il y a d'enregistrements correspondants dans my_table. C'est une belle perte de place.
'masculin' et 'femimin' pourraient n'être stocké qu'une fois chacun avec la liste des numéros d'enregistrement correspondant.

La perte de place viendrait alors d'un mauvais design de ta base.
Et tu perdrais plus qu'un peu de place au niveau des indexes.

Tu dois normaliser ta base de donnée.
Tant que tu ne connaitras pas la normalisation (une base nécessaire), il sera incongru que tu t' interroge sur d'autres optimisations.

[camboui]

Merci pour ces bons conseils... Dois-je normaliser un éventuel champ 'prénom' sachant que 20% des nouveaux nés s'appellent Mohamed
Plus sérieusement, je ne suis pas DBA. Je fais du traitement de données reçues de tiers. MS-SQL est utilisé comme outil pour ces traitements.

Mon propos ici est de comprendre comment MS-SQL fonctionne. Il consomme une énorme quantité d'espace disque, et pourtant je trouve qu'il est très performant; on obtient des temps de réponse souvent courts sur des requêtes complexes traitant des volumes importants.
Ces réponses sont d'autaut plus étonnantes si MS-SQL se contente d'organiser et dupliquer les données sous forme de B+tree sans autre forme d'optimisation particulière.

Tout ceci a une finalité.
On fait nos traitements de données avec un langage procédural. Cela permet pratiquement toutes les fantaisies voulues avec des performances inégalables, jusqu'à certaines limites. En effet, lorsque le volume de données est trop important on est contraint d'effectuer les traitements sur disque au lieu de les faire en mémoire. Du coup les performances chutent au point qu'un traitement équivalent exécuté en SQL en arrive à être plus performant. On se demande donc ce qui est "mal fait" en langage procédural afin de corriger pour au moins égaler SQL.
Deux corrections possibles:

• L'organisation des données sur disque de nos programmes est plus simple que le B+tree (donc sans doute moins performant algorithmiquement);
• on fait "confiance" au système d'exploitation pour sa gestion de cache sur disque.

MS-SQL utilise des B+tree et possède sa propre gestion de cache mémoire. Il "triche" même, là où notre programme dans sa phase "moins performante" n'utilise que peu de mémoire (puisque les données sont sur disque) MS-SQL "bouffe" des gigas si ça lui chante et qu'on l'y autorise.

Voilà donc, je cherche à récupérer les bonnes recettes de MS-SQL et pour ça je veux savoir ce qu'il fait au bas niveau. Je fais des tests en dénormalisant, c'est exprès, ne ne mettant pas d'index là où il faudrait, c'est exprès, ou en mettant des index à outrance avec des INCLUDE inutile, c'est exprès. Et même en faisant ces choses "pas bien" MS-SQL arrive à être performant
Ça peut même être gênant car on en arrive à faire de "mauvais design" en se disant "bah, MS-SQL est performant donc c'est pas grave". Mais c'est un autre débat

[Sergejack]

Quel est l'objectif derrière ta question ?

Le choix d'une technologie ?
Une évaluation de ce qu'occuperait en place une DB après X année ?


Je lis le mot "procédural" dans ta dernière réplique, tu fais bien tes requêtes SQL de manière ensembliste ?


À part ça, je n'ai pas la compétence pour te donner les informations que tu recherches.
Je me sens un peu comme un instituteur d'auto-école aux compétences honnêtes qui se retrouve devant un élève qui demanderait "Combien me faut-il de centilitres d'essence pour que ma voiture XYZ puisse rouler 100KM à une vitesse de 3900KM/h ?".
Je ne suis pas sûr de comprendre la pertinence de la question et le manque de réalisme de celle-ci me laisse pantois.