Subject Re: [firebird-support] What is more effective execute block or select?
Author Ann Harrison
On Wed, Jan 27, 2016 at 5:01 PM, setysvar setysvar@... [firebird-support] <> 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
 >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

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

Declare variable a integer;
Declare variable b integer;
   FOR SELECT DISTINCT <SomeField>, <AnotherField>
   FROM <TinyTable>
   INTO :a, :b do
     UPDATE HugeTable
        SET <AField> = :b
        WHERE <SelectiveIndexedField> = :a;

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.