Re: [firebird-support] VIEW optimization

2016-04-08 Thread Gabor Boros gaborbo...@yahoo.com [firebird-support] 
2016. 04. 07. 21:41 keltezéssel, Ann Harrison aharri...@ibphoenix.com 
[firebird-support] írta:
> You're thinking of a case where there is a (possibly unstated) referential
> relationship between the table that you're getting fields from and the other
> table in the view.  The table you're getting data from is the referencing
> table and the other is the referenced table.  In that particular case, the
> join doesn't matter, but there's no way that Firebird can know that.

Hi Ann,

Thank you for the detailed answer! So, I am smarter than Firebird, 
because I know which JOIN needed and which not. :-)

Gabor






++

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] VIEW optimization

2016-04-07 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support] 
On Thu, Apr 7, 2016 at 3:41 PM, Ann Harrison 
wrote:

> On Thu, Apr 7, 2016 at 2:27 PM, Gabor Boros gaborbo...@yahoo.com
> [firebird-support]  wrote:
>
>> 2016. 04. 07. 19:31 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl
>> [firebird-support] írta:
>>
>
>
Just in case you think Firebird should be able to ignore parts of an outer
join,
let me disillusion you.


>  select d.DepartmentName from Departments d
> *left outer* join Employees e on e.DeptID = d.DeptID
>
> Suppose you have 10 Departments and 10 Employees, but all the
> Employees have the same DeptID.  Then you should get the same
> DepartmentName ten times.
>

The left outer join will return the nine unmatched Departments, returning
one DepartmentName for each unmatched Department.  The name of the
Department with 10 Employees will be returned 10 time.

Good luck,

Ann

>
>
>
>
>

Re: [firebird-support] VIEW optimization

2016-04-07 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support] 
On Thu, Apr 7, 2016 at 2:27 PM, Gabor Boros gaborbo...@yahoo.com
[firebird-support]  wrote:

> 2016. 04. 07. 19:31 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl
> [firebird-support] írta:
> >
> >
> > For a join (inner join), the rows must exist in both tables, so both
> > tables need to be evaluated.
> >
>
> I understand this. Try describe my problem with other words.
> Is the VIEW an atomic (or compiled like a STORED PROCEDURE) thing?
>

For views, only the view definition is stored.  When a query references a
view,
the view is compiled and optimized like any other query.  If possible, it
is
integrated into the larger query.


> I have a VIEW with 4 fields (FIELD1..4), 1 come from the "FROM table"
> (FIELD1) and 3 from JOINs (FIELD2..4). If execute a SELECT FIELD1 FROM
> ... PLAN contains the JOINs. Why? If I write a SELECT manually not
> include JOINs if not needed for the resulted fields, because want
> maximum performance.
>
>
As Mark and others explained, both sides of a join must always be
evaluated, even if you reference only fields from one table.  Consider
this case:

 select d.DepartmentName from Departments d
join Employees e on e.DeptID = d.DeptID

Suppose there are no Employees at all, but 10 Departments.  That
query should return no results because what was asked was to return
the DepartmentName of Departments with Employees.

Suppose you again have 10 Departments, but only 1 Employee and
that Employee has a DeptID that matches one of the Departments.
Then the query should return one DepartmentName.

Suppose you have 10 Departments and 10 Employees, but all the
Employees have the same DeptID.  Then you should get the same
DepartmentName ten times.

You're thinking of a case where there is a (possibly unstated) referential
relationship between the table that you're getting fields from and the other
table in the view.  The table you're getting data from is the referencing
table and the other is the referenced table.  In that particular case, the
join doesn't matter, but there's no way that Firebird can know that.

Good luck,


Ann

Re: [firebird-support] VIEW optimization

2016-04-07 Thread Gabor Boros gaborbo...@yahoo.com [firebird-support] 
2016. 04. 07. 19:31 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] írta:
>
>
> For a join (inner join), the rows must exist in both tables, so both
> tables need to be evaluated.
>
> Mark

I understand this. Try describe my problem with other words.
Is the VIEW an atomic (or compiled like a STORED PROCEDURE) thing?
I have a VIEW with 4 fields (FIELD1..4), 1 come from the "FROM table" 
(FIELD1) and 3 from JOINs (FIELD2..4). If execute a SELECT FIELD1 FROM 
... PLAN contains the JOINs. Why? If I write a SELECT manually not 
include JOINs if not needed for the resulted fields, because want 
maximum performance.

Gabor






++

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] VIEW optimization

2016-04-07 Thread 'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support] 
Also consider the situation with multiple rows on the other side of the join: 
in that case the one side repeats as many times as there are rows

- Bericht beantwoorden -
Van: "'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support]" 
<firebird-support@yahoogroups.com>
Aan: <firebird-support@yahoogroups.com>
Onderwerp: [firebird-support] VIEW optimization
Datum: do, apr. 7, 2016 19:31

For a join (inner join), the rows must exist in both tables, so both tables 
need to be evaluated.
Mark



- Bericht beantwoorden -
Van: "Gabor Boros gaborbo...@yahoo.com [firebird-support]" 
<firebird-support@yahoogroups.com>
Aan: <firebird-support@yahoogroups.com>
Onderwerp: [firebird-support] VIEW optimization
Datum: do, apr. 7, 2016 19:06

2016. 04. 07. 18:48 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] írta:
> Why wouldn't it be necessary, this is an inner join, so the joined table
> needs to be evaluated if the matches row is present.
>

> SELECT RDB$FIELD_NAME FROM VIEW1

SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS

For me the two SELECT is equal. Is not? I don't understand why the JOIN 
is in the PLAN when not needed for the result. My original problem is...
I have a complex SELECT and build up it at runtime in my application 
include only the needed fields, JOINs in it. If create a VIEW from the 
whole SELECT all JOIN etc executed even if only a single field needed 
which can available without any JOIN or other thing.

Gabor






++

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

Re: [firebird-support] VIEW optimization

2016-04-07 Thread 'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support] 
For a join (inner join), the rows must exist in both tables, so both tables 
need to be evaluated.
Mark

- Bericht beantwoorden -
Van: "Gabor Boros gaborbo...@yahoo.com [firebird-support]" 
<firebird-support@yahoogroups.com>
Aan: <firebird-support@yahoogroups.com>
Onderwerp: [firebird-support] VIEW optimization
Datum: do, apr. 7, 2016 19:06

2016. 04. 07. 18:48 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] írta:
> Why wouldn't it be necessary, this is an inner join, so the joined table
> needs to be evaluated if the matches row is present.
>

> SELECT RDB$FIELD_NAME FROM VIEW1

SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS

For me the two SELECT is equal. Is not? I don't understand why the JOIN 
is in the PLAN when not needed for the result. My original problem is...
I have a complex SELECT and build up it at runtime in my application 
include only the needed fields, JOINs in it. If create a VIEW from the 
whole SELECT all JOIN etc executed even if only a single field needed 
which can available without any JOIN or other thing.

Gabor






++

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



https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] VIEW optimization

2016-04-07 Thread Gabor Boros gaborbo...@yahoo.com [firebird-support] 
2016. 04. 07. 18:48 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] írta:
> Why wouldn't it be necessary, this is an inner join, so the joined table
> needs to be evaluated if the matches row is present.
>

> SELECT RDB$FIELD_NAME FROM VIEW1

SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS

For me the two SELECT is equal. Is not? I don't understand why the JOIN 
is in the PLAN when not needed for the result. My original problem is...
I have a complex SELECT and build up it at runtime in my application 
include only the needed fields, JOINs in it. If create a VIEW from the 
whole SELECT all JOIN etc executed even if only a single field needed 
which can available without any JOIN or other thing.

Gabor






++

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] VIEW optimization

2016-04-07 Thread 'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support] 
Why wouldn't it be necessary, this is an inner join, so the joined table needs 
to be evaluated if the matches row is present.
Mark

- Bericht beantwoorden -
Van: "Gabor Boros gaborbo...@yahoo.com [firebird-support]" 
<firebird-support@yahoogroups.com>
Aan: <firebird-support@yahoogroups.com>
Onderwerp: [firebird-support] VIEW optimization
Datum: do, apr. 7, 2016 18:34

Hi All,

VIEW:
CREATE VIEW VIEW1 AS
SELECT RDB$RELATION_FIELDS.RDB$RELATION_NAME, RDB$FIELD_NAME FROM 
RDB$RELATION_FIELDS
JOIN RDB$RELATIONS ON
RDB$RELATION_FIELDS.RDB$RELATION_NAME=RDB$RELATIONS.RDB$RELATION_NAME;

SELECT:
SELECT RDB$FIELD_NAME FROM VIEW1

PLAN:
PLAN JOIN (VIEW1 RDB$RELATION_FIELDS NATURAL, VIEW1 RDB$RELATIONS INDEX 
(RDB$INDEX_0))


Plan contains the JOIN but that not needed for the selected field. Is 
this an SQL standard thing or just Firebird's optimizer not smart enough?

Gabor






++

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



https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] VIEW optimization

2016-04-07 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
07.04.2016 18:34, Gabor Boros gaborbo...@yahoo.com [firebird-support] wrote:
> Plan contains the JOIN but that not needed for the selected field.

   But it is needed to find out how many records with this value you'll get.

-- 
   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] VIEW optimization

2016-04-07 Thread Gabor Boros gaborbo...@yahoo.com [firebird-support] 
Hi All,

VIEW:
CREATE VIEW VIEW1 AS
SELECT RDB$RELATION_FIELDS.RDB$RELATION_NAME, RDB$FIELD_NAME FROM 
RDB$RELATION_FIELDS
JOIN RDB$RELATIONS ON
RDB$RELATION_FIELDS.RDB$RELATION_NAME=RDB$RELATIONS.RDB$RELATION_NAME;

SELECT:
SELECT RDB$FIELD_NAME FROM VIEW1

PLAN:
PLAN JOIN (VIEW1 RDB$RELATION_FIELDS NATURAL, VIEW1 RDB$RELATIONS INDEX 
(RDB$INDEX_0))


Plan contains the JOIN but that not needed for the selected field. Is 
this an SQL standard thing or just Firebird's optimizer not smart enough?

Gabor






++

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/