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

__,_._,_