Discussion :

[kango]

Bonjour à tous,

Je voudrais améliorer le temps de traitement d'un de mes programmes qui utilise une base de données. Je benchmark plusieurs solutions en balançant la "charge" dans la requête SQL ou dans le langage de programmation qui est utilisé. Je teste également avec base de données non relationnelles (netcdf, hdf5). Je connais très bien le langage (Python) et beaucoup moins le SQL (j'utilise sqlite), du coup ma solution SQL est moins performante. J'attribue ceci en grande partie à ma "noobitude" en SQL et je voudrais voir avec vous ce que vous me conseillerez pour optimiser la requête qui fonctionne bien (= donne les bons résultats).

La base de données est très simple. J'ai des données climatiques (températures uniquement pour simplifier) prélevées en différents endroits de la planète (au sol). Et je vais avoir plusieurs mesures dans l'année.

Donc ça donne ceci en terme de tables:

localisations:

Id (integer, auto increment, primary key)	Nom (text)	Latitude (real)	Longitude (real)
1	Trucmuche	20.	45.
...	...	...	...

mesures:

Id (integer, auto increment, primary key)	Localisation (integer, foreign key => localisations)	Date (date)	Temperature (real)
1	1	01/01/2015 - 12:00:00	15.0
...	...	...	...

L'objectif est de réaliser des statistiques sur les relevés de températures (dans une certaine période, dans une certaine zone géographique). Parmi toutes les mesures dont je dispose, il y en a qui m’intéressent tout particulièrement. J'ai une 3ème table pour gérer ces "favoris":

favoris:

Id (integer, auto increment, primary key)	Localisation (integer, foreign key => localisations)
1	12
...	...

Voici la requête que j'ai écrite pour extraire les températures mini et maxi sur toutes les localisations dans mes "favoris":

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
 
SELECT nom, max_temp, min_temp
FROM localisations
JOIN 
    (SELECT mesures.localisation AS localisation, MAX(mesures.temperature) AS max_temp
     FROM mesures
     WHERE mesures.localisation IN (SELECT localisation FROM favoris)
     GROUP BY mesures.localisation) AS max_temps
ON localisations.id = max_temps.localisation
JOIN 
    (SELECT mesures.localisation AS localisation, MIN(mesures.temperature) AS min_temp
     FROM mesures
     WHERE mesures.localisation IN (SELECT localisation FROM favoris)
     GROUP BY mesures.localisation) AS min_temps
ON localisations.id = min_temps.localisation
ORDER BY nom

La requête:

Code :

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

SELECT localisation FROM favoris

est dupliquée. J'imagine que c'est pas très propre. Ensuite les jointures sont "lentes" avec les tables intermédiaires. Avec des tables qui sont dans la base, j'ai bien vu que les index améliorent très significativement la performance mais là je ne sais pas comment je pourrais indexer ni même si ce serait une bonne idée.

Si vous avez des idées ou des conseils, je suis preneur

P.S.: hdf5 (base de données hiérarchique) est performant sur ce genre de cas, il ne me convient pas tout à fait car j'ai besoin de l'aspect "relationnel" pour certaines requêtes (non exposées ici). Avant d'opter pour une solution hydride: une partie des données dans du hiérarchique pour faciliter le traitement statistique et la partie relationnelle dans une base de données relationnelle, je voudrais m'assurer que je n'arrive pas à de mauvaises conclusions du simple fait de mon inexpérience en SQL

Merci

Kango

[tatayo]

Bonjour,
Je ne vois pas trop l'intérêt de passer par de sous-requêtes ici.
Tu peux faire directement des jointures entre les 3 tables Localisation, Mesure et Favoris et extraire les températures min/max en une seule passe.
Les performances ne peuvent qu'être meilleures !

Tatayo.

[kango]

Je peux calculer le MIN et le MAX dans la même requête?

Dans ma base il y a d'autres champs que la température (pression par exemple) et j'ai besoin de faire des stats sur cette colonne. Tout ceci est faisable sans passer par ces sous requêtes?
Je confirme ce que tu dis, la perfo de ces sous-requêtes est pas bonne. Je le vois dès que je rajoute plein de localisations dans mes "favoris".

Je vais déjà essayer de faire le MIN et le MAX d'un coup. Je verrais après pour plusieurs colonnes. Merci déjà pour ta réponse.

[tatayo]

En se limitant à ce que tu nous as montré, on peut tout à fait utiliser plusieurs agrégations sans passer par une sous-requête.
Par contre si tu as d'autres données à récupérer, ce n'est peut-être plus le cas.

Tatayo.

[kango]

En effet, ceci fonctionne très bien

Code :

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

1
2
3
4
5
6
SELECT nom, MIN(mesures.temperature), MAX(mesures.temperature)
FROM localisations
JOIN mesures ON mesures.localisation = localisations.id
WHERE mesures.localisation IN (SELECT localisation FROM favoris)
GROUP BY mesures.localisation
ORDER BY nom

De même si j'ai plusieurs colonnes:

Code :

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

1
2
3
4
5
6
SELECT nom, MIN(mesures.temperature), MAX(mesures.temperature), MIN(mesures.pression), MAX(mesures.pression)
FROM localisations
JOIN mesures ON mesures.localisation = localisations.id
WHERE mesures.localisation IN (SELECT localisation FROM favoris)
GROUP BY mesures.localisation
ORDER BY nom

On peut se passer des sous requêtes et c'est bien plus efficace.

[SergioMaster]

Bonjour,

je n'ai jamais trop aimé les IN (SELECT ... FROM )
pourquoi ne pas avoir utiliser un

Code :

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

1
2
 
INNER JOIN FAVORIS  ON MESURES.LOCALISATION=FAVORIS.LOCALISATION

et j'aime bien "hiérarchiser" mes tables donc quid de :

Code :

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

1
2
3
4
5
SELECT l.nom, MIN(m.temperature), MAX(m.temperature), MIN(m.pression), MAX(m.pression)
FROM mesures m
JOIN localisations l ON m.localisation = l.id
INNER JOIN favoris f ON m.localisation=f.localisation
GROUP BY nom

[kango]

je me maitrise pas encore tous les types de JOIN, je vais tester avec le INNER JOIN du coup.

je ne comprends pas la subtilité du dernier bout de code si ce n'est que tu as inversé l'ordre des tables dans le FROM et dans le JOIN. Je ne m'étais pas (encore) poser la question de la différence de performance entre les deux.

[SergioMaster]

Bonjour,

je me maitrise pas encore tous les types de JOIN, je vais tester avec le INNER JOIN du coup.

pour certains SGBDs le INNER est implicite et JOIN=INNER JOIN

je ne comprends pas la subtilité du dernier bout de code si ce n'est que tu as inversé l'ordre des tables dans le FROM et dans le JOIN. Je ne m'étais pas (encore) poser la question de la différence de performance entre les deux.

pas de subtilité, juste une manie de ma part, j'aime mettre les tables de références en l'occurence Localisations après les tables de "résultats" ici mesures
en fait en me relisant, j'aurais même peut être mis l'INNER JOIN favoris (pratique de l'avoir différencier finalement) avant Localisations

en expression vernaculaire : prendre les résultats qui m'intéressent, interpréter les codes et grouper par localisation

je pense que l'optimiseur de requête fait le même travail (à vérifier) mais souvent je me pose la question "que lirait un tiers dans quelques années ?"
puisque tu as : la base, les requêtes je pense que tu peux facilement faire une comparaison

[SQLpro]

Trois remarques :
1) SQLlite est conçu pour de l'embarqué mono utilisateur. Il n'offre pas les mêmes performances qu'un vrai serveur relationnel comme MS SQL Server.
2) vous utilisez des données spatiales (longitude, latitude). Il existe des types de données SQL spécialisés pour cela (GEOMETRY et GEOGRAPHY). Cela sera plus efficace et tiendra compte de la courbure du géoïde terrestre. C'est le cas de MS SQL Server ou de PostGreSQL + cartouche PostGIS.
3) sans index, une base n'est rien au niveau performances. Rajoutez les index suivants et dites m'en des nouvelles :

Code :

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

1
2
3
CREATE INDEX X1 ON mesures (localisation, temperature, pression);
CREATE INDEX X2 ON localisations (id, nom);
CREATE INDEX X3 ON favoris (localisation);

A +