Discussion :

[Invité]

Bonjour

J importe 235240 lignes dans un table que je crée à la volée comme suit (delphi +asolute database) et je lance 2 procedures

Code :

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

CREATE TABLE Cours (  id AUTOINCINTEGER,  date_time VARCHAR(20), open FLOAT,   low FLOAT,  high FLOAT,  middle FLOAT,  cross_up SMALLINT,  cross_down SMALLINT,  sens_bar SMALLINT,  spread FLOAT);

Resultats :
insertion 4m41.125 secondes.
procedure 1 2m43.734
procedure 2 44s985

je me dis fort logiquement qu il foit y avaoir un moynen d ameliorer les perfs donc je me dirige vers les index.
Les requets que je fais au travers de mes diverses procedures sont

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
select * from cours where (cross_up =1 and sens_bar=1) or (cross_down =1 and sens_bar=1)  order by id asc
 
select * from  cours where id>=123456  and low<=123456 order by id asc
select * from  cours where id>=123456  and high=123456 order by id asc
 
select  * from  Cours where id>=123456  and High>=middle order by id asc
select  * from  Cours where id>=123456  and low<=middle order by id asc
 
select spread from cours where id=123456
 
select low,high,middle from cours where id=123456

Donc je m essaie aux index avec

Code :

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

1
2
3
4
5
6
 
create unique index UniqueName1 ON cours (id,low); 
create unique index UniqueName2 ON cours (id,high);
create unique index UniqueName3 ON cours (id,low,high);
create index UniqueName4 ON cours (cross_up,sens_bar); 
create index UniqueName5 ON cours (cross_down,sens_bar);

Fier de moi je teste. Résultats : Insertion 6.45.812 (+2minutes et 2sec env) procedure 1 : 2.48.328 (+5 secondes) et procedure 2 : 49.250 (+5 secondes)

Ma question est donc des plus simples : Quels index creeriez vous afin d'optimiser les perfs de mes requetes requetes ?


Ah une dernière question : le temps d exécution d'une requête de type Select * est il équivalent à celui d une requête Select Nom du Champ ?

Merci de votre aide.

Stéphane

[aieeeuuuuu]

Bonjour,

commençons par la fin, pour mieux comprendre le début.

Un SELECT * en lieu et place de la sélection des seules colonnes nécessaires peut changer le plan d’exécution, et par la même les temps de réponses. Cela peut notamment empecher l'utilisation d'un index.

prenons votre troisième requete :

Code :

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

select * from  cours where id>=123456  and high=123456 order by id asc

Le moteur pourrait utiliser l'index create unique index UniqueName2 ON cours (id,high); afin de trouver plus rapidement les lignes qui répondent au filtre. (en fait, un index sur (high, id) serait sans doute bien plus efficace)

Là ou le SELECT * devient néfaste, c'est qu'il va obliger, pour chaque ligne identifiée grâce à l'index, à aller chercher le contenu des autres colonnes dans la table, ce qui est couteux. De surcroit, si le moteur estime que le nombre de lignes répondant au critére sera important, il décidera de ne plus utiliser l'index, mais de scanner directement la table, afin d'éviter ses nombreuses recherches dans la table.

Si au lieu de cela vous écrivez la requête ainsi :
select id, high from cours where id>=123456 and high=123456 order by id asc, alors l'index à lui seul suffit à répondre à la requête, on parle d'index couvrant.
S'il est besoin d'une colonne supplémentaire, par exemple low, il est possible de l'ajouter à l'index afin qu'il reste couvrant (même si la colonne low ne rentre pas dans les critère de filtre).

De fait, pour poser les bon indexes, il faut d'abord connaitre les requêtes que vous allez exécuter, et en effet, les colonnes sélectionnées ont leur importance.

Enfin, comme vous avez pu le constater, les index pénalisent les insertions en masse. Il peut être préférable de les désactiver avant l'insertion, et de les reconstruire après, surtout si votre table est vide au départ.

[CinePhil]

À lire aussi :
- Sur ce qu'il faut ou pas indexer ;
- Sur les index et mises à jour ou insertions en masse ;
- Sur la maintenance des index dans les grandes bases de données.

[Invité]

Bonjour

Merci d'avoir pris le temps de me répondre.

Comme conseillé j ai crée l index apres avoir rempli les tables et pour 117730 lignes, au lieu de doubler le temps cela n augmente que de 17 seconde :-)

par contre je n arrive pas à comprendre

Le moteur pourrait utiliser l'index create unique index UniqueName2 ON cours (id,high); afin de trouver plus rapidement les lignes qui répondent au filtre. (en fait, un index sur (high, id) serait sans doute bien plus efficace)

C est ce que je pensais avoir fait avec

Code :

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

create unique index UniqueName2 ON cours (id,high);

merci pour vos précisions

[escartefigue]

C est ce que je pensais avoir fait avec

Code :

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

create unique index UniqueName2 ON cours (id,high);

merci pour vos précisions

C'est parcequ'il faut privilégier la colonne la plus filtrante en premier dans l'index
Comme vous filtrez sur une valeur unique (high=valeur) il est préférable que ce critère soit la 1ère colonne de l'index et non la deuxième
Le deuxième critère est un filtre "supérieur à" probablement peu filtrant (tout dépend des stats)

[SQLpro]

...

Code :

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

CREATE TABLE Cours (  id AUTOINCINTEGER,  date_time VARCHAR(20), open FLOAT,   low FLOAT,  high FLOAT,  middle FLOAT,  cross_up SMALLINT,  cross_down SMALLINT,  sens_bar SMALLINT,  spread FLOAT);

Tout d'abord pour optimiser un temps de réponse, utiliser les bons types de données... Par exemple un temporel sous forme de VARCHAR(20) est stupide. Une date_heure doit être de type TIMESTAMP (ou DATETIME) suivant le SGBDR.

Fier de moi je teste. Résultats : Insertion 6.45.812 (+2minutes et 2sec env) procedure 1 : 2.48.328 (+5 secondes) et procedure 2 : 49.250 (+5 secondes)

C'est normal. Les index servent aux recherches. Un INSERT ne recherche pas. Un UPDATE et un DELETE oui. Mais dans tous les cas de mise à jour (INSERT, UPDATE, DELETE) il faut mettre à jour aussi l'index. Donc, durée plus longue.

Ah une dernière question : le temps d exécution d'une requête de type Select * est il équivalent à celui d une requête Select Nom du Champ ?

Pas du tout. le SELECT * est un imbécilité et ne devrais JAMAIS être utilisé en production. Il ne faut mettre dans la clause SELECT que les colonnes (ce ne sont pas champs, les champs c'est pour les pasyans...) utiles au résultat. Avec cela le moteur se comportera différemment et les performances seront beaucoup plus efficaces...

Ma question est donc des plus simples : Quels index creeriez vous afin d'optimiser les perfs de mes requetes requetes ?

Ceux correspondant aux requêtes les plus fréquentes.

Lisez les articles que j'ai écrit à ce sujet, ou mon dernier livre sur SQL ou un chapitre entier de 50 pages est consacré aux index.

A +

[escartefigue]

Pas du tout. le SELECT * est un imbécilité et ne devrais JAMAIS être utilisé en production.

Et c'est d'ailleurs souvent interdit, et contrôlé par les outils de mise en production

[Invité]

Merci pour ces indications.

Effectivement mon date_time est n est pas du bon type. J y ai remédié. (Cela était du au fait que j utilisais sqlite avec delphi et que je n avais pas ce type. J ai convertit.)

Concernant le select *, j y ai remédie aussi. (c teiat simplement par... feneantise )

J ai commencé à lire . Mon objectif est de n'avoir que des index 3 étoiles ...

merci