01.05.2014 23:24, Leyne, Sean wrote: > >> Optimization I. >> - VOLATILE (default) / DETERMINISTIC flag for UDF and FUNCTION (maybe >> lifecycle for deterministic : statement, transaction, connection) >> - gain : preformance (can cache DETERMINISTIC UDF/FUNCTION results) > > Actually, I think that with the exception of a random number generator, the > default for UDF should be DETERMINISTIC (Over 99% of UDFs are not volatile).
FB3 already supports DETERMINISTIC for functions, although it's not a default (and it should not be). As for legacy UDFs, their time is mostly gone. >> - SELECT DISTINCT <index fields> FROM table is slow (natural scan on all >> records) and SELECT <index fields> FROM table GROUP BY <index fields> is >> also slow (worse! : index scan on all records). >> - currently you have to keep a separate table with this information >> because >> you can't access to this information fast >> - solution 1 : use index in this case; > > Using an index may not help: > > 1- an index is stored without regard to physical disk location, so using it > will create a huge amount of random disk IO. Whereas a NATURAL scan follows > the table. It depends. Primary key may be stored more-or-less in regard to physical disk location. And the measure how good is INDEX vs NATURAL scan can be available to the optimizer (index clustering factor). > 2- In an version based database like Firebird each row will need to be read > to confirm the current value of the target field. It's not about version based databases, it's just about our index implementation. And there are possibilities to avoid record lookups even in our implementation. >> Index usage optimization II. >> IS NOT NULL should use index. It is equivalent with >= min_value or <= >> max_value based on index direction > > I don't think this is possible. > > With an MVCC it is possible for all rows to have both a NULL and NOT NULL > values stored in the field index, so reading each rows is required. But as > noted in #1 above, reading by index can lead to significant disk > IO/degradation Who cares if the statistics tells us that NULLs are 95% of all keys? Dmitry ------------------------------------------------------------------------------ "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available. Simple to use. Nothing to install. Get started now for free." http://p.sf.net/sfu/SauceLabs Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
