Also, very good.
No index on Delta File: 3 seconds. Index on SN: 4 seconds. Index on MasterList (write_out_ok, MFGID, TypeID, SN); 4 seconds. Time is to the nearest second in my test program, so I can't distinguish between the two. In summary: /*FAST */ insert or replace into main.masterlist select d.* from delta.masterlist d left outer join main.masterlist M on d.sn = M.sn where d.write_out_ok=0 and d.record_updatetime >= ifnull(M.record_updatetime, '0000') /* just as FAST*/ insert or replace into main.masterlist select * from delta.masterlist d where d.write_out_ok=0 and not exists (select 1 from main.masterlist M where M.sn = d.sn and M.record_updatetime > d.record_updatetime); /* very SLOW*/ insert or replace into main.masterlist select * from delta.masterlist d where d.write_out_ok=0 and d.sn not in(select M.sn from main.masterlist M where M.record_updatetime > d.record_updatetime) On Wed, Jul 15, 2009 at 7:33 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Adam DeVita wrote: > > I've identified the following query as a bottle neck in a utility I've > > written. > > > > insert or replace into main.masterlist select * from delta.masterlist > > d where d.write_out_ok=0 and > > d.sn not in(select M.sn from main.masterlist M where > > M.record_updatetime > d.record_updatetime) > > Try this: > > insert or replace into main.masterlist > select * from delta.masterlist d > where d.write_out_ok=0 and > not exists (select 1 from main.masterlist M > where M.sn = d.sn and M.record_updatetime > d.record_updatetime); > > It appears that your query doesn't use an index on M(sn), while mine > does. > > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
