Hello,
As mentioned in the subject, our goal is to improve performance regarding
to batch sql updates.
The update sql as follow,

> UPDATE ITEM SET FIELD4 =? WHERE DS=?

We run 100,000 updates in a single transaction. The zipvfs version takes
about 20 min while uncompressed version takes about 7 min.
I've also attached results of "EXPLAIN UPDATE ITEM SET FIELD4 = 10 WHERE DS
= 15;".

Any suggestions is very helpful.

Some background:
We have an app running on Android 4.2 using zipvfs.
The page size is 4096
The cache size is - 2000

Table schema for ITEM

> CREATE TABLE ITEM (
> FIELD0 NUMERIC,
> FIELD1 NUMERIC,
> DS TEXT,
> FIELD2 TEXT,
> FIELD3 TEXT,
> FIELD4 NUMERIC,
> FIELD5 NUMERIC,
> FIELD6 NUMERIC,
> FIELD7 NUMERIC,
> FIELD8 NUMERIC,
> FIELD9 NUMERIC,
> FIELD10 NUMERIC,
> FIELD11 TEXT);


The third column: "DS" is what we query by almost all the time. We also
created index:

> CREATE INDEX DS_INDEX ON ITEM(DS);


There are 30 million records. Zipvfs size of 471mb vs uncompressed db (the
one shipped with Android 4.2) size of 1.39gb.
Zipvfs db using zlib and aes128, which are default.

Thanks
-- 

Yue Wu |  Android Developer
sqlite> EXPLAIN UPDATE ITEM SET FIELD4 = 10 WHERE DS = 15;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     28    0                    00  Start at 28
1     Null           0     1     2                    00  r[1..2]=NULL
2     OpenWrite      0     3     0     13             08  root=3 iDb=0; ITEM
3     OpenWrite      1     4     0     k(2,,)         02  root=4 iDb=0; ds_index
4     Integer        15    16    0                    00  r[16]=15
5     Affinity       16    1     0     B              00  affinity(r[16])
6     SeekGE         1     27    16    1              00  key=r[16]
7       IdxGT          1     27    16    1              00  key=r[16]
8       DeferredSeek   1     0     0                    00  Move 0 to 1.rowid 
if needed
9       Rowid          0     2     0                    00  r[2]=rowid
10      IsNull         2     27    0                    00  if r[2]==NULL goto 
27
11      Column         0     0     3                    00  r[3]=ITEM.FIELD0
12      Column         0     1     4                    00  r[4]=ITEM.FIELD1
13      Column         0     2     5                    00  r[5]=ITEM.DS
14      Column         0     3     6                    00  r[6]=ITEM.FIELD2
15      Column         0     4     7                    00  r[7]=ITEM.FIELD3
16      Integer        10    8     0                    00  r[8]=10
17      Column         0     6     9                    00  r[9]=ITEM.FIELD5
18      Column         0     7     10                   00  r[10]=ITEM.FIELD6
19      Column         0     8     11                   00  r[11]=ITEM.FIELD7
20      Column         0     9     12                   00  r[12]=ITEM.FIELD8
21      Column         0     10    13                   00  r[13]=ITEM.FIELD9
22      Column         0     11    14                   00  r[14]=ITEM.FIELD10
23      Column         0     12    15                   00  r[15]=ITEM.FIELD11
24      MakeRecord     3     13    17    CCBBBCCCCCCCB  00  
r[17]=mkrec(r[3..15])
25      Insert         0     17    2     ITEM           07  intkey=r[2] 
data=r[17]
26    Next           1     7     1                    00
27    Halt           0     0     0                    00
28    Transaction    0     1     4     0              01  usesStmtJournal=0
29    Goto           0     1     0                    00
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to