I believe your choice of query is not good enough. Try this one:
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')
Pavel
On Tue, Jul 14, 2009 at 12:54 PM, Adam DeVita<adev...@verifeye.com> wrote:
> Good day,
>
> Could someone explain where I'm going wrong with this?
>
> 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)
>
> The purpose is to import a data from a remotely created change file, with
> only new/newer records. (Due to the fact that the subject of the data is
> shipping / receiving product serial numbers and that data moves faster than
> product there is no way independent nodes can create a change to a record at
> the same time. Also, deleting is not allowed.)
>
> The change file is attached as 'delta'
>
> The structure of masterlist in the main database is:
> sqlite> .schema masterlist
> CREATE TABLE MasterList (SN int primary key not null, TypeID int default 0
> references Product_type_dictionary(TypeID) , ConstructionDate text, MFGID
> int default 0 references MFG_dictionary (MFGID), Init_cust_ship_date text,
> record_updatetime text default "2000.00.00.00", write_out_ok int default 0);
>
> CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN);
>
> main.masterlist has 36,000 records
> deltas.masterlist has 9,000 records
>
> Notes about fields:
> write_out_ok is a flag indicating that the record has been imported. States
> are 1 or 0.
> MFGID is a manufacturer, about 4 different ints can be used.
> TypeID is a product Type, about 7 different types,
>
> The index is ordered by cardinality, and all int.
> record_updatetime is the modified date & time GMT (UTC),
> yyyy.mm.dd.hh.MM.ss
>
>
> ------------
> Experimenting with indexes on the delta file with
> No indexes:
> 7 min 22s
>
> CREATE INDEX IDX_MasterList on MasterList ( SN);
> 14min 52s
>
> CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN);
> 20 min, 07s
>
> -----------
> Dropped indexes on both main and delta.
> ~20 min.
> ---------------------
>
> Is the real problem a poor choice of index in main?
>
>
> regards,
> Adam
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
