30.04.2014 13:50, Molnár Attila wrote: > > *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.
Why wouldn't you want to create domains and use TYPE OF / DOMAIN OF? > *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) Multi-value (multi-row) INSERT would serve the same goal without affecting the context limit. Batch DML API would also allow remove the need for EXECUTE BLOCK to execute batch INSERTs. Why else would you need a bigger EXECUTE BLOCK? > *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) Dynamic code is not going to work reliably, unless you just iterate fields in the loop. In this case, I see it just as a feature misuse, some other solution may be required instead. > *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) Partically done in FB3, to be improved later. > *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. http://tracker.firebirdsql.org/browse/CORE-1287 > *PSQL exception handling : new variables aside GDSCODE and SQLCODE* > - ERRORMESSAGE : form fbclient Also exists in the tracker. > - GDSCODE_ORI, SQLCODE_ORI, ERRORMESSAGE_ORI : execute statement > errors on external database hides the original error code, you should > access them with these Maybe an ability to iterate all status vection arguments would be a better (and more generic) solution? > *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? What performance info do you mean here? CPU time reported by ISQL? > *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 And if you change master.field1 to become 'NONCONST' in some record your database gets logically corrupted due to constraint violation? Also, the prefix compression nearly completely removes your 'CONST' from the FK index anyway, so there's no useless data storage. > *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) The only syntax I could live with (still not being happy at all!) is using CTE, something like: WITH EXECUTE BLOCK ... AS ... SELECT ... IMHO, it's terribly ugly, but other options are even uglier. 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
