>If the question is which is likely to perform better, then the answer
is the select.
>Select statements can be compiled and optimized once and reused, avoiding
>validating access and metadata references. The execute block must be
compiled,
>optimized, and access checked each time it's used.
>
>In general, execute block should be used when you want to change what
Firebird
>considers fundamental elements of a query - the fields returned, the
tables accessed,
>the sorting elements, and the conditions in the on and where clauses.
I'd never thought I would ask YOU this question, but are you sure, Ann?
I just wonder if you've thought EXECUTE STATEMENT where you've written
EXECUTE BLOCK. I consider EXECUTE BLOCK the DML equivalent of stored
procedures, know they can be put into cursors, prepared and repeatedly
executed and find them quite handy (sometimes they make complex queries
more easily readable, sometimes they improve performance). EXECUTE
STATEMENT on the other hand, I generally try to avoid.
Mark's answer is of course a good one to the particular question. Though
I would like to partially answer the original question: Generally, I've
never even thought about comparing an IIF statement (or CASE) to EXECUTE
BLOCK, to me they are just very different. I've nothing to substantiate
my GUESS (no knowlegde of internal Firebird workings, nor tried
anything), but I doubt this is an area where one of the two generally is
significantly better than the other. On the other hand, there are cases
where EXECUTE BLOCK can help performance (note, I speak for 2.5, I know
nothing about Firebird 3):
UPDATE <HugeTable> h
SET <AField> = (SELECT <AnotherField> FROM <TinyTable> t WHERE
h.<SelectiveIndexedField> = t.<SomeField>)
is much slower than
EXECUTE BLOCK AS
Declare variable a integer;
Declare variable b integer;
BEGIN
FOR SELECT DISTINCT <SomeField>, <AnotherField>
FROM <TinyTable>
INTO :a, :b do
UPDATE HugeTable
SET <AField> = :b
WHERE <SelectiveIndexedField> = :a;
END
HTH,
Set
