Hello Dmitry!
On 2014.05.02. 8:34, Dmitry Yemanov wrote:
> 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?
I'm createing domains, and using TYPE OF. But as I write it's not
enough. This is just for variable declaration but I need a pair in PSQL
body. Maybe this example would help to understand.
EXECUTE BLOCK
AS
DECLARE VARIABLE tmp TYPE OF COLUMN rdb$database.rdb$character_set_name;
DECLARE VARIABLE i INTEGER;
BEGIN
i = 1;
WHILE (i <= 31)--I want avoid hard coded 31 with "SIZE OF tmp" OR
SIZE OF COLUMN rdb$database.rdb$character_set_name
DO BEGIN
--some logic here
i = i + 1;
END
END
>
>> *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?
I won't if these two are available. I wasn't aware of them.
>
>> *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.
It's about iteration. Define "misuse".
>
>> *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?
Status vectos is at API level if I'n not mistaken. What about PSQL level?
>
>> *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?
anything is a good candidate which has better scaleing than the curreny one.
>
>> *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.
I could live with that kind of ugly. This is not priority for me.
>
>
> 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
------------------------------------------------------------------------------
Is your legacy SCM system holding you back? Join Perforce May 7 to find out:
• 3 signs your SCM is hindering your productivity
• Requirements for releasing software faster
• Expert tips and advice for migrating your SCM now
http://p.sf.net/sfu/perforce
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
