[firebird-support] Optimizer request

['Djordje Radovanovic' softsis...@sbb.rs [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

Re: [firebird-support] Optimizer request

[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.






++

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-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

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

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

[firebird-support] Re: Optimizer request

['Jeremy Poppleton' jeremy.popple...@csy.co.uk [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

[firebird-support] PHP REST framework for Firebird 2.1 (dialect 1 preferably), Yii

[jonatan.laurit...@yahoo.dk [firebird-support]]

I am searching PHP REST framework for Yii but I had problems with Laravel who 
required dialect 3 syntax and now I have problems with Yii which have been 
described in stack question:
How to retrieve the result of a Firebird INSERT ... RETURNING in Yii 
http://stackoverflow.com/questions/39411482/how-to-retrieve-the-result-of-a-firebird-insert-returning-in-yii

 
 
 
http://stackoverflow.com/questions/39411482/how-to-retrieve-the-result-of-a-firebird-insert-returning-in-yii
 
 
 How to retrieve the result of a Firebird INSERT ... RETU... 
http://stackoverflow.com/questions/39411482/how-to-retrieve-the-result-of-a-firebird-insert-returning-in-yii
 I am trying to use Firebird 2.1 with Yii (using plugin 
http://www.yiiframework.com/extension/yii2-firebird/) but I have problems doing 
insert (save) commands...
 
 
 
 View on stackoverflow.com 
http://stackoverflow.com/questions/39411482/how-to-retrieve-the-result-of-a-firebird-insert-returning-in-yii
 
 Preview by Yahoo 
 
 
  
So - my questions are:
1) is anybody using Yii with Firebird (with all the ActiveRecord capabilities)
2) how to overcome my problem with Yii
3) maybe there is alternative PHP REST framework for Firebird. I can guess that 
Zend can be good candidate but is there good experience?

J.
 

Re: [firebird-support] Re: Optimizer request

['Djordje Radovanovic' softsis...@sbb.rs [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 1 records and partners table with 1000 
records. Than I made query same as in my example and received 1 reads from 
orders and 1 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-support@yahoogroups.com 
Sent: Friday, September 09, 2016 3:01 PM
To: firebird-support@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

Re: [firebird-support] Optimizer request

[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 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
>
>
>
>
> 
>

Re: [firebird-support] Optimizer request

['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 

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 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

RE: [firebird-support] Optimizer request

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

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

Re: [firebird-support] Optimizer request

['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 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' softsis...@sbb.rs 
[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













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

RE: [firebird-support] Optimizer request

['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?  

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



Sean

Re: [firebird-support] Optimizer request

['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 Brinkman



From: mailto:firebird-support@yahoogroups.com 
Sent: Friday, September 9, 2016 2:30 PM
To: firebird-support@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

Re: [firebird-support] Optimizer request

[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
> 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-support@yahoogroups.com
> 
> *Sent:* Friday, September 9, 2016 2:30 PM
> *To:* firebird-support@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
>
>
>
> 
>

[firebird-support] Connection pool, trigger and binnacle

[Carlos Mazariegos carlos.mazarie...@umg.edu.gt [firebird-support]]

Hi,

I'm using Firebird 2.5 and i have a system running in this server.
I have a binnacle (bitacora) that is updated with triggers "After Update 
or Delete"

and everything works fine, since I use the "current_user".

But I'm developing a new web application with java EE, JSF and 
Glassfish, with
Glassfish i am using a connection pool, this use a user WEBGRAL for n 
users who connect.


Then I have the problem that all transactions are recorded update and delete
in the database with the user WEBGRAL and not the user who entered the 
time of loggearse

in the web application.

There a way to pass the user triggers firebird to recognize them or some 
other idea to solve

the problem.

Regards

--

Carlos Mazariegos
/Dirección de Desarrollo/

Universidad Mariano Gálvez
3a. Av. 9-00 Zona 2 · Int. Finca El Zapote
Tel: *24111800 Ext. 1166 y 1290* · Fax: *Ext. 1166*
carlos.mazarie...@umg.edu.gt  · 
www.umg.edu.gt 

Re: [firebird-support] Optimizer request

[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.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

> __,_._,_
>

Re: [firebird-support] Connection pool, trigger and binnacle

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

09.09.2016 22:48, Carlos Mazariegos carlos.mazarie...@umg.edu.gt 
[firebird-support] wrote:
> There a way to pass the user triggers firebird to recognize them or some 
> other idea to solve
> the problem.

   Use RDB$SET_CONTEXT() in your web-application and RDB$GET_CONTEXT() in 
trigger.


-- 
   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-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

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

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

Re: [firebird-support] Optimizer request

[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... you meant

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

H.