Subject RE: [firebird-support] Reading unnecessary records
Author Leyne, Sean
> 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 and the new
> one is slower in some cases.

True, but v1.0 would generate invalid results in some corner cases. Result accuracy must always trump pure performance.

> Probably I could improve tuning the database, but the bottle neck is the plan,
> that it is not optimized.

But you are assuming that because the PLAN shows that a non-referenced table is actually being read during execution. That may not be the case.

The real test to confirm is running the queries and looking at the total of READs and FETCHes.

Given the small size of the page cache for typical Classic deployments, and the size of the database, it is possible that the reads are to load the index data alone.


- Please provide the schema for the tables and views, as well as the PLAN which is generated
- what is the database page size?
- In your v1.0 vs. v2.5 performance tests, did you use the exact same database for the comparison

> As other users say in this forum about this thread, it is a huge problem.
> They, as probably me, cannot use views because they are slow.

Views are no slower than the same as standalone SQL, this has been tested/benchmarked and confirmed.

I am not convinced that your numbers prove that non-referenced JOIN tables actually impact performance (whether in View or standalone query)