Le sujet qui m’intéresse directement ici est celui des requêtes préparées. Les points forts des requêtes préparées sont de deux ordres:
- d’une part la sécurité de votre application est accrue: adieu les injections SQL.
- ensuite l’exécution de requêtes identiques est accélérée.
Modalités d’exécution des requêtes par les SGBD.
Quand un Systèmes de Gestion de Bases de Données (SGBD) reçoit une requête, il lui fait subir deux types d’analyse: le système s’assure d’abord que la requête ne comporte pas d’erreur, qu’elle est bien valide. Ensuite il tente d’établir le moyen optimal de l’exécuter en prenant en compte les index qui ont été définis dans les tables concernées. Cette dernière opération peut être très longue; cela dépend bien sûr de la complexité des requêtes que vous générez, du nombre de tables concernées, des index que vous avez défini, judicieusement ou non, etc, mais pour la plupart des applications il n’est pas acceptable qu’une requête mette plus d’une seconde pour s’exécuter.
La plupart des SGBD utilisent un système de cache pour éviter de devoir analyser plusieurs fois les mêmes requêtes. Ce système repose sur une table de hachage: le contenu de la requête sert de clé, et la clé mène au plan d’exécution. L’ennui, c’est que le plus petit changement dans une requête aura pour conséquence de générer une clé différente et sera ainsi perçu comme une nouvelle requête par le SGBD:
Ainsi les plans d’exécution de
SELECT * FROM tableX WHERE tableX.champ > 10
et de
SELECT * FROM tableX WHERE tableX.champ > 20
sont identiques, et pourtant le SGBD lancera une analyse pour chacune d’entre elles. La solution: utiliser des requêtes préparées:
SELECT * FROM tableX WHERE tableX.champ > ?
Se faisant vous pouvez remplacer la variable par la valeur que vous désirez APRES que la clé ait été générée et le plan d’exécution établi.
Pool de connexions et requêtes préparées.
Malheureusement, les requêtes préparées sont associées aux connexions et disparaissent avec elles. Ce qui serait idéal, ce serait de pouvoir créer une fois pour toute l’ensemble des requêtes préparées voulues afin de les mettre à disposition de tous les clients durant toute la durée de vie du serveur. Les concepteurs de J2EE y ont pensé, et ils ont intégré cette possibilité de manière transparente aux pool de connexions en associant un cache à chacune des connexions du pool (je ne parlerai pas ici de la création des pool de connexion avec Tomcat: Christophe Jollivet sur developpez.com s’en est déjà chargé, et l’article est très accessible). L’avantage de ce système c’est que vous n’avez pas à vous préoccuper de quoique ce soit. Le revers de la médaille, c’est que vous ne pouvez pas contrôler quelles requêtes seront mises en cache; tout ce que vous pouvez faire c’est définir le nombre de requêtes maximal pouvant être placé dedans à l’aide des paramètres suivants:
- maxStatements: le nombre maximal total de requêtes pouvant être acceptées dans le cache.
- maxStatementsPerConnection: le nombre maximal de requêtes liées à une connexion pouvant être acceptées dans le cache.
