Additionally, SQLite is not honoring the ON CONFLICT IGNORE, even if I add NOT NULL ON CONFLICT IGNORE to each of the key fields. Instead, it is always generating HaltIfNull operations, i.e. ON CONFLICT ABORT.
33 HaltIfNull 1299 2 2 vals.keyid 01 if r[2]=null halt 34 HaltIfNull 1299 2 3 vals.value 01 if r[3]=null halt 35 HaltIfNull 1299 2 4 vals.period_no 01 if r[4]=null halt 36 HaltIfNull 1299 2 5 vals.sync_offset 01 if r[5]=null halt If SQLite were to generate "IsNull <register> <endofloop> 0" instead, the rows with NULL values would be ignored automatically, removing the need for the "WHERE value IS NOT NULL" check. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Igor Tandetnik Gesendet: Dienstag, 26. März 2019 21:34 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] CASE optimization opportunity (was: filling a key/value table) See if something like this works better: INSERT INTO logidx(keyid,value,location) SELECT id, val, location from ( SELECT k.id id, (case k.name when 'type' then l.type when 'name' then l.name when 'size' then l.size else NULL end) AS val, l.location location FROM logfile l, logkey k ) WHERE val IS NOT NULL; Or WITH RawData AS ( SELECT k.id id, (case k.name when 'type' then l.type when 'name' then l.name when 'size' then l.size else NULL end) AS val, l.location location FROM logfile l, logkey k ) INSERT INTO logidx(keyid,value,location) SELECT id, val, location from RawData WHERE val IS NOT NULL; Igor Tandetnik On 3/26/2019 10:15 AM, Hick Gunter wrote: > This works as expected, thanks. > > SQLite implements this as a pair of coroutines: > > Routine A) does a nested scan of the logfile (outer loop = just read > the logifle once) and the logkey table (inner loop) > > Routine B) does the actual insert(s) into the logidx table > > Unfortunately, the necessary check of the generated val causes the CASE > expression to be evaluated twice (see bytecode). > > Instructions 16 thru 25 implement the CASE, leaving R12 holding the > computed value Instruction 26 checks that something the evaluates to > TRUE has been found Instructions 28 thru 37 duplicate the CASE, > leaving R3 with the re-computed value > > The same effect could be achieved by copying R12 into R3 when buildung > the record, just as R11 is copied to R4 > > 9 VFilter 0 43 7 00 iplan=r[7] zplan='' > 10 VColumn 0 47 11 00 > r[11]=vcolumn(47); atx_txlog.period_no > 11 Lt 13 42 11 (BINARY) 53 if r[11]<r[13] > goto 42 > 12 Gt 14 42 11 (BINARY) 53 if r[11]>r[14] > goto 42 > 13 Explain 13 0 0 SCAN TABLE keys AS k 00 > 14 Rewind 1 43 0 00 > 15 Column 1 1 15 00 r[15]=keys.name > 16 Ne 17 19 15 (NOCASE) 52 if r[15]!=r[17] > goto 19 > 17 VColumn 0 15 12 00 > r[12]=vcolumn(15); atx_txlog.event_type > 18 Goto 0 26 0 00 > 19 Ne 18 22 15 (NOCASE) 52 if r[15]!=r[18] > goto 22 > 20 VColumn 0 28 12 00 > r[12]=vcolumn(28); atx_txlog.retailer_loc_id > 21 Goto 0 26 0 00 > 22 Ne 19 25 15 (NOCASE) 52 if r[15]!=r[19] > goto 25 > 23 VColumn 0 64 12 00 > r[12]=vcolumn(64); atx_txlog.ticket_key_string > 24 Goto 0 26 0 00 > 25 Null 0 12 0 00 r[12]=NULL > 26 IfNot 12 41 1 00 > 27 Rowid 1 2 0 00 r[2]=rowid > 28 Ne 17 31 15 (NOCASE) 52 if r[15]!=r[17] > goto 31 > 29 VColumn 0 15 3 00 > r[3]=vcolumn(15); atx_txlog.event_type > 30 Goto 0 38 0 00 > 31 Ne 18 34 15 (NOCASE) 52 if r[15]!=r[18] > goto 34 > 32 VColumn 0 28 3 00 > r[3]=vcolumn(28); atx_txlog.retailer_loc_id > 33 Goto 0 38 0 00 > 34 Ne 19 37 15 (NOCASE) 52 if r[15]!=r[19] > goto 37 > 35 VColumn 0 64 3 00 > r[3]=vcolumn(64); atx_txlog.ticket_key_string > 36 Goto 0 38 0 00 > 37 Null 0 3 0 00 r[3]=NULL > 38 Copy 11 4 0 00 r[4]=r[11] > 39 VColumn 0 6 5 00 > r[5]=vcolumn(6); atx_txlog.sync_offset > 40 Yield 6 0 0 00 > 41 Next 1 15 0 01 > 42 VNext 0 10 0 00 > > -----Ursprüngliche Nachricht----- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von > Igor Tandetnik > Gesendet: Donnerstag, 21. März 2019 17:29 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [EXTERNAL] Re: [sqlite] filling a key/value table > > On 3/21/2019 12:04 PM, Hick Gunter wrote: >> I have an external data store that is accessible to sqlite as a virtual >> table. The equivalent SQL declaration is similar to: >> >> CREATE TABLE logfile (timestamp INTEGER, type INTEGER, name TEXT, >> size INTEGER, ...); >> >> I would like to create an index as a native SQLite table declared like: >> >> CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, >> primary key (keyid, value, location) ) WITHOUT ROWID; >> >> The fields of interest are stored in a config table: >> >> CREATE TABLE logkey( ID INTEGER PRIMARY KEY, name TEXT ); INSERT INTO >> logkey(name) VALUES ('type'),('name'),('size'); >> >> The naive method of inserting values is thus: >> >> INSERT INTO logidx(keyid,value,location) SELECT >> k.id,l.type,l.location from logkey k, logfile l where k.name = >> 'type'AND l.type IS NOT NULL; INSERT INTO >> logidx(keyid,value,location) SELECT k.id,l.name,l.location from >> logkey k, logfile l where k.name = 'name'AND l.name IS NOT NULL; >> INSERT INTO logidx(keyid,value,location) SELECT >> k.id,l.size,l.location from logkey k, logfile l where k.name = >> 'size'AND l.size IS NOT NULL; >> >> This hast he disadvantage of requiring a complete scan of the virtual logidx >> table for each kind of entry. >> >> Any ideas on how to create all the tuples with only one pass of the logidx >> table? > > Something like this: > > INSERT INTO logidx(keyid,value,location) SELECT k.id, > (case k.name when 'type' then l.type when 'name' then l.name when 'size' > then l.size else NULL end) AS val, > l.location > FROM logfile l, logkey k WHERE val IS NOT NULL; > > -- > Igor Tandetnik > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick | Software Engineer | Scientific Games International > GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: > 0430013 | (O) +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
