|
Hi *! Here is my list. DOMAIN OF COLUMN <table or view>.<column> - should work like TYPE OF COLUMN, but CHECK and NOT NULL constraints are checked when value assigned to this variable - gain : performance (allows early check, error raised before the actual DML statement run) SIZE OF <CHAR/VARCHAR domain or variable name>, SCALE OF <NUMERIC domain or variable name> - SIZE OF : returns max CHAR/VARCHAR length or NUMERIC precision, SCALE OF : return scale of NUMERIC - gain : by defining a variable with DOMAIN or TYPE OF (or DOMAIN OF) the declaration is dynamic. But the in the PSQL body there is no dynamic access to this information, you still need hard code these values. With this you can avoid hard coded constants. ARRAY TYPE (only in PSQL) - static and dynamic, one and multi dimensional - gain : performance (no need for table write storing temporary data), shorter and easy to understand PSQL code (UDF parameters with array types?) RECORD TYPE (only in PSQL) - gain : shorter and easy to understand PSQL code (UDF parameters with record types?) TRY-FINALLY - gain : code reduction (Now with AUTONOMUS TRANSACTION it make sense to have try-finally structure. Currently I have the same code in the successfull run path and also in the WHEN ANY path) TRIGGER : NEW/OLD values accessible by column name, and column number (PSQL) - e.g.: NEW['id'], NEW[0] - gain : code reduction, dynamic code (don't have to alter the trigger ICO the table structure altered) Optimizations - CORE-2589 - CORE-2589 - CORE-4266 (please rename the ticket : "NULLS FIRST/LAST should not cause index loss") - IS NOT NULL should use index. It is equivalent with >= min_value or <= max_value based on index direction - condition pre-evaluation and reduction. e.g.: WHERE 1 = 2 AND field = :param is always FALSE. Evaluation does not needed for all records, can decide at prepare time whether the result is an empty resultset or an unfiltered resultset. - use index in "NATURAL" mode when column in a conditional appears in a multi column index, but not in the first place. You may reduce number of database page visits in this way : index page can hold more effective record data beacouse it's narrower than the table data page record (also in worst case it could be worse than NATURAL beacose ot the mixed index and table data page read, but I think overall it could worth it, especially in big tables. measurements needed) - SELECT DISTINCT <indexed fields> FROM table is slow (natural scan on all records) and SELECT <indexed fields> FROM table GROUP BY <indexed fields> is also slow (worse! : index scan on all records). I think in this case it's not neccessary to read all the records in the table, it should be enough to read #of distinct <indexed fields> values from table. (currently you have to keep a separate table with this information because you can't access to this information fast) Thank You! -- Molnár Attila szoftverfejlesztő Libra Szoftver Zrt. 1113 Budapest, Karolina út 65. Tel.: +36 1 255 3939 Fax: +36 1 209 1477 http://www.libraszoftver.hu Tisztelt Ügyfelünk! Tájékoztatjuk, hogy 2015. október 14-től megújult a Libra Groupware, a Libra Szoftver Zrt. bejelentő rendszere. A rendszerhez, korábban már regisztrált ügyfeleink új jelszót a https://libragroupware.mve.hu/lgw/ link használatával kérhetnek. Új regisztráció a regisztra...@mve.hu címre írt levélben igényelhető. |
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
