Vous n'êtes pas identifié(e).
bonjour,
J'ai un besoin relatif à la génération d'un flux texte par opérations de concaténation de chaînes de caractères.
Venant du monde Oracle, mon premier réflexe a été d'utiliser l'opérateur "||", mais a ma grande surprise, les performances sont très mauvaises !
Les tests ci-dessous ont été réalisés avec PostgreSQL 13 en environnement Windows 64-bit.
Cas 1: Utilisation de l'opérateur "||" (10 000 lignes en 10.8 secondes)
create or replace function generate_text(p_rows in integer) returns text
language plpgsql
as $$
declare
v_text text := '';
begin
for i in 1..p_rows
loop
v_text := v_text || 'This is the first value : ' || '1' || ' / This is the second value : ' || '2' || ' / This is the third value : ' || '3' || ' / This is the fourth value : ' || '4' || chr(10);
end loop;
return v_text;
end; $$Cas 2: Utilisation de la fonction "format" pour la substitution des valeurs de chaque ligne (10 000 lignes en 2.6 secondes)
create or replace function generate_text_2(p_rows in integer) returns text
language plpgsql
as $$
declare
v_text text := '';
begin
for i in 1..p_rows
loop
v_text := v_text || format('This is the first value : %1$s / This is the second value : %2$s / This is the third value : %3$s / This is the fourth value : %4$s','1', '2', '3', '4') || chr(10);
end loop;
return v_text;
end; $$Cas 3: Utilisation de la fonction "format" et d'un tableau pour la gestion des lignes de texte (10 000 lignes en 47 millisecondes)
create or replace function generate_text_3(p_rows in integer) returns text
language plpgsql
as $$
declare
v_array text[];
begin
for i in 1..p_rows
loop
v_array := array_append(v_array, format('This is the first value : %1$s / This is the second value : %2$s / This is the third value : %3$s / This is the fourth value : %4$s','1', '2', '3', '4'));
end loop;
return array_to_string(v_array, chr(10));
end; $$ Suite à ces constats :
- Jugez-vous ces différences normales et avez-vous une explication sur de tels écarts de performance ?
- Sachant que le néophyte ira plus facilement sur la première stratégie, n'y a-t-il pas un moyen pour optimiser l'opérateur "||" ?
Cordialement
Hors ligne
Je ne suis pas particulièrement étonné pour la différence entre les cas 1 et 2. L'utilisation d'un opérateur nécessite l'appel d'une fonction. Vous utilisez 9 fois l'opérateur || dans le cas 1,donc vous avez neuf appels de fonction.Or vous n'avez que deux appels de fonction dans le cas 2.Logiquement, le cas 1 devrait donc être 4 fois plus lent que le cas 2, ce qui est grosso modo le cas.
Pour la différence entre le cas 2 et le cas 3, cela dépend plus de ce que font chacune des fonctions. Je ne suis pas spécialement étonné que des fonctions différentes aient des performances différentes.
De toute façon, la manipulation de chaînes de caractères directement en PL/pgsql est plutôt lente. Mieux vaut d'autres langages comme le C ou le perl.
Guillaume.
Hors ligne
L'opérateur || alloue la taille des 2 chaînes cumulées, puis copie dans l'espace alloué la chaîne 1, puis la chaîne 2. Il est obligé de faire ça parce qu'il doit produire une nouvelle chaîne d'un seul tenant.
Comme l'algorithme du cas 1) cumule en boucle la chaine v_text qui est à la fois source et destination, il passe son temps à recopier ce qu'il a déjà copié. D'une certaine manière si le coût d'une opération de concaténation était de 1, l'ordre de grandeur du coût global n'est pas de 1+1+1+1... avec N additions mais plutôt 1+2+3+4+...+N, soit (N*(N+1))/2.
Optimiser individuellement l'opérateur || ne changerait pas cette complexité qui est la raison principale de la lenteur.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Merci beaucoup pour vos réponses et votre réactivité.
Je reste tout de même sceptique sur le fait que rien ne puisse être optimisé en interne, car :
- Le cas n°1 avec Oracle prend 3 secondes pour 10 000 lignes et n'est pas impacté par la taille de la chaîne de caractères produite pour chaque ligne
- Le cas n°1 avec PostgreSQL part en dérive exponentielle dès que la taille de la chaîne de caractères augmente pour chaque ligne
Je conserve la solution basée sur le tableau de chaînes de caractères, qui reste bien plus efficace.
Cordialement
Hors ligne
Cela dit la principale raison de la lenteur de votre cas 1 (et de tous les autres dans une certaine mesure) est due au fait que vous utilisez une approche impérative plutôt qu'ensembliste, et une base de donnée n'est généralement pas faite pour ça. Certains langage procéduraux pourraient avoir de meilleurs performances, mais ce n'est pas le cas de plpgsql, car il n'a pas vocation à implémenter de manière très efficace ce qui n'est pas censé être implémenté en langage procédural.
Une bonne réécriture serait donc :
CREATE OR REPLACE FUNCTION public.generate_text(p_rows integer)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
v_text text := '';
BEGIN
SELECT string_agg('This is the first value : ' || '1' || ' / This is the second value : ' || '2' || ' / This is the third value : ' || '3' || ' / This is the fourth value : ' || '4', chr(10)) INTO v_text FROM generate_series(1, p_rows);
return v_text;
END; $function$Cette fonction s'exécute chez moi en moins de 10ms. Utilisez postgres comme une base de données et vous aurez de meilleurs performances et généralement moins de bugs.
Julien.
https://rjuju.github.io/
Hors ligne
@rjuju,
Merci pour ta réponse éclairée.
Je précise que mon besoin initial est bien ensembliste et majoritairement produit à partir du langage SQL.
Seule la partie de transformation en flux texte final était codé en procédural, car plus facile à maintenir.
Les exemples que j'ai fourni dans mon post initial sont uniquement là pour se focaliser sur la fonction de concaténation de chaînes de caractères.
Après analyse du code source, je constate que les deux opérandes text sont systématiquement recopiées pour former la chaîne concaténée, ce qui pose problème lorsque la chaîne initiale est complétée de manière récursive.
Les autres approches qui font intervenir des tableaux ou des fonctions d’agrégation ne sont pas concernées par cette problématique, ce qui les rend bien plus performantes.
Dans un contexte de migration d'Oracle (ou autre SGBD) vers PostgreSQL, les informations disponibles dans ce sujet peuvent être très utiles.
Cordialement
Hors ligne