On 29/03/12 17:21, Ann Harrison wrote:

> When Norm says he isn't quite sure about something, I have to assume that
> lots of people are also in the dark.

I think you give me too much credit there! ;-)

> The performance problem in preparing
> queries comes from the algorithm Firebird uses to estimate the cardinality
> (number of records) of a table.

<SNIP>

Good explanation, thanks. That's going to end up in the Firebird
Internals manual sometime!

I've no idea how good a suggestion this is, but how difficult (sorry
developers!) would it be to do something along the Oracle lines?

RDB$RELATIONS would have columns for cardinality, spread of values and
so on. These would be recalculated on demand - Oracle used a package
called DBMS_STATS to gather optimiser stats.

The optimiser in Oracle then reads these stats, and histograms etc form
other system tables, and uses these to pick the best execution plan.

Granted the stats have to be kept "reasonably" up to date or performance
suffers, but it's a good way of avoiding dynamic scans to check cardinality?

Just a thought.

Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767