pierr wrote: > Following 2 statements took 400ms to be excuted on a 350M MIPS CPU > and it is a memory database: > > "DELETE FROM tblEvent_type WHERE eguid in (SELECT rowid FROM > tblEvent_basic WHERE sguid=11);"; > "DELETE FROM tblEvent_group WHERE eguid in (SELECT rowid FROM > tblEvent_basic WHERE sguid=11);"; > (An index has been created on tblEvent_group(eguid) , no other index > so far.)
You might benefit from an index on tblEvent_basic(sguid). An index on tblEvent_group(eguid) may actually hurt performance. > And also I notice that "(SELECT rowid FROM tblEvent_basic WHERE > sguid=11)"`was excuted twice ,so I'd like to try to split the > subquery to something like below to see if there will be any > performance gain: > > result = exe_sql "(SELECT rowid FROM tblEvent_basic WHERE > sguid=11);"; exe_sql "DELETE FROM tblEvent_type WHERE > eguid in (result) exe_sql "DELETE FROM tblEvent_group > WHERE eguid in (result) > > How to get the parmater (result) binding to follwing statement in > sqlite? You can't reuse a resultset this way. You could try a temporary table: create temp table Ids (id integer primary key); insert into Ids(id) select rowid from tblEvent_basic where sguid=11; DELETE FROM tblEvent_type WHERE eguid in (SELECT id FROM Ids); DELETE FROM tblEvent_group WHERE eguid in (SELECT id FROM Ids); drop table Ids; Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users