Re: [firebird-support] Performance of deleting records based on another table
On 8/28/19 6:47 AM, Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support] wrote: > Den 2019-08-17 kl. 12:51, skrev Joost van der Sluis jo...@cnoc.nl > [firebird-support]: > > > > Hi all, > > > > I finally realized today that I do not know how to properly remove > > records based on another table. > > > > Suppose I have two tables, one with data (called BIG), and one table > > (SMALL) with only some id's that has to be removed from the BIG-table. > > > > I cannot get this to work, without having Firebird looping over all > > records in BIG naturally, once for each record in SMALL. > > > > This is a problem I have faced many times. The best solution I've been > able to find is an execute block statement that iterates the records in > SMALL and uses a "delete ... where big.id = :found_small_id" statement > to delete each matching BIG record. Something like this (including set > term if required for your SQL tool): > > set term #; > execute block as > declare id int; > begin > for select small.id from small into :id > do delete from big where id = :id; > end# > set term ;# Thanks for the confirmation. I still find it strange, though. Would be nice if someone could come up with a solution for this. By improving the planning-engine (or how it is called). Or maybe adapt 'merge' so it also allows deletes. Regards, Joost.
[firebird-support] Performance of deleting records based on another table
Hi all, I finally realized today that I do not know how to properly remove records based on another table. Suppose I have two tables, one with data (called BIG), and one table (SMALL) with only some id's that has to be removed from the BIG-table. I cannot get this to work, without having Firebird looping over all records in BIG naturally, once for each record in SMALL. Example: (Firebrid 3.0.4, but also holds on 2.5) -- SQL> connect "test.fdb"; Database: "test.fdb", User: JOOST SQL> create table big(id integer primary key, data varchar(10)); SQL> create table small(id integer primary key); SQL> insert into big values (1,'adfdvf'); SQL> insert into big values (2,'adfdvf'); SQL> insert into big values (3,'adfdvf'); SQL> insert into big values (4,'adfdvf'); SQL> insert into big values (5,'adfdvf'); SQL> insert into big values (6,'adfdvf'); SQL> insert into big values (7,'adfdvf'); SQL> insert into big values (8,'adfdvf'); SQL> insert into big values (9,'adfdvf'); SQL> insert into big values (10,'adfdvf'); SQL> insert into small values (5); SQL> set planonly on; SQL> delete from big where exists (select 1 from small where small.id=big.id); PLAN (SMALL INDEX (RDB$PRIMARY2)) PLAN (BIG NATURAL) -- Naturally, when BIG is really big, this takes very, very long. I've tried everything. 'where in' does not help, and I can not force it to use some other plan either. One possibility is to use an execute-block. This one is somewhat better (not really - in case small is not that small): -- SQL> set term |; SQL> execute block as declare variable v integer; CON> begin CON> for select id from small into :v do delete from big where id=:v; CON> end CON> | PLAN (BIG INDEX (RDB$PRIMARY1)) PLAN (SMALL NATURAL) -- Finally, the only good solution I could find was this: -- SQL> merge into big using small on (big.id=small.id) when matched then update set data='remove'; PLAN JOIN (SMALL NATURAL, BIG INDEX (RDB$PRIMARY1)) SQL> delete from big where data='remove'; -- Is this really the only efficient way to remove data from one table, based on another one? So set some temporary flag and then remove based on that flag? There must be another way. Regards, Joost
[firebird-support] Performance of deleting records based on another table
Hi all, I finally realized today that I do not know how to properly remove records based on another table. Suppose I have two tables, one with data (called BIG), and one table (SMALL) with only some id's that has to be removed from the BIG-table. I cannot get this to work, without having Firebird looping over all records in BIG naturally, once for each record in SMALL. Example: (Firebrid 3.0.4, but also holds on 2.5) -- SQL> connect "test.fdb"; Database: "test.fdb", User: JOOST SQL> create table big(id integer primary key, data varchar(10)); SQL> create table small(id integer primary key); SQL> insert into big values (1,'adfdvf'); SQL> insert into big values (2,'adfdvf'); SQL> insert into big values (3,'adfdvf'); SQL> insert into big values (4,'adfdvf'); SQL> insert into big values (5,'adfdvf'); SQL> insert into big values (6,'adfdvf'); SQL> insert into big values (7,'adfdvf'); SQL> insert into big values (8,'adfdvf'); SQL> insert into big values (9,'adfdvf'); SQL> insert into big values (10,'adfdvf'); SQL> insert into small values (5); SQL> set planonly on; SQL> delete from big where exists (select 1 from small where small.id=big.id); PLAN (SMALL INDEX (RDB$PRIMARY2)) PLAN (BIG NATURAL) -- Naturally, when BIG is really big, this takes very, very long. I've tried everything. 'where in' does not help, and I can not force it to use some other plan either. One possibility is to use an execute-block. This one is somewhat better (not really - in case small is not that small): -- SQL> set term |; SQL> execute block as declare variable v integer; CON> begin CON> for select id from small into :v do delete from big where id=:v; CON> end CON> | PLAN (BIG INDEX (RDB$PRIMARY1)) PLAN (SMALL NATURAL) -- Finally, the only good solution I could find was this: -- SQL> merge into big using small on (big.id=small.id) when matched then update set data='remove'; PLAN JOIN (SMALL NATURAL, BIG INDEX (RDB$PRIMARY1)) SQL> delete from big where data='remove'; -- Is this really the only efficient way to remove data from one table, based on another one? So set some temporary flag and then remove based on that flag? There must be another way. Regards, Joost
