(I sent an e-mail to the list, and got a nice response, although it didn't really say where I should send enhancement patches. So I'm sending this one here, in the hopes that at least one person will comment on it.)
Currently, the implementation of 'update' and 'delete' for views is such that UPDATE/DELETE FROM MyView WHERE condition is effectively coded as the following steps: 1. INSERT INTO _temptable SELECT * FROM MyView; 2. for each row in _temptable, if "condition" is true, make a note somewhere that we need to process this row 3. for each row we made note of, populate the OLD and NEW pseudotables, then run all of the triggers. This works fine in theory, but it has a slight problem: it makes a complete replication of the view's data, then iterates over that data, in order to work. For a contrived example, I have attached a script that demonstrates this quite well: it builds up a table "numbers" with 4096 rows in it, and then creates a view "numview" which is a simple Cartesian join of "numbers" against itself. This means that a "select * from numview" yields 16777216 rows. By extension, it means that the first step of an UDPATE against numview creates a temporary(ephemeral) table with 16 million rows, then iterates over those 16 million rows. This is a bit slow. To see the problem in action, run the attached SQL script and then try the following statement: update numview set n1=-1 where n1=1 and n2=5; (Be prepared to wait a bit.) My patch restructures an update like "UPDATE view WHERE condition" to behave like this: 1. INSERT INTO _temptable SELECT * FROM (SELECT * FROM MyView) WHERE condition; 2. for each row in _temptable, make a note somewhere that we need to process this row 3. for each row we made note of, populate the OLD and NEW pseudotables, then run all of the triggers. This has the following advantages: 1. Only the rows that are matched by the delete/update's WHERE clause are copied. You can have a 100-million-row view, and it will only copy one row if if your WHERE clause is specific enough. (It will copy no rows if it's a bit too specific!) 2. Indices associated with columns referenced in the WHERE clause have a chance to be used. Oh, and for completeness: I, the author of this patch, dedicate any and all copyright interest in this code to the public domain. I make this dedication for the benefit of the public at large, and to the detriment of myself, my heirs, and my successors. I intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights of this code under copyright law. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE
create table numbers (num int PRIMARY KEY); create table numbers2 (c int); insert into numbers values (0); insert into numbers2 values (1); -- 1 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 2 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 4 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 8 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 16 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 32 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 64 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 128 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 256 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 512 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 1024 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 2048 insert into numbers select (num+c) from numbers join numbers2; update numbers2 set c=(select count(*) from numbers); -- 4096 create view numview as select a.num as n1, b.num as n2 from numbers a, numbers b where a.num is not NULL and b.num is not NULL; create trigger numviewupd instead of update on numview begin update numbers set num=new.n1 where num=old.n1; end;
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------