|Subject||Re: [firebird-support] What is more effective execute block or select?|
On Wed, Jan 27, 2016 at 5:01 PM, setysvar setysvar@... [firebird-support] <firstname.lastname@example.org> 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
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 recognizethat 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;
FOR SELECT DISTINCT <SomeField>, <AnotherField>
INTO :a, :b do
SET <AField> = :b
WHERE <SelectiveIndexedField> = :a;
ENDGDS/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 typicallyused nested loops "for <this> for <that which matched this> do <the other> end-for end-for", which maps very nicely into the PSQLFOR SELECT. The SQL UPDATE statement is one of the ugliest parts of that ugly language and makes it very difficult to optimizethe 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