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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
