Améliorer une procédure stockée
Bonjour à tous,
J'ai un site de petites annonces et je cherche à réaliser le traitement suivant :
Parmi l'ensemble des catégories existantes, pour une région donnée, existe-t-il au moins une annonce?
Conditions supplémentaires :
- Il faut que les annonces est l'état valide = 1
- Si l'annonce appartient à la catégorie 7 ("soirée"), il faut que le champ contenant la date de la soirée soit >= à la date actuelle.
- Les résultats doivent être classés suivant le nom de la catégorie : champs "categorie.categorie_nom" ou "annonce.opt_cat_nom"
Deux tables sont concernées par ce traitement :
la table "categorie" et la table "annonce". Une annonce donnée "appartient" à une catégorie et à une région.
Afin d'optimiser les temps de traitement, ma base de donnée procède des redondances d'informations,
certain champs de la table "categorie" sont en effet dupliqués dans la table "annonce" afin de limiter le nombre de jointures nécessaires:
categorie.categorie_id -> annonce.opt_cat_id
categorie.categorie_nom -> annonce.opt_cat_nom
categorie.categorie_domaine -> annonce.opt_cat_domaine
Certain risque surement de trouver ce fonctionnement discutable mais ce n'est pas l'objet de ce post...
Pour résoudre ce problème j'ai d'abord créer cette requête :
Code:
1
2
3
4
| SELECT DISTINCT (a.opt_cat_id), a.opt_cat_nom, a.opt_cat_domaine FROM annonce a
LEFT OUTER JOIN z_soiree z ON (a.annonce_num=z.num) WHERE a.opt_region = '12'
AND a.annonce_valide=1 AND (z.date IS NULL OR TO_DAYS(z.date) >= TO_DAYS(NOW()))
ORDER BY a.opt_cat_nom |
Mais on se rend rapidement compte de la limite de cette dernière : un DISTINCT est réalisé sur plusieurs milliers de tuple alors que je ne cherche qu'à tester l'existence ou non de tuples respectant les conditions citées au début. L'utilisation d'un "GROUP BY" est similaire en terme de lourdeur de traitement.
J'ai donc décidé de créer une procédure stocké est de "stopper" la requête dès qu'un tuple respectant les conditions est trouvé (via "LIMIT 1") :
Code:
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
35
36
37
| DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test (IN var_in_opt_region INT(3), OUT var_out_categorie_id int(8),
OUT var_out_categorie_nom varchar(80), OUT var_out_categorie_domaine varchar(80) )
BEGIN
DECLARE var_done INT DEFAULT 0;
DECLARE var_categorie_id INT(8);
DECLARE cur_categorie CURSOR FOR SELECT c.categorie_id FROM categorie c ORDER BY c.categorie_nom;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET var_done = 1 ;
OPEN cur_categorie;
WHILE NOT var_done DO
FETCH cur_categorie INTO var_categorie_id;
IF var_categorie_id != 7 THEN
SELECT a.opt_cat_id, a.opt_cat_nom, a.opt_cat_domaine
FROM annonce a
WHERE a.opt_cat_id = var_categorie_id AND a.opt_region=var_in_opt_region AND a.annonce_valide=1
LIMIT 1;
ELSE
SELECT a.opt_cat_id, a.opt_cat_nom, a.opt_cat_domaine
FROM annonce a
INNER JOIN z_soiree z ON (a.annonce_num=z.num)
WHERE a.opt_cat_id = var_categorie_id AND a.opt_region=var_in_opt_region
AND a.annonce_valide=1 AND TO_DAYS(z.date) >= TO_DAYS(NOW())
LIMIT 1;
END IF;
END WHILE;
CLOSE cur_categorie;
END$$
DELIMITER ; |
Cette procédure stockée fonctionne, cependant je me pose plusieurs questions :
- Mon raisonement est il correct? Est-il possible de faire plus simple/rapide/mieux ?
- Uniquement pour l'avant dernière et la dernière itération, cette procédure me renvoie deux fois le même résultat. Comment éviter cela? (Est ce que le flag "var_done" n'est pas correctement définit du fait qu'une autre requête est exécutée pour chaque "FETCH" ???)
- Question annexe : Tel qu'est codé la procédure, certaine itération de renvoie aucun résultat. En effet si pour une catégorie donnée aucune annonce ne respect les conditions, aucun résultat n'est renvoyé. Comment être sur que chaque itération renvoie un résultat ?
N'hésitez pas à me demander si un point a besoin d'être détaillé...
D'avance merci pour vos réponses !!!
