Discussion :

[cantador]

Bonjour,

j'ai deux tables (RESEAU et LOCAL), avec pour l'une 530 000 enregistrements et l'autre 340 000.
je construis une requête sous access directement dans le requêteur, en créant deux liaisons dynamiques entre deux champs.
je passe un champ (REF) en paramètre dans la table 2.
ce qui donne dans le FROM et le WHERE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
FROM [LOCAL] INNER JOIN RESEAU ON (LOCAL.[COMPTE] = RESEAU.[COMPTE]) AND (LOCAL.[TOTAL]= RESEAU.[TOTAL])
WHERE (((LOCAL.[REF])="donnee"))

L'exécution s'effectue en 3 secondes et sort correctement les résultats attendus..

la même requête sous Firebird avec création de deux index sur les champs COMPTE et TOTAL de la table2 (pour accélérer d'autres requêtes..) et une clé primaire incrémentale sur chacune des tables (non utilisée dans les requêtes..)

met 130 secondes pour sortir les données !

j'ai essayé pas mal de trucs : suppression des index, backup, restore etc..
rien à faire, toutes les autres requêtes fonctionnent bien sauf celle-ci.

??

merci pour votre aide

[dehorter olivier]

Bonjour

Et y a t-il un index sur la colonne : LOCAL.[REF] ?

a+

olivier

[cantador]

Bonjour dehorter olivier,

j'ai fait le test en mettant un index sur REF avec en plus un Backup Restore de la base.
rien ne sort..je shunte au bout de 1'30"..(marre d'attendre..)

je passe un LIKE sous Firebird :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
WHERE (LOCAL.REF LIKE ''%'' || :REFER || ''%'')
Query1.ParamByName('REFER').Value := cxTEMonParam.Text;

j'ai essayé aussi sans le LIKE /

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
WHERE (LOCAL.REF =:REFER)
Query1.ParamByName('REFER').Value := cxTEMonParam.Text;

toujours le même problème..

merci

[dehorter olivier]

As tu testé le plan de la requête ? Cela permettrait de voir clairement le goulot d'étranglement.

[cantador]

dernier résultat sur ibmanager: 36 lignes pour 1' 19"

grrhhh..
hummmhhh....

j'ai aussi l'analyse de performance avec un grand tuyau horizontal avec un chiffre: 14 947 390

?

[dehorter olivier]

Le "natural" indique qu'il n'utilise pas l'index

Pourrais-tu réaliser un script de création de base ainsi qu'un petit je de données afin que je teste ?

[cantador]

j'ai également créé ma requête à l'aide du constructeur de requête sur IbManager.
le résultat est identique..

pour le scrypt, je peux le sortir bien sûr, mais je ne peux rien divulguer.
quand aux données, c'est impossible..beaucoup trop confidentiel..

ce que je peux faire en revanche, c'est sortir un scrypt en changeant les noms des champs

ça te convient ?

@+

[cantador]

de toute façon, le script ne te donnera pas grand chose, car c'est une base que je créé à la mano
nom de base, nom de champ et importation des données à partir de fichier csv.
juste quelques précisions supplémentaires, la plupart des mes champs sont en VARCHAR 255
et mon pagesize est à 4096
et dans ma base, je n'ai aucune liaison en dur (aucune clé étrangère..)
juste, des clés primaires, pour numéroter les enregistrements que j'aurais d'ailleurs pu éviter..
voilà

[dehorter olivier]

Oui bien sur

j'utilise IBExpert comme interface, et je lui ferais générer un jeu de données bidon test

a+

olivier

Ps : je viens de voir le second message.
Pas de clé étrangère ???? Ils devraient en avoir pour les intégrités qui doivent être utiliser dans les jointures

[cantador]

Pas de clé étrangère ???? Ils devraient en avoir pour les intégrités qui doivent être utiliser dans les jointures

oui, tu as raison, ce n'est pas dans mes habitudes de travail, mais là, je suis devant un cas de figure particulier,
car toutes mes tables comportent plein de répétitions dans les champs en question:
mais les enregistrements ne sont pas des DOUBLONS !

ex: avec le champ COMPTE des tables RESEAU et LOCAL
RESEAU.COMPTE
toto
titi
titi
titi
tata
toto
toto

LOCAL.COMPTE
papa
popo
riri
toto
toto
toto
titi
titi
riri
riri
riri
toto
toto
tata
tata
tata
tata

Si on se contente de mettre en mettre une liaison à la volée entre les deux tables, sous ACCESS ou sous le requêteur de IbManager, pas de soucis, mais bien sûr, il est impossible de créer une liaison 1_N en dur.
en fait j'ai des liaisons N-N dans toutes mes tables !
Je travaille sur 3 champs, deux qui servent de liaison puis je passe en paramètre, soit :
COMPTE ou TOTAL ou le champ REF de la table LOCAL

mais tu as raison,
c'est vraisemblablement toutes ces données répétitives qui doivent perturber FireBird..
mais ACCESS, lui s'en sort..
?

[dehorter olivier]

En effet.
Il me semble avoir lu que lorsque même si un index existe, mais qu'il n'apporte rien (cas de très multiples répétitions donc une stat de l'index proche de 1), l'index n'est pas utilisé.


Proposition :
Pourquoi ne pas essayer une sous-requête permettant de sélectionner les LOCAL.[REF])="donnee"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT LOCAL.COMPTE, LOCAL.TOTAL 
    FROM Local
    WHERE Local.Ref = "donnee"

pour faire quelque chose comme cela (Je sais, ce n'est vraiment, vraiment, vraiment pas bien )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT xxxx
   FROM (SELECT LOCAL.COMPTE, LOCAL.TOTAL 
    FROM Local
    WHERE Local.Ref = "donnee") AS SR
      INNER JOIN Reseau ON (SR.Local = RESEAU.COMPTE AND SR.TOTAL = RESEAU.TOTAL)

Ne me frapper pas

[SergioMaster]

Bonjour,

je ne voulais pas trop m'impliquer dans ce trend, faute de temps, désolé.
il est toujours possible de forcer le PLAN de la requête

[cantador]

@dehorter olivier:

dans ce type d'écriture, le SELECT ne passe pas ->>> INVALID TOKEN

@SergioMaster:

forcer le plan de la requête ?
pourquoi pas ?
c'est nouveau pour moi, mais intéressant..
j'ai vu qu'on pouvait jouer avec les index...les choisir etc..

l'autre piste étant celle préconisée par dehorter olivier : SELECT FROM SELECT

?

même si je reste toujours coincé avec cette requête
merci pour ces pistes de réflexion qui font toujours avancer les choses.

@bientôt

[makowski]

la plupart des mes champs sont en VARCHAR 255

ça ne va pas aider non plus

le seul moyen si tu veux qu'on t'aide c'est vraiment de donner le script de def des tables et un jeux de données significatif
là on parle trop à l'aveugle

[cantador]

Bonjour makowski,

je vais y réfléchir...

@+

[cantador]

Bonjour à tous et bonne année,

je reprends cette discussion après avoir procédé à des optimisations de la base de données (champs)
et des requêtes SQL (remplacement des LIKE par STARTING WITH)

la base a bien fondu et les actions se font plus rapide, mais la requête en question même si ses temps sont divisés par 2,
est encore affreusement beaucoup plus longue que celle exécutée par ACCESS 2000 !

j'ai besoin de finir les optimisations de deux tables comportant de nombreux champs et pour ce faire, je dois connaître la taille maximum des occupations des données (+ la doc bien sûr)

en sql cela donne pour un seul champ :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SELECT MAX (CHAR_LENGTH(MonChamp)) FROM MaTable

l'idéal aurait été de pouvoir réaliser cette opération sur l'ensemble des champs en une seule fois et obtenir

nom des champs / occupation maxi
champ1 25
champ2 48
champ3 60
etc..

je ne vois pas comment construire cette requête

merci de votre aide

[SergioMaster]

Bonjour à tous et bonne année

Je renvoi les vœux avec mes souhaits, ce que tu souhaites et plus encore !

l'idéal aurait été de pouvoir réaliser cette opération sur l'ensemble des champs en une seule fois
je ne vois pas comment construire cette requête

une requête, peut être pas , mais dans une procédure stockée cela semble faisable

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SET TERM ^ ;
 
CREATE PROCEDURE LISTE_CHAMPS
RETURNS 
 ( CHAMP VARCHAR(30),
   LONGUEUR SMALLINT)
AS 
BEGIN
FOR SELECT TRIM(T1.rdb$field_name) 
      FROM  rdb$relation_fields T1 where T1.rdb$relation_name='MATABLE'  
      INTO :CHAMP
DO BEGIN
 SELECT MAX (CHAR_LENGTH(:Champ)) FROM MATABLE INTO :LONGUEUR;
 SUSPEND; 
END
END^
 
SET TERM ; ^

Procédure faite à l'arraché et certainement à peaufiner

[Edit] j'ai vu qu'il y avait 2 tables (ou plus) donc en modifiant la procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SET TERM ^ ;
ALTER PROCEDURE LISTE_CHAMPS (
    NOMTABLE Varchar(31) )
RETURNS (
    CHAMP Varchar(31),
    LONGUEUR Smallint )
AS
DECLARE STMT VARCHAR(180);
BEGIN
FOR SELECT TRIM(T1.rdb$field_name) 
      FROM  rdb$relation_fields T1 where T1.rdb$relation_name=:NOMTABLE  
      INTO :CHAMP
DO BEGIN
 STMT='SELECT MAX (CHAR_LENGTH('||:CHAMP||')) FROM '||NOMTABLE;
 EXECUTE STATEMENT STMT INTO :LONGUEUR;
 SUSPEND; 
END
END^
SET TERM ; ^

[cantador]

merci beaucoup SergioMaster

je vais gagner du temps et des efforts avec tout ça..
pour la requête merdique, j'ai laissé tomber..
j'ai introduit un second paramètre permettant de restreindre la liste des résultats
et là, je suis en millièmes de secondes..
je n'ai rien compris à ce truc de fous!
ça fait partie des joies de l'informatique.
à noter qu'avec le STARTING WITH, l'index est utilisé contrairement au LIKE.
je mets résolu, sauf si tu souhaites creuser encore cette anomalie (envoie moi un mail)
@+ et encore merci

cantador