Subject Re: [firebird-support] What is more effective execute block or select?
Author Ann Harrison

On Wed, Jan 27, 2016 at 7:48 AM, hamacker sirhamacker@... [firebird-support] <> wrote:

I would know whats methods is more effective, per exemple:

Example below shows two convoluted ways to return 'Y' or 'N' depending on
whether an item with a particular id exists: a select against rdb$database
and an execute block.  

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.

If the question is which of these particular queries is more effective, the answer is
neither.  Mark pointed out that there are much simpler ways to determine if an item 
with a particular value exists, though they simply fail to return a value when the 
answer is no.  In most cases it's pretty easy to build the rest of the logic in whatever
it is that's asking the question.

Good luck,


To know if itens exists or not, select
    when exists(select 1 from cv_itens  where id_cv=51716)
    then 'Y'
    else 'N'
  end as existe_itens
from rdb$database

OR using block execute:
execute block
returns(exist varchar(1))
  if (exists(select 1 from cv_itens  where id_cv=51716)) then exist='Y';

I think that first method using rdb$database as source, checking permissions and others things and second method is not and more powerfull using psql.

I would like to know what you guys think about.