On Wed, Jan 27, 2016 at 5:01 PM, setysvar setysvar@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 >If the question is which is likely to perform better, then the answer
is the select.
...
 >In general, execute block should be used when you want to change what
Firebird
 >considers fundamental elements of a quer...

I'd never thought I would ask YOU this question, but are you sure, Ann?

Sure?  No.  

I just wonder if you've thought EXECUTE STATEMENT where you've written
EXECUTE BLOCK.

Probably.  Does EXECUTE BLOCK allow you to build up the block at runtime?

If so, then I think I may be right unless the compiler is clever enough to recognize 

that your particular block is static.

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

GDS/Galaxy aka InterBase, aka Firebird was built around a relational language that practitioners at the time would have called "procedural" as opposed to Quel which was "declarative".  Both lost to SQL which was just ugly.  The procedural language typically

used nested loops "for <this> for <that which matched this> do <the other> end-for end-for", which maps very nicely into the PSQL

FOR SELECT.   The SQL UPDATE statement is one of the ugliest parts of that ugly language and makes it very difficult to optimize

the case where you're drawing values from a small table to update a large table.

If EXECUTE BLOCK requires static queries, then I'm completely wrong.  If not, you might be better writing procedures for updates like this, or as Mark suggests, MERGE.

Cheers,

Ann