[firebird-support] Optimizer request

['Djordje Radovanovic' [e-mail] [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 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

[Dimitry Sibiryakov [e-mail] [firebird-support]]

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
I tried query with subquery in where clause and found big issue for this type of subquery.

That's why using of JOIN instead of IN is recommended whenever possible.
--
WBR, SD.


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

['Jeremy Poppleton' [e-mail] [firebird-support]]

In is slow.

Better to optimize the query like this:



Select o.*

From orders o

Inner join

(

Select

p.Partid

From

Partners p

Where

p.country = SPAIN

}as partidentity

On partidentity.Partid = o.PartID



Which should be faster than a straight join i.e



Select o.* from orders o inner join partners p on p.Partid = o.partid

Where p.country = SPAIN

Jez

['Djordje Radovanovic' [e-mail] [firebird-support]]

Ok. I did not have example like this (I put it as easy to understand example) but I was curious. Is this kind of clause makes any difference? I made my test database with tables with same fields as in my example. I made relation between tables. I filled orders table with 10000 records and partners table with 1000 records. Than I made query same as in my example and received 10000 reads from orders and 10000 reads from partners where orders were Non-index reads and partner were Index reads. Than I try this Join cluse and guess what? I received EXACTLY THE SAME PERFOMANCE ANALYSIS.

Maybe it is something wrong with JOIN clause or maybe I am right...

Regards,

Djole

From: mailto:firebird-***@yahoogroups.com
Sent: Friday, September 09, 2016 3:01 PM
To: firebird-***@yahoogroups.com
Subject: [firebird-support] Re: Optimizer request



In is slow.

Better to optimize the query like this:


Select o.*

From orders o

Inner join

(

Select

p.Partid

From

Partners p

Where

p.country = SPAIN

}as partidentity

On partidentity.Partid = o.PartID


Which should be faster than a straight join i.e


Select o.* from orders o inner join partners p on p.Partid = o.partid

Where p.country = SPAIN

Jez

[Svein Erling Tysvær [e-mail] [firebird-support]]

Never use IN (subselect). Change to

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

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
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 clouse has priority (and there is no way to be opposite)
than we have big improvement in optimization.
Best regards,
Djordje Radovanovic

['Djordje Radovanovic' [e-mail] [firebird-support]]

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

Djordje

From: mailto:firebird-***@yahoogroups.com
Sent: Friday, September 09, 2016 5:10 PM
To: firebird-***@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' ***@sbb.rs [firebird-support] <firebird-***@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

['Leyne, Sean' [e-mail] [firebird-support]]

Try:

select orders.*
from
(select partid from partners where partners.country = ‘Spain’) T
JOIN orders ON orders.partid = T.partid

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




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

Djordje

From: mailto:firebird-***@yahoogroups.com
Sent: Friday, September 09, 2016 5:10 PM
To: firebird-***@yahoogroups.com<mailto:firebird-***@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' ***@sbb.rs<mailto:***@sbb.rs> [firebird-support] <firebird-***@yahoogroups.com<mailto:firebird-***@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

['Djordje Radovanovic' [e-mail] [firebird-support]]

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

Djordje

From: mailto:firebird-***@yahoogroups.com
Sent: Friday, September 09, 2016 6:09 PM
To: firebird-***@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-***@yahoogroups.com [mailto:firebird-***@yahoogroups.com]
Sent: September 9, 2016 12:06 PM
To: firebird-***@yahoogroups.com
Subject: Re: [firebird-support] Optimizer request







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


Djordje


From: mailto:firebird-***@yahoogroups.com

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

To: firebird-***@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' ***@sbb.rs [firebird-support] <firebird-***@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]

['Leyne, Sean' [e-mail] [firebird-support]]

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
Still no changes. This looks to me as a riddle.
select orders.*
from
(select partid from partners where partners.country = ‘Spain’) T
JOIN orders ON orders.partid = T.partid

Do you have an index on partners.country?

If not, how do you expect the system to optimize the search?

The engine would need to perform a scan of the partners table...



Sean

[[e-mail] [firebird-support]]

I'm continuing in old conversation because I have similar question.




How to avoid using IN(subselect) in UPDATE?




Theoretical example:




update orders o
set o.something = 'something other'
where o.delivery_date is null
  and o.partid in (select p.partid from partners p where p.country='Spain')





I'm using FB 2.5.x


Right now I don't have big real data for testing.
I'm just wondering because from what I understand from here then for every
row of orders firebird would make that constant subselect again and again.
Am I right?

Would using EXISTS() help?




E.



---------- Původní zpráva ----------
Od: Svein Erling TysvÊr ***@gmail.com [firebird-support] <firebird-
***@yahoogroups.com>
Komu: firebird-***@yahoogroups.com
Datum: 9. 9. 2016 17:10:21
Předmět: 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' ***@sbb.rs
(mailto:***@sbb.rs) [firebird-support] <firebird-***@yahoogroups.
com(mailto:firebird-***@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

 

 







"








"=

[Virgo Pärna [e-mail] [firebird-support]]

Post by ***@seznam.cz [firebird-support]
update orders o
set o.something = 'something other'
where o.delivery_date is null
  and o.partid in (select p.partid from partners p where p.country='Spain')
Would using EXISTS() help?

IIRC Firebird will optimize IN to EXISTS anyway, if possible.
Only way to really optimize this kind of update would be with
stored procedure or EXECUTE BLOCK. Like:

execute block as
declare variable partid integer;
begin
for select p.partid
from partners p
where p.country='Spain'
into :partid do
begin
update orders o set o.something = 'something other'
where o.delivery_date is null and o.partid = :partid;
end
end

['Arno Brinkman' [e-mail] [firebird-support]]

Hi,

As others already suggested i would also prefer EXISTS, but anyway there is missing an index on Country.

The IN sub-select is not taking into cache and then used for the index on orders.partid.
If you want that optimalisation then go for a derived table to join against.

Kind Regards,
Arno Brinkman



From: mailto:firebird-***@yahoogroups.com
Sent: Friday, September 9, 2016 2:30 PM
To: firebird-***@yahoogroups.com
Subject: [firebird-support] Optimizer request




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

[Louis Kleiman [e-mail] [firebird-support]]

How about this:

select O.*
from orders O
join partners P on (P.partid = O.partid)
where P.country = 'Spain'

Post by 'Arno Brinkman' ***@abvisie.nl [firebird-support]
Hi,
As others already suggested i would also prefer EXISTS, but anyway there
is missing an index on Country.
The IN sub-select is not taking into cache and then used for the index on orders.partid.
If you want that optimalisation then go for a derived table to join against.
Kind Regards,
Arno Brinkman
*Sent:* Friday, September 9, 2016 2:30 PM
*Subject:* [firebird-support] Optimizer request
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 clouse has priority (and there is no way to be opposite)
than we have big improvement in optimization.
Best regards,
Djordje Radovanovic

[Ann Harrison [e-mail] [firebird-support]]

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
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

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
__,_._,_

[Helen Borrie [e-mail] [firebird-support]]

Hello Ann,

Post by Ann Harrison ***@ibphoenix.com [firebird-support]
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';

I'm sure this was a slip of the pen, Ann... you meant

select o.* 
     from orders o
            inner join partners p 
     on p.partid = o.opartid
      where p.country = 'Spain';

H.

[Ann Harrison [e-mail] [firebird-support]]

Post by Ann Harrison ***@ibphoenix.com [firebird-support]
select o.*
from orders o
inner join partners p

* where p.partid = o.opartid > and p.country = 'Spain*';
select o.*
from orders o
inner join partners p
* on p.partid = o.opartid*
* where *p.country = 'Spain';

which is more correct. But the firebird optimizer is smart enough to find
join
terms, even when misplaced, so the two generate the same plans. Again,
with more complex queries, results may differ.

Cheers,

Ann

['Djordje Radovanovic' [e-mail] [firebird-support]]

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



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

[[e-mail] [firebird-support]]

Thank you 

Sent from Yahoo Mail. Get the app

On Saturday, September 10, 2016 10:04 PM, "'Djordje Radovanovic' ***@sbb.rs [firebird-support]" <firebird-***@yahoogroups.com> wrote:


  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:firebird-***@yahoogroups.com Sent: Friday, September 09, 2016 10:51 PMTo: firebird-***@yahoogroups.com Subject: Re: [firebird-support] Optimizer request   On Fri, Sep 9, 2016 at 8:30 AM, 'Djordje Radovanovic' ***@sbb.rs [firebird-support] <firebird-***@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 inthis case - it does when the inner select is complicated, but not here.  Firebird won'tcarry around unneeded fields from the partners table. What is the distribution of partners.country? Good luck, Ann
__,_._,_
#yiv1810376193 #yiv1810376193 -- #yiv1810376193ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1810376193 #yiv1810376193ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1810376193 #yiv1810376193ygrp-mkp #yiv1810376193hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1810376193 #yiv1810376193ygrp-mkp #yiv1810376193ads {margin-bottom:10px;}#yiv1810376193 #yiv1810376193ygrp-mkp .yiv1810376193ad {padding:0 0;}#yiv1810376193 #yiv1810376193ygrp-mkp .yiv1810376193ad p {margin:0;}#yiv1810376193 #yiv1810376193ygrp-mkp .yiv1810376193ad a {color:#0000ff;text-decoration:none;}#yiv1810376193 #yiv1810376193ygrp-sponsor #yiv1810376193ygrp-lc {font-family:Arial;}#yiv1810376193 #yiv1810376193ygrp-sponsor #yiv1810376193ygrp-lc #yiv1810376193hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1810376193 #yiv1810376193ygrp-sponsor #yiv1810376193ygrp-lc .yiv1810376193ad {margin-bottom:10px;padding:0 0;}#yiv1810376193 #yiv1810376193actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1810376193 #yiv1810376193activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1810376193 #yiv1810376193activity span {font-weight:700;}#yiv1810376193 #yiv1810376193activity span:first-child {text-transform:uppercase;}#yiv1810376193 #yiv1810376193activity span a {color:#5085b6;text-decoration:none;}#yiv1810376193 #yiv1810376193activity span span {color:#ff7900;}#yiv1810376193 #yiv1810376193activity span .yiv1810376193underline {text-decoration:underline;}#yiv1810376193 .yiv1810376193attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1810376193 .yiv1810376193attach div a {text-decoration:none;}#yiv1810376193 .yiv1810376193attach img {border:none;padding-right:5px;}#yiv1810376193 .yiv1810376193attach label {display:block;margin-bottom:5px;}#yiv1810376193 .yiv1810376193attach label a {text-decoration:none;}#yiv1810376193 blockquote {margin:0 0 0 4px;}#yiv1810376193 .yiv1810376193bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1810376193 .yiv1810376193bold a {text-decoration:none;}#yiv1810376193 dd.yiv1810376193last p a {font-family:Verdana;font-weight:700;}#yiv1810376193 dd.yiv1810376193last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1810376193 dd.yiv1810376193last p span.yiv1810376193yshortcuts {margin-right:0;}#yiv1810376193 div.yiv1810376193attach-table div div a {text-decoration:none;}#yiv1810376193 div.yiv1810376193attach-table {width:400px;}#yiv1810376193 div.yiv1810376193file-title a, #yiv1810376193 div.yiv1810376193file-title a:active, #yiv1810376193 div.yiv1810376193file-title a:hover, #yiv1810376193 div.yiv1810376193file-title a:visited {text-decoration:none;}#yiv1810376193 div.yiv1810376193photo-title a, #yiv1810376193 div.yiv1810376193photo-title a:active, #yiv1810376193 div.yiv1810376193photo-title a:hover, #yiv1810376193 div.yiv1810376193photo-title a:visited {text-decoration:none;}#yiv1810376193 div#yiv1810376193ygrp-mlmsg #yiv1810376193ygrp-msg p a span.yiv1810376193yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1810376193 .yiv1810376193green {color:#628c2a;}#yiv1810376193 .yiv1810376193MsoNormal {margin:0 0 0 0;}#yiv1810376193 o {font-size:0;}#yiv1810376193 #yiv1810376193photos div {float:left;width:72px;}#yiv1810376193 #yiv1810376193photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv1810376193 #yiv1810376193photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1810376193 #yiv1810376193reco-category {font-size:77%;}#yiv1810376193 #yiv1810376193reco-desc {font-size:77%;}#yiv1810376193 .yiv1810376193replbq {margin:4px;}#yiv1810376193 #yiv1810376193ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1810376193 #yiv1810376193ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1810376193 #yiv1810376193ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1810376193 #yiv1810376193ygrp-mlmsg select, #yiv1810376193 input, #yiv1810376193 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1810376193 #yiv1810376193ygrp-mlmsg pre, #yiv1810376193 code {font:115% monospace;}#yiv1810376193 #yiv1810376193ygrp-mlmsg * {line-height:1.22em;}#yiv1810376193 #yiv1810376193ygrp-mlmsg #yiv1810376193logo {padding-bottom:10px;}#yiv1810376193 #yiv1810376193ygrp-msg p a {font-family:Verdana;}#yiv1810376193 #yiv1810376193ygrp-msg p#yiv1810376193attach-count span {color:#1E66AE;font-weight:700;}#yiv1810376193 #yiv1810376193ygrp-reco #yiv1810376193reco-head {color:#ff7900;font-weight:700;}#yiv1810376193 #yiv1810376193ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1810376193 #yiv1810376193ygrp-sponsor #yiv1810376193ov li a {font-size:130%;text-decoration:none;}#yiv1810376193 #yiv1810376193ygrp-sponsor #yiv1810376193ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1810376193 #yiv1810376193ygrp-sponsor #yiv1810376193ov ul {margin:0;padding:0 0 0 8px;}#yiv1810376193 #yiv1810376193ygrp-text {font-family:Georgia;}#yiv1810376193 #yiv1810376193ygrp-text p {margin:0 0 1em 0;}#yiv1810376193 #yiv1810376193ygrp-text tt {font-size:120%;}#yiv1810376193 #yiv1810376193ygrp-vital ul li:last-child {border-right:none !important;}#yiv1810376193

['Arno Brinkman' [e-mail] [firebird-support]]

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


From: mailto:firebird-***@yahoogroups.com
Sent: Saturday, September 10, 2016 10:05 PM
To: firebird-***@yahoogroups.com
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

From: mailto:firebird-***@yahoogroups.com
Sent: Friday, September 09, 2016 10:51 PM
To: firebird-***@yahoogroups.com
Subject: Re: [firebird-support] Optimizer request



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

['Djordje Radovanovic' [e-mail] [firebird-support]]

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

From: mailto:firebird-***@yahoogroups.com
Sent: Sunday, September 11, 2016 12:11 AM
To: firebird-***@yahoogroups.com
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


From: mailto:firebird-***@yahoogroups.com
Sent: Saturday, September 10, 2016 10:05 PM
To: firebird-***@yahoogroups.com
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

From: mailto:firebird-***@yahoogroups.com
Sent: Friday, September 09, 2016 10:51 PM
To: firebird-***@yahoogroups.com
Subject: Re: [firebird-support] Optimizer request



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

[Dimitry Sibiryakov [e-mail] [firebird-support]]

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
This time instead of 10000 reads of orders and 10000 reads of partners I received exactly
what I was asking for.

You'd move to desired result faster if instead of useless stats showed plans of the
queries.
--
WBR, SD.


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

['Djordje Radovanovic' [e-mail] [firebird-support]]

Read my previous messages, maybe you'll find my desired result.
You were the first one who tried to help me and I admire that but If you do
not like my thread, just ignore it, or maybe to teach me how to ask this
kind of question to not bother you with my wrong style.

Thanks,

Djordje

-----Original Message-----
From: Dimitry Sibiryakov ***@ibphoenix.com [firebird-support]
Sent: Sunday, September 11, 2016 11:03 AM
To: firebird-***@yahoogroups.com
Subject: Re: [firebird-support] Optimizer request

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
This time instead of 10000 reads of orders and 10000 reads of partners I received exactly
what I was asking for.

You'd move to desired result faster if instead of useless stats showed
plans of the
queries.
--
WBR, SD.


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu
there.

Also search the knowledgebases at
http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links





------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[Dimitry Sibiryakov [e-mail] [firebird-support]]

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
maybe to teach me how to ask this
kind of question to not bother you with my wrong style.

That's exactly what I wrote: whenever you ask for help with SQL queries, post the plan
of the queries, not just stats.
--
WBR, SD.


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[liviuslivius [e-mail] [firebird-support]]

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
This time instead of 10000 reads of orders and 10000 reads of partners I
received exactly
what I was asking for.

Hi,

i do not know what server do you use but if i run simple query on FB3
select
o.*
from
partners p
inner join orders o ON p.partid = o.partid
WHERE
p.country = 'Spain';

on non propagated with data tables, plan is:

PLAN JOIN (P INDEX (PARTNERS_IDX1), O INDEX (FK_ORDERS_PARTNERS))

but if i fill tables with test data and UPDATE INDEX STATISTICS
i got plan

PLAN JOIN (P INDEX (PARTNERS_IDX1), O INDEX (FK_ORDERS_PARTNERS))


then i suppose your real problem is your index statistics or selectivity of some values in joined fields

regards,
karol Bieniaszewski

['Djordje Radovanovic' [e-mail] [firebird-support]]

I am still using Firebird 2.5 and for same query my plan looks like this

PLAN JOIN (O NATURAL, P INDEX (PK_PARTNERS))

according to your plan I need to switch to firebird 3 as soon as possible.

Best regards,

Djordje Radovanovic

From: mailto:firebird-***@yahoogroups.com
Sent: Monday, September 12, 2016 9:49 AM
To: firebird-***@yahoogroups.com
Subject: Re: Re: [firebird-support] Optimizer request

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
This time instead of 10000 reads of orders and 10000 reads of partners I
received exactly
what I was asking for.

Hi,

i do not know what server do you use but if i run simple query on FB3
select
o.*
from
partners p
inner join orders o ON p.partid = o.partid
WHERE
p.country = 'Spain';

on non propagated with data tables, plan is:

PLAN JOIN (P INDEX (PARTNERS_IDX1), O INDEX (FK_ORDERS_PARTNERS))

but if i fill tables with test data and UPDATE INDEX STATISTICS
i got plan

PLAN JOIN (P INDEX (PARTNERS_IDX1), O INDEX (FK_ORDERS_PARTNERS))

then i suppose your real problem is your index statistics or selectivity of some values in joined fields

regards,
karol Bieniaszewski




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

[Dimitry Sibiryakov [e-mail] [firebird-support]]

Post by 'Djordje Radovanovic' ***@sbb.rs [firebird-support]
according to your plan I need to switch to firebird 3 as soon as possible.

You must check that index on partners.country is active and its statistic is fresh. I
also hope that less than 60% of partners are from Spain, because otherwise you have a
problem...
--
WBR, SD.


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[liviuslivius [e-mail] [firebird-support]]

Hi,
i do not know what server do you use but if i run simple query on FB3
select
o.*
from
partners p
inner join orders o ON p.partid = o.partid
WHERE
p.country = 'Spain';
on non propagated with data tables, plan is:
PLAN JOIN (O NATURAL, P INDEX (PK_PARTNERS))
but if i fill tables with test data and UPDATE INDEX STATISTICS
i got plan
PLAN JOIN (P INDEX (PARTNERS_IDX1), O INDEX (FK_ORDERS_PARTNERS))
then i suppose your real problem is your index statistics or selectivity of some values in joined fields
regards,
karol Bieniaszewski

 
 
correction in first plan ..