CASE optimization opportunity (was: filling a key/value table)

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

CASE optimization opportunity (was: filling a key/value table)

Hick Gunter
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:[hidden email]] Im Auftrag von Igor Tandetnik
Gesendet: Donnerstag, 21. März 2019 17:29
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CASE optimization opportunity (was: filling a key/value table)

Igor Tandetnik-2
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:[hidden email]] Im Auftrag von Igor Tandetnik
> Gesendet: Donnerstag, 21. März 2019 17:29
> An: [hidden email]
> 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
> [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: CASE optimization opportunity (was: filling a key/value table)

Hick Gunter
Tried both, no change with SQlite version 3.24. The reference t oval in the WHERE clause causes SQLite to recompute the value.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Igor Tandetnik
Gesendet: Dienstag, 26. März 2019 21:34
An: [hidden email]
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:[hidden email]] Im Auftrag von
> Igor Tandetnik
> Gesendet: Donnerstag, 21. März 2019 17:29
> An: [hidden email]
> 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
> [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: CASE optimization opportunity (was: filling a key/value table)

Hick Gunter
In reply to this post by Igor Tandetnik-2
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:[hidden email]] Im Auftrag von Igor Tandetnik
Gesendet: Dienstag, 26. März 2019 21:34
An: [hidden email]
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:[hidden email]] Im Auftrag von
> Igor Tandetnik
> Gesendet: Donnerstag, 21. März 2019 17:29
> An: [hidden email]
> 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
> [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users