sqlite: insert or update a row, performance issue

Question

sqlite table:

CREATE TABLE IF NOT EXISTS INFO
(
    uri TEXT PRIMARY KEY,
    cap INTEGER,
    /* some other columns */
    uid TEXT
);

INFO table has 5K+ rows and is run on a low power device (comparable to a 3 year old mobile phone). I have this task: insert new URI with some values into INFO table, however, if URI is already present, then I need to update uid text field by appending extra text to it if the extra text to be appended isn't found within existing uid string; all other fields should remain unchanged.

As an example: INFO already has uri="http://example.com" with this uid string: "|123||abc||xxx|". I need to add uri="http://example.com" and uid="|abc|". Since "|abc|" is a substring within existing field for the uri, then nothing should be updated. In any case, remaining fields shouldn't be updated

To get it done I have these options:

1. build some sql query (if it's possible to do something like that with sqlite in one sql statement),
2. Do everything manually in two steps: a) retrieve row for uid, do all processing manually and b) update existing or insert a new row if needed

Considering this is constrained device, which way is preferable? What if I omit the the extra requirement of sub-string match and always append uid to existing uid field?

Answer 1

"If it is possible with sqlite in one sql statement":

Yes, it is possible. The "UPSERT" statement has been nicely discussed in this question.

Applied to your extended case, you could do it like this in one statement:

insert or replace into info  (uri, cap, uid) 
values ( 'http://example.com', 
         coalesce((select cap  from info where uri  = 'http://example.com'),'new cap'),
         (select case 
             when (select uid 
                   from  info 
                   where uri  = 'http://example.com') is null 
             then '|abc|'
             when instr( (select uid 
                          from  info 
                          where uri  = 'http://example.com'),'|abc|') = 0 
             then (select uid 
                   from info 
                   where uri  = 'http://example.com') || '|abc|'
             else (select uid 
                  from  info 
                  where uri  = 'http://example.com')
             end )
      );

Checking the EXPLAIN QUERY PLAN gives us

selectid    order       from        detail
----------  ----------  ----------  -------------------------
0           0           0           EXECUTE SCALAR SUBQUERY 0
0           0           0           SEARCH TABLE info USING INDEX sqlite_autoindex_INFO_1 (uri=?)
0           0           0           EXECUTE SCALAR SUBQUERY 1
1           0           0           EXECUTE SCALAR SUBQUERY 2
2           0           0           SEARCH TABLE info USING INDEX sqlite_autoindex_INFO_1 (uri=?)
1           0           0           EXECUTE SCALAR SUBQUERY 3
3           0           0           SEARCH TABLE info USING INDEX sqlite_autoindex_INFO_1 (uri=?)
1           0           0           EXECUTE SCALAR SUBQUERY 4
4           0           0           SEARCH TABLE info USING INDEX sqlite_autoindex_INFO_1 (uri=?)
1           0           0           EXECUTE SCALAR SUBQUERY 5
5           0           0           SEARCH TABLE info USING INDEX sqlite_autoindex_INFO_1 (uri=?)

As far as I know, sqlite will not cache the results of scalar sub-queries (I could not find any evidence of chaching when looking at the VM code produced by EXPLAIN for the above statement). Hence, since sqlite is an in-process db, doing things with two separate statements will most likely perform better than this.

You might want to benchmark the runtimes for this - results will of course depend on your host language and the interface you use (C, JDBC, ODBC, ...).

EDIT

A little performance benchmark using the JDBC driver and sqlite 3.7.2, running 100.000 modifications on a base data set of 5000 rows in table info, 50% updates, 50% inserts, confirms the above conclusion:

1. Using three prepared statements (first a select, then followed by an update or insert, depending on the selected data): 702ms
2. Using the above combined statement: 1802ms

The runtimes are quite stable across several runs.