On Wed, Jan 27, 2016 at 5:01 PM, setysvar setys...@gmail.com [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
