Subject Re: [firebird-support] Optimizer request
Author Djordje Radovanovic
Still no changes. This looks to me as a riddle.


Sent: Friday, September 09, 2016 6:09 PM
Subject: RE: [firebird-support] Optimizer request


select orders.*


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

JOIN orders ON orders.partid = T.partid

From: []
Sent: September 9, 2016 12:06 PM
Subject: Re: [firebird-support] Optimizer request

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



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


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 = ‘Spain’)

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

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 = ‘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]