Re: Temps de réponse

Bonjour,

J'ai une base Postgres 7.4.3 installée sur une serveur linux
J'y accède via un poste client sous windows XP en utilisant le driver
ODBC pour Postgres (PostgreSQL30)

Nous voulons utilisons des outils du marché comme Transformer de Cognos
et Analyses Services de Microsoft pour interroger cette base et
constuire des cubes multi-dimensionnelles. C'est ce que nous faisons à
l'heure actuelle sur une base Oracle.

Les tests effectués avec ces outils donnent des résultats deçevant en
terme de temps de réponse (exemple: 40 min pour créer un cube sous
Cognos avec seulement 4000 ligne de factures, par compaison le même
genre de cube sur une base de 70 000 lignes prend 20 min).

J'ai effectué des tests sous winsql (outils de requêtage sous windows)
et une requête avec 4 jointures externes prend 18 min.
La même requête faite directement sur le serveur linux (sous psql) prend
6 min 40 s.

Le même genre de requête sur une base Oracle plus importante donne un
résultat immédiat.

Y-a-t'il un paramétrage particulier à mettre en place pour optimiser par
exemple les jointures externes.

Merci

Eric Hagenbach
eric(dot)hagenbach(at)vif(dot)tm(dot)fr
------------------------
Exemple de requete:
select T1."x1" as c1,
T1."x2" as c2,
T2."xxx" as c130,
T3."mt1" as c133,
T4."d1" as c140,
T5."d12" as c141
from (((("d"."table1" T1 left outer join "d"."table2" T2
on ((((T1."c1" = T2."c1") and (T1."c2" = T2."c2")) and
(T1."c3" = T2."c3")) and (T1."c4" = T2."c4")) and
(T1."c5" = T2."5")) left outer join "d"."table3" T3 on T1."id" = T3."id")
left outer join "d"."table4" T4 on T1."id" = T4."id")
left outer join "d"."table5" T5 on T1."id" = T5."id")
where ((((((T1."c1" = 'XX') and (T1."c2" = ' ')) and
(T1."C3" = 'XX')) and (T1."C4" = '99')) and
(T1."C7" = 'ZZZ')) and (T1."C8" IN ('AA','BB')))

Eric HAGENBACH wrote:

> Y-a-t'il un paramétrage particulier à mettre en place pour optimiser par
> exemple les jointures externes.

est-il possible d'avoir le explain de la requête ?
merci,

--
Jean-Max Reymond
CKR Solutions
http://www.ckr-solutions.com

Eric HAGENBACH a écrit :

> Bonjour,
>
> J'ai une base Postgres 7.4.3 installée sur une serveur linux
> J'y accède via un poste client sous windows XP en utilisant le driver
> ODBC pour Postgres (PostgreSQL30)
>
> Nous voulons utilisons des outils du marché comme Transformer de
> Cognos et Analyses Services de Microsoft pour interroger cette base et
> constuire des cubes multi-dimensionnelles. C'est ce que nous faisons à
> l'heure actuelle sur une base Oracle.
>
> Les tests effectués avec ces outils donnent des résultats deçevant en
> terme de temps de réponse (exemple: 40 min pour créer un cube sous
> Cognos avec seulement 4000 ligne de factures, par compaison le même
> genre de cube sur une base de 70 000 lignes prend 20 min).
>
> J'ai effectué des tests sous winsql (outils de requêtage sous windows)
> et une requête avec 4 jointures externes prend 18 min.
> La même requête faite directement sur le serveur linux (sous psql)
> prend 6 min 40 s.
>
Mea culpa, j'avais laissé les fichiers log (psqodbc_XXXX.log et
mylog_XXXX.log) et ça ralentissait énormément la requête:
la requête dure 6 min 50 s sous windows et le cube sous Cognos prend 9
min. C'est encore assez long !

> Le même genre de requête sur une base Oracle plus importante donne un
> résultat immédiat.
>
> Y-a-t'il un paramétrage particulier à mettre en place pour optimiser
> par exemple les jointures externes.
>
PAr contre j'ai une requête construite par l'outil Analyses Manager de
Microsoft qui fait la jointure entre 7 dimensions soit 7 tables (en
fait une des tables est déjà une vue avec une jointure externe) et la
requête lancé hier à 16h15 n'a toujours pas donnée de résultat (sur une
base qui ne compte que quelques milliers d'enregistrements) !!!
La même requête sans la jointure externe retourne le résutat en 9 s !!

> Merci
>
> Eric Hagenbach
> eric(dot)hagenbach(at)vif(dot)tm(dot)fr
> ------------------------
> Exemple de requete:
> select T1."x1" as c1,
> T1."x2" as c2,
> T2."xxx" as c130,
> T3."mt1" as c133,
> T4."d1" as c140,
> T5."d12" as c141
> from (((("d"."table1" T1 left outer join "d"."table2" T2
> on ((((T1."c1" = T2."c1") and (T1."c2" = T2."c2")) and
> (T1."c3" = T2."c3")) and (T1."c4" = T2."c4")) and
> (T1."c5" = T2."5")) left outer join "d"."table3" T3 on T1."id" = T3."id")
> left outer join "d"."table4" T4 on T1."id" = T4."id")
> left outer join "d"."table5" T5 on T1."id" = T5."id")
> where ((((((T1."c1" = 'XX') and (T1."c2" = ' ')) and
> (T1."C3" = 'XX')) and (T1."C4" = '99')) and
> (T1."C7" = 'ZZZ')) and (T1."C8" IN ('AA','BB')))
>

Eric HAGENBACH a écrit :

> Eric HAGENBACH a écrit :
>
>> Bonjour,
>>
>> J'ai une base Postgres 7.4.3 installée sur une serveur linux
>> J'y accède via un poste client sous windows XP en utilisant le driver
>> ODBC pour Postgres (PostgreSQL30)
>>
>> Nous voulons utilisons des outils du marché comme Transformer de
>> Cognos et Analyses Services de Microsoft pour interroger cette base
>> et constuire des cubes multi-dimensionnelles. C'est ce que nous
>> faisons à l'heure actuelle sur une base Oracle.
>>
>> Les tests effectués avec ces outils donnent des résultats deçevant en
>> terme de temps de réponse (exemple: 40 min pour créer un cube sous
>> Cognos avec seulement 4000 ligne de factures, par compaison le même
>> genre de cube sur une base de 70 000 lignes prend 20 min).
>>
>> J'ai effectué des tests sous winsql (outils de requêtage sous
>> windows) et une requête avec 4 jointures externes prend 18 min.
>> La même requête faite directement sur le serveur linux (sous psql)
>> prend 6 min 40 s.
>>
> Mea culpa, j'avais laissé les fichiers log (psqodbc_XXXX.log et
> mylog_XXXX.log) et ça ralentissait énormément la requête:
> la requête dure 6 min 50 s sous windows et le cube sous Cognos prend 9
> min. C'est encore assez long !
>
>> Le même genre de requête sur une base Oracle plus importante donne un
>> résultat immédiat.
>>
>> Y-a-t'il un paramétrage particulier à mettre en place pour optimiser
>> par exemple les jointures externes.
>>
> PAr contre j'ai une requête construite par l'outil Analyses Manager de
> Microsoft qui fait la jointure entre 7 dimensions soit 7 tables (en
> fait une des tables est déjà une vue avec une jointure externe) et la
> requête lancé hier à 16h15 n'a toujours pas donnée de résultat (sur
> une base qui ne compte que quelques milliers d'enregistrements) !!!
> La même requête sans la jointure externe retourne le résutat en 9 s !!

En approfondissant les recherches, il s'avère que le véritable problème
dans la vue n'est pas la jointure externe elle-même mais la condition
mise en place dans le where (il s'agit de la condition qui porte
uniquement sur la table principale):
Cette condition est du type:
Where (T1.C1 = 'XX' and T1.C2 = '99' and T1.C3 = 'ZZZ' and (T1.C4 IN
('AA','BB'))
Si je mets uniquement: where (T1.C1 = 'XX') la résultat de la vue
s'affiche en moins de 50 s
Si je mets where (T1.C1 = 'XX' and T1.C2 = '99'), le résultat n'est
toujours pas affiché au bout de 5 min.
Les 2 champs mentionnés font partis des index ??

Est-ce que ce genre de problème vous est connu ??

Je ne retrouve pas ce problème de condition quand je fais des accès
directs à la table (quand elle n'est pas dans la vue).

Est-ce que la requête finale (du genre celle en bas du mail) ne
serait-elle pas trop compliquée pour Postgres (ou simplement mal faite
pour Postgres) ?

>> Merci
>>
>> Eric Hagenbach
>> eric(dot)hagenbach(at)vif(dot)tm(dot)fr
>> ------------------------
>> Exemple de requete:
>> select T1."x1" as c1,
>> T1."x2" as c2,
>> T2."xxx" as c130,
>> T3."mt1" as c133,
>> T4."d1" as c140,
>> T5."d12" as c141
>> from (((("d"."table1" T1 left outer join "d"."table2" T2
>> on ((((T1."c1" = T2."c1") and (T1."c2" = T2."c2")) and
>> (T1."c3" = T2."c3")) and (T1."c4" = T2."c4")) and
>> (T1."c5" = T2."5")) left outer join "d"."table3" T3 on T1."id" = T3."id")
>> left outer join "d"."table4" T4 on T1."id" = T4."id")
>> left outer join "d"."table5" T5 on T1."id" = T5."id")
>> where ((((((T1."c1" = 'XX') and (T1."c2" = ' ')) and
>> (T1."C3" = 'XX')) and (T1."C4" = '99')) and
>> (T1."C7" = 'ZZZ')) and (T1."C8" IN ('AA','BB')))
>>
>

Le Wed, 28 Jul 2004 12:37:31 +0200

> En approfondissant les recherches, il s'avère que le véritable problème
> dans la vue n'est pas la jointure externe elle-même mais la condition
> mise en place dans le where (il s'agit de la condition qui porte
> uniquement sur la table principale):
> Cette condition est du type:
> Where (T1.C1 = 'XX' and T1.C2 = '99' and T1.C3 = 'ZZZ' and (T1.C4 IN
> ('AA','BB'))
> Si je mets uniquement: where (T1.C1 = 'XX') la résultat de la vue
> s'affiche en moins de 50 s
> Si je mets where (T1.C1 = 'XX' and T1.C2 = '99'), le résultat n'est
> toujours pas affiché au bout de 5 min.
> Les 2 champs mentionnés font partis des index ??
>
> Est-ce que ce genre de problème vous est connu ??
>
> Je ne retrouve pas ce problème de condition quand je fais des accès
> directs à la table (quand elle n'est pas dans la vue).
>
> Est-ce que la requête finale (du genre celle en bas du mail) ne
> serait-elle pas trop compliquée pour Postgres (ou simplement mal faite
> pour Postgres) ?
>

Quels sont les types de données des champs C1, C2 et C3. J'ai vu des
requetes de ce genre passé de 22 min. à 22 sec. en changeant l'index de
bigint vers integer.

--
Sébastien Lardière

Le mercredi 28 Juillet 2004 12:37, Eric HAGENBACH a écrit :
> >
> En approfondissant les recherches, il s'avère que le véritable problème
> dans la vue n'est pas la jointure externe elle-même mais la condition
> mise en place dans le where (il s'agit de la condition qui porte
> uniquement sur la table principale):
> Cette condition est du type:
> Where (T1.C1 = 'XX' and T1.C2 = '99' and T1.C3 = 'ZZZ' and (T1.C4 IN
> ('AA','BB'))
> Si je mets uniquement: where (T1.C1 = 'XX') la résultat de la vue
> s'affiche en moins de 50 s
> Si je mets where (T1.C1 = 'XX' and T1.C2 = '99'), le résultat n'est
> toujours pas affiché au bout de 5 min.
> Les 2 champs mentionnés font partis des index ??

Des index ou d'un index ... ça change beaucoup de chose ...

Tu peux nous passer un Explain de ta requête s'il te plaît et la structure des
tables ... pourque l'on puisse y voir plus claire ...

Merci
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Sébastien Lardière a écrit :

>Le Wed, 28 Jul 2004 12:37:31 +0200
>
>
>
>>En approfondissant les recherches, il s'avère que le véritable problème
>>dans la vue n'est pas la jointure externe elle-même mais la condition
>>mise en place dans le where (il s'agit de la condition qui porte
>>uniquement sur la table principale):
>>Cette condition est du type:
>>Where (T1.C1 = 'XX' and T1.C2 = '99' and T1.C3 = 'ZZZ' and (T1.C4 IN
>>('AA','BB'))
>>Si je mets uniquement: where (T1.C1 = 'XX') la résultat de la vue
>>s'affiche en moins de 50 s
>>Si je mets where (T1.C1 = 'XX' and T1.C2 = '99'), le résultat n'est
>>toujours pas affiché au bout de 5 min.
>>Les 2 champs mentionnés font partis des index ??
>>
>>Est-ce que ce genre de problème vous est connu ??
>>
>>Je ne retrouve pas ce problème de condition quand je fais des accès
>>directs à la table (quand elle n'est pas dans la vue).
>>
>>Est-ce que la requête finale (du genre celle en bas du mail) ne
>>serait-elle pas trop compliquée pour Postgres (ou simplement mal faite
>>pour Postgres) ?
>>
>>
>>
>
>Quels sont les types de données des champs C1, C2 et C3. J'ai vu des
>requetes de ce genre passé de 22 min. à 22 sec. en changeant l'index de
>bigint vers integer.
>
>
>
Ce sont des champs de type "chaine de caractères" (character varying(5)
pour le champs C2 par exemple)

Par contre quand je regarde la description de la vue sous psql (\d
nomvue), le where est indiqué de la façon suivante:
where T1.C1::text = 'XX'::text (c'est valable pour tous les champs de la
condition). Est-ce que ça joue ?!?

Merci,

Le Wed, 28 Jul 2004 14:24:47 +0200
Eric HAGENBACH <eric(dot)hagenbach(at)vif(dot)tm(dot)fr> a écrit :

> >>Where (T1.C1 = 'XX' and T1.C2 = '99' and T1.C3 = 'ZZZ' and (T1.C4 IN
> >>('AA','BB'))

> Ce sont des champs de type "chaine de caractères" (character varying(5)
> pour le champs C2 par exemple)
>
> Par contre quand je regarde la description de la vue sous psql (\d
> nomvue), le where est indiqué de la façon suivante:
> where T1.C1::text = 'XX'::text (c'est valable pour tous les champs de la
> condition). Est-ce que ça joue ?!?

Oui, je pense que ca joue beaucoup, même. Un index sur un type 'text',
c'est déjà lourd, alors en plus avec des milliers de ligne et dans une
requete complexe !

On ne le dira jamais assez, mais les choix de conceptions sont
primordiaux pour la bonne tenue de la base de données. Ce n'est pas pour
rien qu'il y a plusieurs types de données disponibles, encore faut-il
s'en servir !

Je serais toi, je commencerais par revoir completement les schémas de la
base. De plus, tu utilises les Drivers ODBC, qui n'est pas ce qu'il y a
de plus rapide, malheureusement ...

--
Sébastien Lardière

Sébastien Lardière a écrit :

>Le Wed, 28 Jul 2004 14:24:47 +0200
>Eric HAGENBACH <eric(dot)hagenbach(at)vif(dot)tm(dot)fr> a écrit :
>
>
>
>>>>Where (T1.C1 = 'XX' and T1.C2 = '99' and T1.C3 = 'ZZZ' and (T1.C4 IN
>>>>('AA','BB'))
>>>>
>>>>
>
>
>
>>Ce sont des champs de type "chaine de caractères" (character varying(5)
>>pour le champs C2 par exemple)
>>
>>Par contre quand je regarde la description de la vue sous psql (\d
>>nomvue), le where est indiqué de la façon suivante:
>>where T1.C1::text = 'XX'::text (c'est valable pour tous les champs de la
>>condition). Est-ce que ça joue ?!?
>>
>>
>
>Oui, je pense que ca joue beaucoup, même. Un index sur un type 'text',
>c'est déjà lourd, alors en plus avec des milliers de ligne et dans une
>requete complexe !
>
>
>
En fait, la construction de la vue est fait par un script psql et
dedans il n'est pas précisé le type 'text' dans la condition (c'est le
script Oracle qui a été adapté pour fonctionner avec Postgres).
Toute la base de données à ainsi été conçue par migration d'une base
Oracle vers une base Postgres (notamment en utilisant l'outil ora2pg.pl)
ainsi tous les champs en varchar2 d'Oracle sont passés en character
varying chez Postgres.

>On ne le dira jamais assez, mais les choix de conceptions sont
>primordiaux pour la bonne tenue de la base de données. Ce n'est pas pour
>rien qu'il y a plusieurs types de données disponibles, encore faut-il
>s'en servir !
>
>Je serais toi, je commencerais par revoir completement les schémas de la
>base. De plus, tu utilises les Drivers ODBC, qui n'est pas ce qu'il y a
>de plus rapide, malheureusement ...
>
>
>
La base oracle est déjà installée chez des clients et la base Postgres
doit suivre la même structure. Nous avons un logiciel écrit en java qui
attaque les 2 bases, donc les modifs de structure doivent être minimes.

Quand à l'utilisation du driver ODBC, c'est pour l'instant la seule
solution qu'on a trouvé quand on passe par des outils du marché sous
windows (Cognos et MS olap) pour attaquer Postgres, et encore il y a
pas mal de paramétrage à mettre en ouvre par exemple pour le requêteur
de chez Cognos (merci le support Cognos pour son aide, par contre pour
Microsoft c'est plus flou !!).

Pour en revenir la structure, seule la jointure externe entre la table
principale et une table annexe avec des champs de types "chaine de car"
et "numérique" est peut-être inadéquate mais bon il s'agit d'une
jointure classique entre une entête de facture et le détail de la
facture. Les autres jointures se font par un seul champs de type
numérique (genre id). Il s'agit d'une représentation classique en étoile
(une table principale et des tables satellites).
Par contre, quand on utilise des outils tels que MS Olap, ceux-ci créée
d'autres jointures (pour relier un code client de la facture à une
dimension géographique par exemple): il peut y en avoir un certain
nombre . Et ces jointures viennent s'ajouter à la requête finale qui est
envoyée par l'outil vers la base Postgres.
Pour l'instant, nous n'avions pas eu trop de problème avec MS Olap et
Oracle : On avait simplement ajouter des vues sur la base pour faciliter
le travail du client
(au final c'est lui qui utilise MS Olap) et quelques index pour
accélérer les temps de réponses. Ces index ont également été ajoutés
pour Postgres.

En espérant avoir été plus clair sur le contexte d'utilisation de ces
requêtes.

Rq: Le but final de passer d'une base Oracle vers une base Postgres est
bien entendu de faire baisser les coûts (Et Postgres a été choisi par
rapport à Mysql à cause des vues qui ne sont pas gérées dans cette
dernière, pour le moment...).

Eric

Eric HAGENBACH wrote:

> En espérant avoir été plus clair sur le contexte d'utilisation de ces
> requêtes.
>

on souhaite avoir l'explain !!!!!
le reste n'est que littérature :-(

--
Jean-Max Reymond
CKR Solutions
http://www.ckr-solutions.com

Eric HAGENBACH wrote:
> En fait, la construction de la vue est fait par un script psql et
> dedans il n'est pas précisé le type 'text' dans la condition (c'est le
> script Oracle qui a été adapté pour fonctionner avec Postgres).
> Toute la base de données à ainsi été conçue par migration d'une base
> Oracle vers une base Postgres (notamment en utilisant l'outil ora2pg.pl)
> ainsi tous les champs en varchar2 d'Oracle sont passés en character
> varying chez Postgres.

Quel rapport entre « text » et « varchar2 » ?

Vous insinuez qu'ora2pg transforme les VARCHAR2 d'Oracle en Text sous PG?...

Si oui, donnez un exemple.. Ça peut être un VARCHAR2 extrèmement gros?...

>> On ne le dira jamais assez, mais les choix de conceptions sont
>> primordiaux pour la bonne tenue de la base de données. Ce n'est pas pour
>> rien qu'il y a plusieurs types de données disponibles, encore faut-il
>> s'en servir !

Remarque on ne peut plus sensée!... Il faut toujours choisir le type de
données le plus approprié à l'utilisation. C'est une règle simple,
domage qu'on l'oublie trop! :-)

> La base oracle est déjà installée chez des clients et la base Postgres
> doit suivre la même structure. Nous avons un logiciel écrit en java qui
> attaque les 2 bases, donc les modifs de structure doivent être minimes.

Certes. Mais votre base Oracle côté client est-elle tunée, ne serait-ce
qu'un peu?...

Pouvez vous nous fournir le Oracle.ini de l'instance Oracle en question?

Mieux, un OraSnap en ligne? (http://www.oracle-books.com/orasnap/)

> Quand à l'utilisation du driver ODBC, c'est pour l'instant la seule
> solution qu'on ait trouvée quand on passe par des outils du marché sous
> windows (Cognos et MS olap) pour attaquer Postgres, et encore il y a
> pas mal de paramétrage à mettre en ouvre par exemple pour le requêteur
> de chez Cognos (merci le support Cognos pour son aide, par contre pour
> Microsoft c'est plus flou !!).

Votre PG est sous linux, ou sous windows. Sous windows en Natif (oui
c'est déjà possible, en version de dev bien sur...) ou sous CygWin?..

> Pour en revenir la structure, seule la jointure externe entre la table
> principale et une table annexe avec des champs de types "chaine de car"
> et "numérique" est peut-être inadéquate mais bon il s'agit d'une
> jointure classique entre une entête de facture et le détail de la
> facture.

Peut être "classique" au sens métier pour vous. Reste que ce n'est pas
bien, PG est obligé de caster (::TEXT)...

La conception de votre schéma semble pour le moins curieuse, non?

> Les autres jointures se font par un seul champs de type
> numérique (genre id).

Pourquoi les autres et pas la précédente? :-/

> Il s'agit d'une représentation classique en étoile

Bien des physiques ont cette tête en effet, ça ne préjuge de rien dans
votre cas.

> En espérant avoir été plus clair sur le contexte d'utilisation de ces
> requêtes.

Comme on vous l'a déjà dit, un EXPLAIN avec un bout de schéma est
largement plus explicatif pour nous pour vous aider.

> Rq: Le but final de passer d'une base Oracle vers une base Postgres est
> bien entendu de faire baisser les coûts

Dans un 1er temps, on vient toujours à PG pour cela, comme pour tous les
autres logiciels libres. Je pense qu'après qques mois d'utilisation, cet
argument là sera minime par rapport à d'autres...

> Et Postgres a été choisi par
> rapport à Mysql à cause des vues qui ne sont pas gérées dans cette
> dernière, pour le moment

Sans troller (une fois de plus). Vu la tête de vos requetes, oubliez
direct MySQL, et pas seulement pour les vues hein.... :-))

Cordialement,

--
JP ARGUDO

L'Explain lancé sur les différentes requêtes a permis de vérifier que
selon les requêtes, l'index utilisé pour la jointure externe n'était pas
le même.

Après modification de la requête posant problème pour la forcer à
prendre le bon index, les temps se sont très largement améliorés.

Le temps de création d'un cube sous MS Olap tourne autour de 40" (sans
agrégation) et 1'40 (avec agrégations).

Merci à tous.

Eric.

Eric HAGENBACH wrote:
> L'Explain lancé sur les différentes requêtes a permis de vérifier que
> selon les requêtes, l'index utilisé pour la jointure externe n'était pas
> le même.

Faites vous des vacuum de votre base PG?... SI oui, quand, comment... à
quelle occasion.

L'optimiseur choisit rarement le "mauvais" index, je trouve cela un peu
suspect.

> Après modification de la requête posant problème pour la forcer à
> prendre le bon index, les temps se sont très largement améliorés.

Comment forcez-vous la requête pour prendre "le bon index"?

> Le temps de création d'un cube sous MS Olap tourne autour de 40" (sans
> agrégation) et 1'40 (avec agrégations).
> Merci à tous.

De rien, vous avez trouvé tout seul au final!

> Eric.

Je crois que toute la communauté vous serait gré de rédiger un article
succint sur votre étude de migrabilité... Avec les objectif de l'étude,
les problèmes rencontrés, leur solution, et vos conclusions...

Nous pourrions la poster sur www.PostgreSQLFr.org.

Qu'en pensez-vous?..

Merci!

--
Jean-Paul Argudo
www.Argudo.org
www.PostgreSQLFr.org