Still no changes. This looks to me as a riddle.

Djordje

From: mailto:firebird-support@yahoogroups.com
Sent: Friday, September 09, 2016 6:09 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Optimizer request



Try:


select orders.*

from

(select partid from partners where partners.country = ‘Spain’) T

JOIN orders ON orders.partid = T.partid


From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: September 9, 2016 12:06 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Optimizer request







Same result. No changes. Perfomance analyzer gives same result.


Djordje


From: mailto:firebird-support@yahoogroups.com

Sent: Friday, September 09, 2016 5:10 PM

To: firebird-support@yahoogroups.com

Subject: Re: [firebird-support] Optimizer request




Never use IN (subselect). Change to


select * from orders where exists( select * from partners where partners.partid = orders.partid and partners.country = ‘Spain’)


2016-09-09 14:30 GMT+02:00 'Djordje Radovanovic' softsistem@... [firebird-support] <firebird-support@yahoogroups.com>:


I tried query with subquery in where clause and found big issue for this type of subquery.


for example:


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 clouse has priority (and there is no way to be opposite) than we have big improvement in optimization.


Best regards,


Djordje Radovanovic













[Non-text portions of this message have been removed]