Should I be concerned about the number of local db calls I make with mobile app and web?

Question

I am building a flutter app with web support and using drift as local storage db. I have a table with following schema:

CREATE TABLE Tasks(
   Id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
   Name TEXT NOT NULL,
   Priority INT NOT NULL
   IsSynced BOOLEAN DEFAULT false);

Is it okay to make different drift asynchronous db calls to get the records with different priority that is not synced or should I write a single drift query to get all the non synced records and separate them based on priority in code. I know it does not make much different in this scenario. Since drift is used for local storage. Should I be concerned with the number of db calls.

Answer 1

First

Id INT NOT NULL PRIMARY KEY AUTOINCREMENT, 

will result in an error as AUTOINCREMENT is only allowed if the column type is specifically INTEGER. You would get a failure as AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

Second

If you are considering efficiency then consider if you REALLY need AUTOINCREMENT.

• AUTOINCREMENT confusingly does not actually result in a unique id being generated rather it is a constraint(rules) that imposes the condition that the value, if generated, MUST be greater than any value that has existed (if it does not).

• The constraint/rule is afforded by an additional table sqlite_sequence which, a row per table, holds the highest ever assigned id value.

  • Accessing and maintaining this table, albeit it automatic, does impose additional resource utilisation.
  • each table requires, at the minimum a page (default 4k) hence 4k to perhaps cater for a fraction of that page

• see https://www.sqlite.org/autoinc.html for a more comprehensive overview that also makes mention of the rowid which if INTEGER PRIMARY KEY is used is aliased by the column with that definition.

• note the very first statement.

Should I be concerned with the number of db calls.

Regarding the number of db calls, it depends on the usage scenario. Will the single call, when not split, return a great deal of data. If so all data in a single chunk may be less efficient to handle, this versus multiple calls where the communication with the server could be the bottleneck.

You should undertake research based upon your needs.

Demonstration of AUTOINCREMENT v just INTEGER PRIMARY KEY (and column type flexibility)

Perhaps consider the following code (used in an SQLite Tool (Navicat for SQLite in this case)):-

/* Just in case the environment is dirty */
DROP TABLE IF EXISTS Tasks;
DROP TABLE IF EXISTS Tasks2;
/* Create the question's table (corrected by changing INT to INTEGER) */
CREATE TABLE IF NOT EXISTS Tasks(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, priority int NOT NULL, issynced boolean_or_whatever_due_to_column_type_flexibility DEFAULT false);
/* The comparison */
/*      NOTE without AUTOINCREMENT */
/*      NOTE demonstration of flexible column types
        i.e. a column type of the ridiculous "boolean_or_whatever_due_to_column_type_flexibility" column type
            will be given a type affinity of NUMERIC due to BOOL (see 3.1.1 of https://sqlite.org/datatype3.html)
*/
CREATE TABLE IF NOT EXISTS Tasks2(id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, priority int NOT NULL, issynced boolean_or_whatever_due_to_column_type_flexibility DEFAULT false);
/* Insert base starting row for both tables */
INSERT INTO tasks VALUES(1000,'namexxx',99,1);
INSERT INTO tasks2 SELECT * FROM tasks;
/* insert another 1000 rows into tasks table */
WITH
    /* create CTE (Common Table Expression (a temporary table that exists for the duration of the execution)) 
        with 1 column, named i, that has a value 1, 2 3 ... to 1000 */
    cte_generate_many(i) AS (
        SELECT 1 /* first row */
        UNION ALL 
            SELECT 
                i+1 /* 1 greater than the current row from the cte, so ever increasing */ 
                FROM cte_generate_many 
                LIMIT 1000 /* recursively add rows till stopped (LIMIT 1000) */ 
    )
INSERT INTO tasks (name,priority,issynced)
    SELECT 
        'name'||i, /* Name will be "name?" where ? is 1 then 2 then 3 .... */
        abs(random() % 10) + 1, /* priroity will be random between 1 and 10 */
        (i % 3) > 0 
        FROM cte_generate_many
;
/* insert another 1000 rows into tasks2 table (comparison) same as previous bar into tasks2 not tasks table */
WITH
    cte_generate_many(i) AS (
        SELECT 1
        UNION ALL SELECT i+1 FROM cte_generate_many LIMIT 1000
    )
INSERT INTO tasks2 (name,priority,issynced)
    SELECT 'name'||i,
        abs(random() % 10) + 1,
        (i % 3) > 0 
        FROM cte_generate_many
;
/* show the 2 tables and then sqlite sequence */
SELECT * FROM tasks;
SELECT * FROM tasks2;
SELECT * FROM sqlite_sequence;
/* demonstrate that AUTOINCREMENT constrain is not applied if value is given (not generated) */
INSERT INTO tasks VALUES(-1000,'namezzzz',99,1);
INSERT INTO tasks2 VALUES(-1000,'namezzzz',99,1);
SELECT * FROM tasks WHERE id < 1 UNION ALL SELECT * FROM tasks2 WHERE id < 1;
/* Cleanup */
DROP TABLE IF EXISTS Tasks;
DROP TABLE IF EXISTS Tasks2;

• note the comments which try to explain much of what is being done

The above results in 4 results. They are:-

Result 1

• 

through to:-

• 

Result 2 (i.e. very much the same table but without AUTOINCREMENT)

• also the ridiculous column type that demonstrates SQLite flexibility with column types (unless STRICT is used) see https://sqlite.org/datatype3.html

• 

through to:-

• 

Result 3

• 

• i.e. no row for tasks2 table (so no overheads for tasks2 table)

Result 4

• 

• i.e. no issue inserting rows with values lower than the highest if providing the value (irrespective of AUTOINCREMENT)

Discussion of 1 v 2

In short there is no difference other than the priority which is based upon a random value. So as far as the 1000 rows is concerned AUTOINCREMENT or not makes no difference.

• if AUTOINCREMENT is used and the row with highest id is deleted the next generated id will be as if the row still existed. Whilst without AUTOINCREMENT the next insert would (typically) be the same as the deleted row (if the deleted row was the highest id).

As can be see the ridiculous column name makes no real difference (in fact BOOLEAN itself is not one of the actual column affinity types, these being NULL, INTEGER, REAL, TEXT, BLOB and NUMERIC). However bar the rowid or an alias thereof (which id is in both tasks and tasks2) then a column can hold a value of any type, which is a feature that most other RDB's do not offer.

When it comes down to AUTOINCREMENT this keyword was chosen to allow SQLite to be more compatible with other RDB's.