Hi,
in late november i posted some questions about simple queries with joins.
Sometimes they were fast(indexes are used) and sometimes they were slow
(natural join is used).
(Message:Performance differences from 25.11.2004)
Now i have a query were indexes are used but it take over 21minutes on my
machine (Firebird 1.5.2, WinXP, on Pentium 4, 2.66Ghz, 1GB RAM).
SELECT count(t_apos.id_apos)
FROM t_apos
JOIN t_auftrag ON f_id_auftrag + 0 = id_auftrag
WHERE t_apos.f_id_anliefer = 10
AND t_auftrag.f_adatum between '07.12.2004 00:00' AND '08.01.2005 11:23'
AND t_apos.f_id_auftrag + 0 != 0
PLAN JOIN (T_AUFTRAG INDEX (IND_AUFTRAG_ADATUM),T_APOS INDEX
(RDB$FOREIGN62))
RDB$FOREIGN62 = foreign key on t_apos.f_id_anliefer
PlanAnalyzer says that Firebird need 82.194.000 indexed reads on t_apos
and 2.280 indexed reads on t_auftrag
Result of count is 769,
t_apos has 221.063 rows and t_auftrag has 97.751 rows.

Is this normal behaviour of Firebird ?
I can't believe that someone can use Firebird for reports where the user can
make his own query, because it's so easy to break firebird.


mfg
Carsten


Table definitions:
CREATE TABLE T_APOS (
ID_APOS INTEGER NOT NULL,
F_ID_RUECK INTEGER NOT NULL,
F_ID_ANLIEFER INTEGER NOT NULL,
F_ID_RUVER INTEGER NOT NULL,
F_ID_ANVER INTEGER NOT NULL,
F_ID_VORSCHRIFT INTEGER NOT NULL,
F_ID_AUFTRAG INTEGER NOT NULL,
F_ID_ANGEBOT INTEGER NOT NULL,
F_ID_LAGERPOSZUORD INTEGER NOT NULL,
F_TEILNR VARCHAR(150),
F_BEZEICHNUNG VARCHAR(200),
F_ID_WERKSTOFF INTEGER NOT NULL,
F_GEOFAKTOR INTEGER,
F_ID_ARBEITSPLAN INTEGER NOT NULL,
F_KH_VON_SOLL DOUBLE PRECISION,
F_KH_BIS_SOLL DOUBLE PRECISION,
F_KH_EINHEIT_SOLL VARCHAR(15),
F_KH_VON_IST DOUBLE PRECISION,
F_KH_BIS_IST DOUBLE PRECISION,
F_MENGE INTEGER,
F_EM DOUBLE PRECISION,
F_GEWICHT DOUBLE PRECISION,
F_ZEICHNR VARCHAR(100),
F_ZEICHDATUM DATE,
F_KOMMENTAR VARCHAR(1000),
F_LIEFERTERMIN DATE,
F_SPLITSTATUS VARCHAR(100),
F_AUSLIEFER_MENGE INTEGER,
F_RECHNUNGSTELLUNG DATE,
F_ANZAHL_TBK INTEGER,
F_FULL_CHECK INTEGER,
F_T_LIEFER INTEGER,
F_ID_MA_ACT INTEGER NOT NULL,
F_TIEFE_VON_SOLL DOUBLE PRECISION,
F_TIEFE_BIS_SOLL DOUBLE PRECISION,
F_TIEFE_VON_IST DOUBLE PRECISION,
F_TIEFE_BIS_IST DOUBLE PRECISION,
F_BEST_NR VARCHAR(30),
F_BEST_DATUM DATE,
F_KOM_LIEFER VARCHAR(1000),
F_STATUS INTEGER,
F_GH_VON_SOLL DOUBLE PRECISION,
F_GH_BIS_SOLL DOUBLE PRECISION,
F_GH_VON_IST DOUBLE PRECISION,
F_GH_BIS_IST DOUBLE PRECISION,
F_RH_VON_SOLL DOUBLE PRECISION,
F_RH_BIS_SOLL DOUBLE PRECISION,
F_RH_VON_IST DOUBLE PRECISION,
F_RH_BIS_IST DOUBLE PRECISION,
F_RC_VON_SOLL DOUBLE PRECISION,
F_RC_VON_IST DOUBLE PRECISION,
F_GH_EINHEIT_SOLL VARCHAR(15),
F_RH_EINHEIT_SOLL VARCHAR(15),
F_POSITION INTEGER,
F_LIEFER_NR VARCHAR(32),
F_RECH_NR VARCHAR(32),
F_ANR_KUNDE VARCHAR(32),
F_PRUEFZEUGNISART INTEGER,
F_FOLGEBEHANDLUNG INTEGER,
F_RH_EINHEIT_IST VARCHAR(15),
F_KH_EINHEIT_IST VARCHAR(15),
F_GH_EINHEIT_IST VARCHAR(15),
F_ID_FREMDAUFTRAG INTEGER default 0 NOT NULL,
F_RC_BIS_SOLL DOUBLE PRECISION DEFAULT -1,
F_RC_BIS_IST DOUBLE PRECISION DEFAULT -1,
F_RC_TIEFE_VON_SOLL DOUBLE PRECISION DEFAULT -1,
F_RC_TIEFE_BIS_SOLL DOUBLE PRECISION DEFAULT -1,
F_RC_TIEFE_VON_IST DOUBLE PRECISION DEFAULT -1,
F_RC_TIEFE_BIS_IST DOUBLE PRECISION DEFAULT -1,
F_DEACTIVATION TIMESTAMP,
F_AUSLIEFERUNG TIMESTAMP,
F_LIEFERSCHEIN_DATUM TIMESTAMP,
F_ANZANLIEFERVERPACKUNGEN INTEGER default 1,
F_FREIGABENOETIG INTEGER default 0,
F_FREIGABEDATUM TIMESTAMP,
F_ID_FREIGABEMITARBEITER INTEGER default 0,
F_ID_POSITIONSSERIE INTEGER default 0 NOT NULL,
F_RECH_KOMMENTAR VARCHAR(1000),
F_ANGEBOTNR VARCHAR(25),
F_AUSLIEFERUNG_DATUM TIMESTAMP,
F_AUSLIEFERUNG_NR VARCHAR(25),
F_PROFORMALIEFERSCHEIN INTEGER,
F_PRUEFZEUGNIS_DATUM TIMESTAMP,
F_PRUEFZEUGNIS_KOMMENTAR VARCHAR(1000),
F_MITSONDERFREIGABE INTEGER default 0 NOT NULL,
F_KOSTENLOS INTEGER DEFAULT 0 NOT NULL,
F_KOSTENLOS_MITPOS INTEGER DEFAULT 0 NOT NULL,
F_ID_MA_KOSTENLOS INTEGER DEFAULT 0 NOT NULL,
F_DAT_KOSTENLOS TIMESTAMP,
F_MITAUFTRAGSKLARSTELLUNG INTEGER default 0 NOT NULL,
F_OHNEEMAILVERSAND INTEGER default 0 NOT NULL,
F_ID_APOSDERNACHARBEIT INTEGER default 0 NOT NULL,
F_ID_APOSNACHARBEITFUER INTEGER default 0 NOT NULL,
F_APOSNRDERNACHARBEIT VARCHAR(25),
F_PRUEFZEUGNIS_MA INTEGER default 0 NOT NULL,
F_DAT_ERSTELLUNG TIMESTAMP,
F_ID_MA_ERSTELLER INTEGER NOT NULL
);

ALTER TABLE T_APOS ADD PRIMARY KEY (ID_APOS);

ALTER TABLE T_APOS ADD CONSTRAINT APOSANLIEFERTRANS FOREIGN KEY
(F_ID_ANLIEFER) REFERENCES T_TRANSPORT (ID_TRANSPORT) ON DELETE NO ACTION ON
UPDATE NO ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT APOSANLIEFERVERP FOREIGN KEY (F_ID_ANVER)
REFERENCES T_VERPACKUNG (ID_VERPACKUNG) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT APOSAPLAN FOREIGN KEY (F_ID_ARBEITSPLAN)
REFERENCES T_ARBEITSPLAN (ID_ARBEITSPLAN) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT APOSLAGERPOS FOREIGN KEY
(F_ID_LAGERPOSZUORD) REFERENCES T_LAGERPOSZUORD (ID_LAGERPOSZUORD) ON DELETE
NO ACTION ON UPDATE NO ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT APOSMIT FOREIGN KEY (F_ID_MA_ACT)
REFERENCES T_MITARBEITER (ID_MITARBEITER) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT APOSSER FOREIGN KEY (F_ID_POSITIONSSERIE)
REFERENCES T_POSITIONSSERIE (ID_POSITIONSSERIE) ON DELETE NO ACTION ON
UPDATE NO ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT APOSTRANS FOREIGN KEY (F_ID_RUECK)
REFERENCES T_TRANSPORT (ID_TRANSPORT) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT APOSVERP FOREIGN KEY (F_ID_RUVER)
REFERENCES T_VERPACKUNG (ID_VERPACKUNG) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT APOSVOR FOREIGN KEY (F_ID_VORSCHRIFT)
REFERENCES T_BEHVORSCHRIFT (ID_BEHVORSCHRIFT) ON DELETE NO ACTION ON UPDATE
NO ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT APOSWERK FOREIGN KEY (F_ID_WERKSTOFF)
REFERENCES T_WERKSTOFF (ID_WERKSTOFF) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT FK_APOS_ID_AUFTRAG FOREIGN KEY
(F_ID_AUFTRAG) REFERENCES T_AUFTRAG (ID_AUFTRAG) ON DELETE NO ACTION ON
UPDATE NO ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT FK_APOS_MA_ERSTELLER FOREIGN KEY
(F_ID_MA_ERSTELLER) REFERENCES T_MITARBEITER (ID_MITARBEITER) ON DELETE
CASCADE ON UPDATE NO ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT FK_APOS_MA_KL FOREIGN KEY
(F_ID_MA_KOSTENLOS) REFERENCES T_MITARBEITER (ID_MITARBEITER) ON DELETE
CASCADE ON UPDATE NO ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT FK_APOS_MA_PZ FOREIGN KEY
(F_PRUEFZEUGNIS_MA) REFERENCES T_MITARBEITER (ID_MITARBEITER) ON DELETE
CASCADE ON UPDATE NO ACTION;
ALTER TABLE T_APOS ADD CONSTRAINT FREMDAUFTRAGAPOS FOREIGN KEY
(F_ID_FREMDAUFTRAG) REFERENCES T_FREMDAUFTRAG (ID_FREMDAUFTRAG) ON DELETE NO
ACTION ON UPDATE NO ACTION;
ALTER TABLE T_APOS ADD FOREIGN KEY (F_ID_FREIGABEMITARBEITER) REFERENCES
T_MITARBEITER (ID_MITARBEITER);

CREATE INDEX APOS_LIEFERNR ON T_APOS (F_LIEFER_NR);
CREATE INDEX IND_APOS_AUSLIEFERUNG ON T_APOS (F_AUSLIEFERUNG);
CREATE DESCENDING INDEX IND_APOS_ID ON T_APOS (ID_APOS);
CREATE INDEX IND_APOS_LIEFERTERMIN ON T_APOS (F_LIEFERTERMIN);



CREATE TABLE T_AUFTRAG (
ID_AUFTRAG INTEGER NOT NULL,
F_AUFTRAGNR VARCHAR(20) NOT NULL,
F_ID_KUNDE INTEGER NOT NULL,
F_LS_NR VARCHAR(30),
F_LS_DATUM DATE,
F_ID_MA INTEGER NOT NULL,
F_KOMMENTAR VARCHAR(1000),
F_KOM_LIEFER VARCHAR(1000),
F_ID_MA_ACT INTEGER NOT NULL,
F_ACTIVE INTEGER,
F_T_LIEFER INTEGER,
F_T_RECHNUNG INTEGER,
F_STATUS INTEGER,
F_ID_SERIE INTEGER NOT NULL,
F_DEACTIVATION TIMESTAMP,
F_ADATUM TIMESTAMP
);

ALTER TABLE T_AUFTRAG ADD UNIQUE (F_AUFTRAGNR);

ALTER TABLE T_AUFTRAG ADD PRIMARY KEY (ID_AUFTRAG);

ALTER TABLE T_AUFTRAG ADD CONSTRAINT AUFSER FOREIGN KEY (F_ID_SERIE)
REFERENCES T_SERIE (ID_SERIE) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE T_AUFTRAG ADD CONSTRAINT AUFTRAGMIT FOREIGN KEY (F_ID_MA)
REFERENCES T_MITARBEITER (ID_MITARBEITER) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE T_AUFTRAG ADD CONSTRAINT AUFTRAGMITACT FOREIGN KEY (F_ID_MA_ACT)
REFERENCES T_MITARBEITER (ID_MITARBEITER) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE T_AUFTRAG ADD CONSTRAINT FS_KUNDE_AUFTRAG FOREIGN KEY
(F_ID_KUNDE) REFERENCES T_KUNDE (ID_KUNDE) ON DELETE NO ACTION ON UPDATE NO
ACTION;

CREATE INDEX IND_AUFTRAG_ADATUM ON T_AUFTRAG (F_ADATUM);
CREATE DESCENDING INDEX IND_AUFTRAG_ID ON T_AUFTRAG (ID_AUFTRAG);