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
);




/******************************************************************************/
/**** Primary Keys
****/
/******************************************************************************/

ALTER TABLE CHEQUE ADD CONSTRAINT PK_CHEQUE PRIMARY KEY (IDGROUPE,
IDDOSSIER, IDCHEQUEDEBITEUR, DATEDEPOT, NOCHEQUE)
USING INDEX "chequeIndicePK";


/******************************************************************************/
/**** Foreign Keys
****/
/******************************************************************************/

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;


/*****************************/
/****Indices **/
/*****************************/

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");

----- Original Message -----
From: Svein Erling Tysvær
To: firebird-support@yahoogroups.com
Sent: Monday, June 06, 2005 2:18 PM
Subject: [firebird-support] Re: Why the JOIN is soooo long?


Hmm, I wonder what tool you are using, I've never seen that syntax
before. Assuming

ON "" & Cheque.IDCHEQUE= "" & RapportFiduciePart5.refNSF

translates to

ON '' || Cheque.IDCHEQUE = '' || RapportFiduciePart5.refNSF

you effectively prevents using indexes on IDCHEQUE and refNSF.
Preventing indexes from being used is sometimes a good option, but I'd
be happier if I knew why preventing those indexes helps, and to
understand that I need to see a PLAN. Please show us the PLAN Firebird
creates. Also, you say that RapportFiduciePart5 is a query, did you
mean view or stored procedure? Can you show us the source?

Set

--- In firebird-support@yahoogroups.com, "mona yazbeck" wrote:
> Both fields are indexes.
>
> This query works fine on "text" field... And when I change my query
> for a INNER JOIN like this:
>
> INNER JOIN RapportFiduciePart5
> ON "" & Cheque.IDCHEQUE= "" & RapportFiduciePart5.refNSF;
>
> It's a lot faster! (But still about 20 seconds.... (better compared
> to an endless query)
>
> SELECT Cheque.DATEDEPOT, RapportFiduciePart5.IDGROUPE,
> RapportFiduciePart5.IDDOSSIER, RapportFiduciePart5.NOCHEQUE,
> RapportFiduciePart5.montantClient AS MontantTot,
> RapportFiduciePart5.chequeRemis, RapportFiduciePart5.nomComplet,
> RapportFiduciePart5.DATEDEPOT AS DateDuDepotDuCheque,
> RapportFiduciePart5.IDCHEQUE
> FROM Cheque
> INNER JOIN RapportFiduciePart5
> ON Cheque.IDCHEQUE= RapportFiduciePart5.refNSF;
>
> IDCHEQUE and refNSF are both INTEGER. When I do a similar query with
> One integer and one Text Field using "" & integer = text, it works
> great.
>
> RapportFiduciePart5 is a Query and alone, it runs pretty fast
> Cheque is a Table




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




--------------------------------------------------------------------------------
Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.