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
