Feature #11915
ouvert
Requête SQL incorrecte pour H2
0%
Description
Une partie de la persistance en base de données dans Silverpeas est réalisée en SQL pure via notre composant JdbcSqlQuery. Ce dernier offre une DSL pour écrire des requêtes SQL tout en s'occupant des étapes redondantes et tout en automatisant certains mécanismes comme, par exemple, la pagination.
La pagination SQL utilise des fonctions de fenêtrage. Or, l'une d'elles, COUNT() OVER() n'est malheureusement pas supportée par H2. H2 est la base de données utilisées d'une part par nos tests d'intégration et d'autre part par les distributions de test/démo de Silverpeas (IzPack, Docker, ...). Pour information, ce système de base de données a été choisi parce qu'il est directement embarqué dans Wildfly et par conséquent nous évite de complexifier la mise en place des différents contextes de test avec un autre système de bases de données légers (Derby, Firebird, ...).
Il faudrait ici revoir le code dans DefaultJdbcSqlExecutor qui utilise la fonction de fenêtrage COUNT() OVER() dans le cas de la pagination. Une solution possible serait d'utiliser une sous-requête SELECT COUNT() FROM.
Ce problème concerne toutes les versions de Silverpeas >= 6.1
Mis à jour par Yohann Chastagnier il y a plus de 3 ans
- Tracker changé de Bug à Feature
- Statut changé de New à Feedback
- Assigné à mis à Yohann Chastagnier
- Navigateur
Toussupprimé - Votre version de Silverpeas
6.1supprimé - Votre base de données
Toutessupprimé
Il est possible de désactiver l'utilisation de la fonction OVER() en définissant le paramètre jdbc.pagination.method.countOver à false dans le fichier de paramétrage $SILVERPEAS_HOME/properties/org/silverpeas/general.properties.
Une fois désactivée, des problématiques de performances peuvent alors apparaître sur des volumes de données importants.
Dans un contexte de démonstration, ces dernières ne devraient pas se manifester.
La fonction OVER() est utilisée dans le cas d'une requête pour laquelle une limitation du nombre de données est attendue et où le nombre total de lignes réelles est souhaité. Les 200 premières lignes sur les 250000 existantes par exemple. La fonction OVER() permet d'obtenir ce nombre réel de lignes existantes sans exécuter d'autres requêtes et sans perte en performance.
Je vais étudier la possibilité de proposer une alternative avec l'utilisation de la fonction COUNT() pour les base de données qui ne supportent pas COUNT() mais qui seraient dans un contexte de gestion de volumes de données importants.
Mis à jour par Miguel Moquillon il y a plus de 3 ans
Pour information, une alternative à COUNT() OVER() et en standard SQL est d'utiliser une sous-requête SELECT COUNT() FROM ... WHERE ....
- La requête initiale qui comprend la fonction
COUNT() OVER()s’exécute en moyenne en 65ms d'abord puis 35ms les autres fois. - Une double requête
SELECT ... FROM ... WHERE ...suivie d'une autreSELECT COUNT() FROM ... WHERE ...s'exécute en moyenne en 150ms dans un premier temps puis en 115ms les autres fois, - Une dernière requête qui reprend celle initiale mais dans laquelle la fonction
COUNT() OVER()est remplacée par une sous-requêteSELECT COUNT() FROM ... WHERE ...s'exécute quant à elle en moyenne en 35ms quelque soit le nombre de fois.
Les tests ont été réalisés avec la console SQL d'IDEA IntelliJ sur une base de données Silverpeas en locale et qui comprend un jeu de 34 données. La première mesure est écartée. Voici la requête initiale :
SELECT M.mediaId, M.mediaType, M.instanceId, M.title, M.description, M.author, M.keyWord, M.beginVisibilityDate,
M.endVisibilityDate, M.createDate, M.createdBy, M.lastUpdateDate, M.lastUpdatedBy,
COUNT(*) OVER() AS SP_MAX_ROW_COUNT
FROM SC_Gallery_Media M
WHERE M.instanceId = 'gallery11' ORDER BY M.createDate desc, M.mediaId desc;
Mis à jour par Yohann Chastagnier il y a plus de 3 ans
En analysant un peu plus en détail, il semble que le traitement sans l'utilisation de la fonction OVER() soit déjà optimisé.
L'utilisation de cette dernière permet d'être encore plus performant dans le parcours du ResultSet.
Finalement, je ne pense pas que cela soit nécessaire de proposer l'alternative du COUNT() :-)
Mis à jour par Miguel Moquillon il y a plus de 3 ans
Ok. Il me semblait que le COUNT() OVER() était utilisé dans la pagination afin d'avoir le nombre maxi de données en base, et donc dans les cas où ne sont récupérés que des fenêtres de données qui sont retournées sous forme de listes PaginationList (ou SilverpeasList )
Mis à jour par Miguel Moquillon il y a plus de 3 ans
Après test, le fait de positionner la propriété jdbc.pagination.method.countOver à false dans le fichier de paramétrage $SILVERPEAS_HOME/properties/org/silverpeas/general.properties résout les problème avec H2.