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
