On 04/04/2014 23:36, Alexandre Benson Smith wrote:
If you don't need a particular table information, having it on the
view is useless... So if you have the need of a distinct rescult set
only one view would not handle this. So the on the fly generated query
is not better than the view
Please, let me explain it better:
I cannot use INNER JOIN since many codification may be NULL. For example, a
person could not have a hobby, or the the job codification could be NULL
because he/she is a child. So, I need to use LEFT JOINs.
This view, PersonView, is exposed to database users
I use the latest version of FB 2.5.
There is a view for called PERSON. Each row represents a person and it shows
information as address, name, hobbies, etc.
There are 20 joined codification tables using LEFT JOIN. For example all cities
are codified, hobbies, etc.
The structure of the view is
We generate the queries on the fly rather than trying to use a view,
precisely because of these problems.
So if CityName is not required in the output, there's no JOIN to City.
And if there's something in the WHERE clause such as HobbyCode 27
then we know that HobbyID can't be null, which
I agree with Tim. Whe dont uwe views too becase this speed problem.
Em 04/04/2014 08:51, Tim Ward t...@telensa.com escreveu:
We generate the queries on the fly rather than trying to use a view,
precisely because of these problems.
So if CityName is not required in the output, there's no
The structure of the view is something like CREATE VIEW PersonView ..
SELECT *
FROM PersonTable P
LEFT JOIN City ON City.ID = P.CityID
LEFT JOIN Hobby ON Hobby.ID = P.HobbyID ...
and so on for the 20 codified tables. City.ID is a primary key, like all IDs
for
remaining codifications.
I get these data from an old app called Interbase PLAN Analizer, that works
pretty well.
It shows me reads and also the plan. I can see how Firebird includes in the
plan all codification tables, when it is not strictly necessary.
Something changed in FB 2.5 version. I used 1.0 for a long time
I get these data from an old app called Interbase PLAN Analizer, that works
pretty well.
It shows me reads and also the plan. I can see how Firebird includes in the
plan all codification tables, when it is not strictly necessary.
Something changed in FB 2.5 version. I used 1.0 for a long
I would expect that Firebird only read 10 record, however it reads 10 +
10x20 = 210 records.
In theory the optimizer could know that City.ID and Hobby.ID are both
primary keys (or unique) and therefore only scan PersonTable.
The engine/optimizer does know that.
The engine, however,
Hi,
Did you use order by or group by in this view?
Regards,
Karol Bieniaszewski
- Reply message -
Od: kokok_ko...@yahoo.es
Do: firebird-support@yahoogroups.com
Temat: [firebird-support] Reading unnecessary records
Data: pt., kwi 4, 2014 13:36
I use the latest version of FB 2.5
Em 4/4/2014 09:31, fabianoas...@gmail.com escreveu:
I agree with Tim. Whe dont uwe views too becase this speed problem.
Em 04/04/2014 08:51, Tim Ward t...@telensa.com
mailto:t...@telensa.com escreveu:
We generate the queries on the fly rather than trying to use a
view, precisely
11 matches
Mail list logo
