----- Reply message -----
Od: kokok_kokok@...
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.
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 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.

How can I optimize this view? My problem is that Firebird uses a really slow plan because it reads ALL codification records.
For example, supposing PersonTable has 10 records.
SELECT COUNT(*) FROM PersonView

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& nbsp; are both primary keys (or unique) and therefore only scan PersonTable.

Another example:
SELECT CityName FROM PersonView
I woud expect that Firebird read 10 records for PersonTable and 10 for City table, but it reads 210.

The real problem is that I have millions of records in the database, and a simple consult can take minutes when it could take few seconds with an optimized plan.


What options do I have?

Thank you