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

 

On Wed, Jan 27, 2016 at 7:48 AM, hamacker sirhamacker@gmail.com [firebird-support] <firebird-support@yahoogroups.com> 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,

Ann  

 

To know if itens exists or not, select
select
  case
    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))
as
begin
  exist='N';
  if (exists(select 1 from cv_itens  where id_cv=51716)) then exist='Y';
  suspend;
end

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.

__._,_.___

---

Posted by: Ann Harrison <aharrison@ibphoenix.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___