Subject Re: [firebird-support] Optimizer request
Author Djordje Radovanovic
Post date 2016-09-11T08:29:32Z
This one makes significiant change.
 
This time instead of 10000 reads of orders and 10000 reads of partners I received exactly what I was asking for.
 
133 reads of parters table
1335 of orders table
 
Thank you Arno. Please can you explain me part on the right side of FROM? Somewhere in my subconsciousness I have in mind that something like this is doable but never read good explanation of that kind of virtual table.
 
Best reagrds,
 
Djordje Radovanovic
 
Sent: Sunday, September 11, 2016 12:11 AM
Subject: Re: [firebird-support] Optimizer request
 
 

Which Firebird version are you using?
 
 
Does this perform for you as expected:
 
SELECT
  *
FROM
  (SELECT partners.partid + 0 AS PartID FROM partners WHERE partners.country = 'Spain') dt
  JOIN orders ON (orders.partid = dt.partid)
 
 
 
Kind Regards,
Arno Brinkman
 
Sent: Saturday, September 10, 2016 10:05 PM
Subject: Re: [firebird-support] Optimizer request
 


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
 
Sent: Friday, September 09, 2016 10:51 PM
Subject: Re: [firebird-support] Optimizer request
 
 

On Fri, Sep 9, 2016 at 8:30 AM, 'Djordje Radovanovic' softsistem@... [firebird-support] <firebird-support@yahoogroups.com> 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
__,_._,_