|Subject||RE: [firebird-support] Reading unnecessary records|
> The structure of the view is something like CREATE VIEW PersonView ..Where are you getting those details?
> 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.
Firebird reports every row that is being read, including internal index rows/data.
Also the query PLAN details makes the distinction between "fetches" reads which were resolved by engine page cache and "reads" (actual reads from disk/or OS disk cache)
So, without seeing the fetch details, it is not possible to say how your "problem" can be solved.
> The real problem is that I have millions of records in the database, and aWith that size of database, it is likely that the database cache size setting is low and should be increased.
> simple consult can take minutes when it could take few seconds with an
> optimized plan.
To answer that question, you will need to specific which version of the engine you are running? Classic, SuperClassic or SuperServer.