CREATE TABLE PARTNERS (
PARTID INTEGER NOT NULL,
COUNTRY CHAR(20)
);
ALTER TABLE PARTNERS ADD CONSTRAINT PK_PARTNERS PRIMARY KEY (PARTID);
CREATE INDEX PARTNERS_IDX1 ON PARTNERS (COUNTRY);
CREATE TABLE ORDERS (
ORDERNUM INTEGER NOT NULL,
ORDERDATE DATE,
AMOUNT DECIMAL(18,2),
PARTID INTEGER
);
ALTER TABLE ORDERS ADD CONSTRAINT PK_ORDERS PRIMARY KEY (ORDERNUM);
ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_PARTNERS FOREIGN KEY (PARTID)
REFERENCES PARTNERS (PARTID) ON DELETE NO ACTION ON UPDATE NO ACTION;
This is tables definitions. So, index exist and after all perfomance analyzer
gives me same result.
Perfomance analyzer was taken from IBExpert. Whatever I do (as others suggest)
perfomance are the same. I still vote for changes in optimizer. Right side from
WHERE clause needs to be evaluated before main query and it is simple and much
better solution same as Oracle optimize query.
Firebird is very kind to programmer and has easy to understand, efficient P/SQL
and some improvements will give us significant efficiency.
.
Djordje
From: mailto:[email protected]
Sent: Friday, September 09, 2016 10:51 PM
To: [email protected]
Subject: Re: [firebird-support] Optimizer request
On Fri, Sep 9, 2016 at 8:30 AM, 'Djordje Radovanovic' [email protected]
[firebird-support] <[email protected]> wrote:
I tried query with subquery in where clause and found big issue for this type
of subquery.
select * from orders where orders.partid in (select partners.partid from
partners where partners.country = ‘Spain’)
Perfomance Analysis returns me this
partners 687660 non index reads
orders 28657 index reads
If you analyze this result you’ll find that there is 687659 unnecessary non
index reads. If developer of optimizer accept that all queries on the left side
of where clause has priority (and there is no way to be opposite) than we have
big improvement in optimization.
Is there an index on partners.country? What plans are generated for each query?
If I were writing this query, I'd write
select o.*
from orders o
inner join partners p
where p.partid = o.opartid
and p.country = 'Spain';
All that silliness about "select from (select from )" probably doesn't save
anything in
this case - it does when the inner select is complicated, but not here.
Firebird won't
carry around unneeded fields from the partners table.
What is the distribution of partners.country?
Good luck,
Ann
__,_._,_
