Re: Re: [firebird-support] Optimizer request

2016-10-19 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support]
Oryginalna wiadomość > Od: "ehmmm.fireb...@seznam.cz [firebird-support]" < > firebird-support@yahoogroups.com> > Data: 19.10.2016 09:34 (GMT+01:00) > Do: firebird-support@yahoogroups.com > Temat: Re: [firebird-support] Optimizer request > > > > I&#x

ODP: Re: [firebird-support] Optimizer request

2016-10-19 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
hi, maybe MERGE is your answer regards,Karol Bieniaszewski Oryginalna wiadomość Od: "ehmmm.fireb...@seznam.cz [firebird-support]" Data: 19.10.2016 09:34 (GMT+01:00) Do: firebird-support@yahoogroups.com Temat: Re: [firebird-support] Optimiz

Re: [firebird-support] Optimizer request

2016-10-19 Thread ehmmm.fireb...@seznam.cz [firebird-support]
right? Would using EXISTS() help? E. -- Původní zpráva -- Od: Svein Erling Tysvær setys...@gmail.com [firebird-support] Komu: firebird-support@yahoogroups.com Datum: 9. 9. 2016 17:10:21 Předmět: Re: [firebird-support] Optimizer request "   Never use IN (subselect). Change to sel

Re: [firebird-support] Optimizer request

2016-09-13 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]
13.09.2016 12:23, 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] wrote: > 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

Re: [firebird-support] Optimizer request

2016-09-13 Thread 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support]
, September 12, 2016 9:49 AM To: firebird-support@yahoogroups.com Subject: Re: Re: [firebird-support] Optimizer request > > 11.09.2016 10:29, 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] > wrote: > > This time instead of 1 reads of orders and

Re: Re: Re: [firebird-support] Optimizer request

2016-09-12 Thread liviuslivius liviusliv...@poczta.onet.pl [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 an

Re: Re: [firebird-support] Optimizer request

2016-09-12 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
> > 11.09.2016 10:29, 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] > wrote: > > This time instead of 1 reads of orders and 1 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 s

Re: [firebird-support] Optimizer request

2016-09-11 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]
11.09.2016 18:39, 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] wrote: > 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 st

Re: [firebird-support] Optimizer request

2016-09-11 Thread 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support]
ordje -Original Message- From: Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] Sent: Sunday, September 11, 2016 11:03 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Optimizer request 11.09.2016 10:29, 'Djordje Radovanovic' softsis...@sbb.rs [firebird-

Re: [firebird-support] Optimizer request

2016-09-11 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]
11.09.2016 10:29, 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] wrote: > This time instead of 1 reads of orders and 1 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 quer

Re: [firebird-support] Optimizer request

2016-09-11 Thread 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support]
ards, Arno Brinkman From: mailto:firebird-support@yahoogroups.com Sent: Saturday, September 10, 2016 10:05 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Optimizer request CREATE TABLE PARTNERS ( PARTID INTEGER NOT NULL, COUNTRY CHAR(20) ); ALTER TABLE PA

Re: [firebird-support] Optimizer request

2016-09-10 Thread 'Arno Brinkman' fbsupp...@abvisie.nl [firebird-support]
rd-support@yahoogroups.com Sent: Saturday, September 10, 2016 10:05 PM To: firebird-support@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 K

Re: [firebird-support] Optimizer request

2016-09-10 Thread monteropetronila...@yahoo.com [firebird-support]
cient P/SQL and some improvements will give us significant efficiency..Djordje From: mailto:firebird-support@yahoogroups.com Sent: Friday, September 09, 2016 10:51 PMTo: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Optimizer request   On Fri, Sep 9, 2016 at 8:30 AM, 'Djor

Re: [firebird-support] Optimizer request

2016-09-10 Thread 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support]
-support@yahoogroups.com Subject: Re: [firebird-support] Optimizer request On Fri, Sep 9, 2016 at 8:30 AM, 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] wrote: I tried query with subquery in where clause and found big issue for this type of subquery. select * from or

Re: [firebird-support] Optimizer request

2016-09-10 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support]
I wrote > > select o.* > > from orders o > > inner join partners p > > > * where p.partid = o.opartid > and p.country = 'Spain*'; > > Helen suggests the more correct: > select o.* > from orders o > inner join partners p > * on p.partid = o.op

Re: [firebird-support] Optimizer request

2016-09-09 Thread Helen Borrie hele...@iinet.net.au [firebird-support]
Hello Ann, Saturday, September 10, 2016, 8:51:05 AM, you wrote: > 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...

Re: [firebird-support] Optimizer request

2016-09-09 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support]
On Fri, Sep 9, 2016 at 8:30 AM, 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] 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

Re: [firebird-support] Optimizer request

2016-09-09 Thread Louis Kleiman lklei...@sstms.com [firebird-support]
How about this: select O.* from orders O join partners P on (P.partid = O.partid) where P.country = 'Spain' On Fri, Sep 9, 2016 at 12:57 PM, 'Arno Brinkman' fbsupp...@abvisie.nl [firebird-support] wrote: > > > Hi, > > As others already suggested i would also prefer EXISTS, but anyway there >

Re: [firebird-support] Optimizer request

2016-09-09 Thread 'Arno Brinkman' fbsupp...@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

RE: [firebird-support] Optimizer request

2016-09-09 Thread 'Leyne, Sean' s...@broadviewsoftware.com [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?

Re: [firebird-support] Optimizer request

2016-09-09 Thread 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support]
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

RE: [firebird-support] Optimizer request

2016-09-09 Thread 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support]
: 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<mailto:firebird-support@yahoogroups.com> Subje

Re: [firebird-support] Optimizer request

2016-09-09 Thread 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support]
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

Re: [firebird-support] Optimizer request

2016-09-09 Thread Svein Erling Tysvær setys...@gmail.com [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’) 2016-09-09 14:30 GMT+02:00 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] : > > > I tried query with subquery in where c

Re: [firebird-support] Optimizer request

2016-09-09 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]
09.09.2016 14:30, 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] wrote: > 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. -