[firebird-support] What is more effective execute block or select?
I would know whats methods is more effective, per exemple: 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.
Re: [firebird-support] What is more effective execute block or select?
On Wed, 27 Jan 2016 10:48:49 -0200, "hamacker sirhamac...@gmail.com [firebird-support]" wrote: > I would know whats methods is more effective, per exemple: > 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. I'd use select 1 from cv_itens where id_cv=51716 and check if you have a row or not. If it can produce multiple rows, then use select first 1 1 from cv_itens where id_cv=51716, or select 1 from cv_itens where id_cv=51716 rows 1. That is much simpler, and likely has less overhead. Mark
Re: [firebird-support] What is more effective execute block or select?
On Wed, Jan 27, 2016 at 7:48 AM, hamacker sirhamac...@gmail.com [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, 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. > > > > >
Re: [firebird-support] What is more effective execute block or select?
>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. I'd never thought I would ask YOU this question, but are you sure, Ann? I just wonder if you've thought EXECUTE STATEMENT where you've written EXECUTE BLOCK. I consider EXECUTE BLOCK the DML equivalent of stored procedures, know they can be put into cursors, prepared and repeatedly executed and find them quite handy (sometimes they make complex queries more easily readable, sometimes they improve performance). EXECUTE STATEMENT on the other hand, I generally try to avoid. Mark's answer is of course a good one to the particular question. Though I would like to partially answer the original question: Generally, I've never even thought about comparing an IIF statement (or CASE) to EXECUTE BLOCK, to me they are just very different. I've nothing to substantiate my GUESS (no knowlegde of internal Firebird workings, nor tried anything), but I doubt this is an area where one of the two generally is significantly better than the other. On the other hand, there are cases where EXECUTE BLOCK can help performance (note, I speak for 2.5, I know nothing about Firebird 3): UPDATE h SET = (SELECT FROM t WHERE h. = t.) is much slower than EXECUTE BLOCK AS Declare variable a integer; Declare variable b integer; BEGIN FOR SELECT DISTINCT , FROM INTO :a, :b do UPDATE HugeTable SET = :b WHERE = :a; END HTH, Set
Re: [firebird-support] What is more effective execute block or select?
On Wed, 27 Jan 2016 23:01:50 +0100, "setysvar setys...@gmail.com [firebird-support]" wrote: > [..] On the other hand, there are cases > where EXECUTE BLOCK can help performance (note, I speak for 2.5, I know > nothing about Firebird 3): > > UPDATE h > SET = (SELECT FROM t WHERE > h. = t.) > > is much slower than > > EXECUTE BLOCK AS > Declare variable a integer; > Declare variable b integer; > BEGIN >FOR SELECT DISTINCT , >FROM >INTO :a, :b do > UPDATE HugeTable > SET = :b > WHERE = :a; > END Out of curiosity: have you also compared this with using MERGE? Mark
Re: [firebird-support] What is more effective execute block or select?
No, I must admit limited knowledge of MERGE. For Firebird 3 is seems like a good alternative, but I think Fb 2.1 and 2.5(?) requires a WHEN NOT MATCHED THEN INSERT clause (which is typically not desirable in these cases). Set 2016-01-28 13:31 GMT+01:00 Mark Rotteveel m...@lawinegevaar.nl [firebird-support] : > On Wed, 27 Jan 2016 23:01:50 +0100, "setysvar setys...@gmail.com > [firebird-support]" wrote: > > > [..] On the other hand, there are cases > > where EXECUTE BLOCK can help performance (note, I speak for 2.5, I know > > nothing about Firebird 3): > > > > UPDATE h > > SET = (SELECT FROM t WHERE > > h. = t.) > > > > is much slower than > > > > EXECUTE BLOCK AS > > Declare variable a integer; > > Declare variable b integer; > > BEGIN > >FOR SELECT DISTINCT , > >FROM > >INTO :a, :b do > > UPDATE HugeTable > > SET = :b > > WHERE = :a; > > END > > Out of curiosity: have you also compared this with using MERGE? > > Mark > > > > Posted by: Mark Rotteveel > > > ++ > > 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/ > > ++ > > > Yahoo Groups Links > > > >
Re: [firebird-support] What is more effective execute block or select?
On Thu, 28 Jan 2016 16:22:03 +0100, "Svein Erling Tysvær setys...@gmail.com [firebird-support]" wrote: > No, I must admit limited knowledge of MERGE. For Firebird 3 is seems like a > good alternative, but I think Fb 2.1 and 2.5(?) requires a WHEN NOT MATCHED > THEN INSERT clause (which is typically not desirable in these cases). I don't have a Firebird install available right now, but looking at the syntax file (parse.y), the WHEN NOT MATCHED THEN INSERT clause is not required, not in 2.1 nor in 2.5. The syntax in the language reference suggests it is required, but as far as I can see it isn't. Mark
Re: [firebird-support] What is more effective execute block or select?
On Wed, Jan 27, 2016 at 5:01 PM, setysvar setys...@gmail.com [firebird-support] 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 h > SET = (SELECT FROM t WHERE > h. = t.) > > is much slower than > > EXECUTE BLOCK AS > Declare variable a integer; > Declare variable b integer; > BEGIN >FOR SELECT DISTINCT , >FROM >INTO :a, :b do > UPDATE HugeTable > SET = :b > WHERE = :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 for do 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
Re: [firebird-support] What is more effective execute block or select?
Re: [firebird-support] What is more effective execute block or select?
Thanks, it´s clear now. 2016-01-27 14:27 GMT-02:00 Ann Harrison aharri...@ibphoenix.com [firebird-support] : > >
