|
Hello Dimitry! Here is my wish 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?) increase or remove completly the context limit (not the context variable but the table/view/sp stream, current limit is 255) - gain : performance (bigger EXECUTE BLOCK with more DML, e.g.: a lot of INSERT in a single SQL command) 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) 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) Optimization II. - 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; solution 2 : a system table with this information; soultion 3 : select from index (not so SQL standard) - gain : redundancy reduction and/or speed Index usage optimization I. CORE-2589 Index usage optimization II. IS NOT NULL should use index. It is equivalent with >= min_value or <= max_value based on index direction Index usage optimization III. - 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. More details for exception 335544321 - Arithmetic exception, numeric overflow, or string truncation : table & fieldname (procedure & variable name) in exception message PSQL exception handling : new variables aside GDSCODE and SQLCODE - ERRORMESSAGE : form fbclient - GDSCODE_ORI, SQLCODE_ORI, ERRORMESSAGE_ORI : execute statement errors on external database hides the original error code, you should access them with these - gain : better logging and error tracking and user feedback longer object names CORE-749 rename objects it's a nightmare to rename soething with many-many dependencies performance info - ms is not so accurate and it seems also affecte by the thread time slice size. Need a more accurate measure : maybe gettickcount? Foreign key column or referenced column can be a const value - e.g. : ALTER TABLE <table> ADD CONSTRAINT FKNAME FOREIGN KEY (field1, field2) REFERENCES <referenced_table> ('CONST', field3); - gain : data integrity without useless data storage Save blob as file to disk at server side - gain : easy UDF deploy and update (and many other thing) - it's also a seurity risk, so some options needed in firebird.conf select from execute block - gain : avoid stored procedure creation. With select from select it's easy to generate code, but can't do that with execute block. (select from procedure is allowed, but execute block can't replace the procedure in this case) Thank You! -- Molnár Attila szoftverfejlesztő Tel : 372-3333 E-mail: amol...@mve.hu LIBRA Szoftver zrt. 1113 Bp. Karolina út 65. Tel: 372-3333 Fax: 209-1477 Web: www.mve.hu E-mail: i...@mve.hu Olvasson ügyfeleinkkel elért közös sikereinkről: http://www.mve.hu/hu/referenciaink |
------------------------------------------------------------------------------ "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
