I found two situation, where optimizer use bad plan or when FB lost speed.
I not need help, I only report tips for improvement FB server.

First (SQL example sql1):
AB indexd is needed for Max/Min functions in real database.
FB select index AB, but if I plan manualy index AA, avarege speed was incresed.

Second (SQL examlpe sql2):
Slow evaluating of IN (BETWEEN, OR, ...) clausule.
When I roll up into UNIONS speed was radicaly incresed.

Slavek

sql1:

CREATE DATABASE "localhost:f:\test.gdb" USER "sysdba" PASSWORD "masterkey";
CREATE TABLE T(
id INT,
tDate TIMESTAMP
);

SET TERM ^;

CREATE PROCEDURE Fill AS
DECLARE VARIABLE i INT;
DECLARE VARIABLE d TIMESTAMP;
BEGIN
i=-90;
WHILE(i<90)DO BEGIN
d='1.1.2001';
WHILE(d<'1.1.2003') DO BEGIN
INSERT INTO T VALUES(:i,:d);
d=d+0.05;
END
i=i+1;
END
END^

SET TERM ;^

EXECUTE PROCEDURE Fill;
CREATE INDEX AA ON T(id,tDate);
CREATE INDEX AB ON T(tDate,id);

SELECT COUNT(*) FROM T;

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=0
ORDER BY tDate;

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=0
PLAN (T INDEX (AA))
ORDER BY tDate;

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=100
ORDER BY tDate;

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=100
PLAN (T INDEX (AA))
ORDER BY tDate;

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=-100
ORDER BY tDate;

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=-100
PLAN (T INDEX (AA))
ORDER BY tDate;

sql2:
SET STATS;
SET PLAN;
CREATE DATABASE "localhost:f:\test_in.gdb" USER "sysdba" PASSWORD "masterkey";

CREATE TABLE T(
id INT,
tDate TIMESTAMP
);

CREATE INDEX AA ON T(id,tDate);
SET TERM ^;

CREATE PROCEDURE Fill AS
DECLARE VARIABLE i INT;
DECLARE VARIABLE d TIMESTAMP;
BEGIN
i=-90;
WHILE(i<90)DO BEGIN
d='1.1.2001';
WHILE(d<'1.1.2003') DO BEGIN
INSERT INTO T VALUES(:i,:d);
d=d+0.05;
END
i=i+1;
END
END^

SET TERM ;^
EXECUTE PROCEDURE Fill;
SELECT COUNT(*) FROM T;

SELECT id FROM T WHERE id=0 AND tDate BETWEEN '1.1.2000' AND '2.1.2000';

SELECT id FROM T WHERE id IN(0,1) AND tDate BETWEEN '1.1.2000' AND '2.1.2000';

SELECT id FROM T WHERE id=0 AND tDate BETWEEN '1.1.2000' AND '2.1.2000'
UNION
SELECT id FROM T WHERE id=1 AND tDate BETWEEN '1.1.2000' AND '2.1.2000';

SELECT id FROM T WHERE id=0 AND tDate BETWEEN '1.1.2004' AND '2.1.2004';

SELECT id FROM T WHERE id IN(0,1) AND tDate BETWEEN '1.1.2004' AND '2.1.2004';

SELECT id FROM T WHERE id=0 AND tDate BETWEEN '1.1.2004' AND '2.1.2004'
UNION
SELECT id FROM T WHERE id=1 AND tDate BETWEEN '1.1.2004' AND '2.1.2004';


ing. Slavomir Skopalik DEL a.s.
Olomoucka 355 Marianske udoli 783 75
Czech Republic
----------------------------------------------
Tel: 585 353 548
Mobil: 602 795 874
Fax: 585 352 364
e-mail:skopalik@...
http://hlubocky.del.cz