|Subject||Re: [firebird-support] What is more effective execute block or select?|
On Wed, Jan 27, 2016 at 7:48 AM, hamacker sirhamacker@... [firebird-support] <firstname.lastname@example.org> wrote:
I would know whats methods is more effective, per exemple:Example below shows two convoluted ways to return 'Y' or 'N' depending onwhether an item with a particular id exists: a select against rdb$databaseand 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, avoidingvalidating 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 Firebirdconsiders 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 isneither. Mark pointed out that there are much simpler ways to determine if an itemwith a particular value exists, though they simply fail to return a value when theanswer is no. In most cases it's pretty easy to build the rest of the logic in whateverit is that's asking the question.Good luck,AnnTo know if itens exists or not, selectOR using block execute:
when exists(select 1 from cv_itens where id_cv=51716)
end as existe_itens
if (exists(select 1 from cv_itens where id_cv=51716)) then exist='Y';
endI 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.