Hi Mona!

Your query is not bad, but without a plan I'm really no good at
helping. The only thing I can suggest is that if most refNSF is >0,
then you could change to
AND Cheque.refNSF+0>0
and similarly for other indexed fields which have a very common value.
For further help, you could download IB_SQL from www.ibobjects.com.
That is freeware and it displays a PLAN as soon as you prepare a
statement (the light bulb in the left corner). With the plan - and
ideally some statistics, we are several on this list that can help you
out.

HTH,
Set

--- In firebird-support@yahoogroups.com, "mona yazbeck" wrote:
> Hi again!
>
> I don't know exactly how to create the plan with IBExpert Personal
> Edition... can I?
>
> And RapportFiduciePart5, is really juste a Query, I am using Access
> that's why!
>
> Here's the query: (And I know it's ugly!)
>
> SELECT Cheque.IDGROUPE, Cheque.IDDOSSIER, Cheque.montantClient,
> Cheque.IDCHEQUE, Cheque.NOCHEQUE, Cheque.Concdate, Cheque.montant,
> [prenom] & " " & [nom] AS nomComplet, Cheque.MontantClient_Dispo,
> Cheque.intrust, Cheque.HNFrais_Dispo, Cheque.groupeChequeClient,
> Cheque.groupeChequeHonoraire, Cheque.groupeChequeHN,
> Cheque.montantHN, Cheque.ConcYesNo, Cheque.NSF, Cheque.chequeRemis,
> Cheque.refNSF, Cheque.DATEDEPOT, Cheque.chkDossier, Cheque.depot,
> Cheque.validationDepot, Cheque.percepteur
> FROM Cheque
> INNER JOIN PersonnesDébiteurs
> ON Cheque.IDCHEQUEDEBITEUR = PersonnesDébiteurs.IDDEBITEUR
> WHERE
> Cheque.NSF="1"
> AND Cheque.chequeRemis="1"
> AND Cheque.refNSF>0
> AND Cheque.chkDossier="1"
> AND Cheque.depot)="1"
> AND Cheque.validationDepot="1"
> AND Cheque.percepteur="Fiducie";
>
>
> And finally, I'll show you the DDL of Cheque
>
>
> CREATE GENERATOR G_CHEQUEIDCHEQUEGEN1;
>
> CREATE TABLE CHEQUE (
> NOCHEQUE VARCHAR(50) NOT NULL,
> IDCHEQUEDEBITEUR INTEGER NOT NULL,
> IDGROUPE VARCHAR(50) NOT NULL,
> IDDOSSIER INTEGER NOT NULL,
> DATEDEPOT DATE NOT NULL,
> IDCHEQUE INTEGER NOT NULL,
> "montant" NUMERIC(8,2),
> "percepteur" VARCHAR(50),
> NSF CHAR(1) DEFAULT 0 NOT NULL,
> "validationDepot" CHAR(1) DEFAULT 0 NOT NULL,
> "depot" CHAR(1) DEFAULT 0 NOT NULL,
> "chequeRemis" CHAR(1) DEFAULT 0 NOT NULL,
> "chkDossier" CHAR(1) DEFAULT 0 NOT NULL,
> "leType" CHAR(1) DEFAULT 0 NOT NULL,
> "ConcYesNo" CHAR(1) DEFAULT 0 NOT NULL,
> "Concdate" DATE,
> "montantClient" NUMERIC(8,2),
> "montantHN" NUMERIC(8,2),
> "description" VARCHAR(50),
> "intrust" CHAR(1) DEFAULT 0 NOT NULL,
> "refIntrust" VARCHAR(50) DEFAULT 0,
> "groupeCheque" VARCHAR(50) DEFAULT 0,
> "groupeChequeHN" VARCHAR(50) DEFAULT 0,
> "groupeChequeHonoraire" VARCHAR(50) DEFAULT 0,
> "groupeChequeClient" VARCHAR(50) DEFAULT 0,
> "refNSF" INTEGER,
> "MontantClient_Dispo" CHAR(1) DEFAULT 0 NOT NULL,
> "HNFrais_Dispo" CHAR(1) DEFAULT 0 NOT NULL,
> "encaisse" CHAR(1) DEFAULT 0 NOT NULL,
> "dateNSF" DATE
> );
>
> ALTER TABLE CHEQUE ADD CONSTRAINT PK_CHEQUE PRIMARY KEY (IDGROUPE,
> IDDOSSIER, IDCHEQUEDEBITEUR, DATEDEPOT, NOCHEQUE)
> USING INDEX "chequeIndicePK";
>
> ALTER TABLE CHEQUE ADD CONSTRAINT CHEQUEKEY0 FOREIGN KEY (IDDOSSIER,
> IDGROUPE) REFERENCES DOSSIERS (IDDOSSIER, IDGROUPE);
> ALTER TABLE CHEQUE ADD CONSTRAINT CHEQUEKEY1 FOREIGN KEY
> (IDCHEQUEDEBITEUR) REFERENCES PERSONNESDEBITEURS (IDDEBITEUR);
> ALTER TABLE CHEQUE ADD CONSTRAINT CHEQUEKEY2 FOREIGN KEY (IDDOSSIER,
> IDGROUPE, IDCHEQUEDEBITEUR) REFERENCES DOSSIERSDEBITEURS (IDDOSSIER,
> IDGROUPE, IDDEBITEUR) ON UPDATE NO ACTION;
>
> CREATE INDEX CHEQUE_IDX1 ON CHEQUE (IDDOSSIER);
> CREATE INDEX CHEQUE_IDX2 ON CHEQUE ("Concdate");
> CREATE INDEX CHEQUE_IDX3 ON CHEQUE (DATEDEPOT);
> CREATE INDEX CHEQUE_IDX4 ON CHEQUE (IDCHEQUE);
> CREATE INDEX CHEQUE_IDX5 ON CHEQUE ("refNSF");