SQLite relational INSERT with unknown foreign key

Question

I made an SQLite database in a VB.NET application. It stores time series data for multiple varName records over 2 tables:

Table varNames :

CREATE TABLE IF NOT EXISTS varNames(
    id      INTEGER PRIMARY KEY,
    varName TEXT    UNIQUE
);

It looks like this:

ID   |  varName
---------------
1    |  var1
2    |  var2
...  |  ...

Table varValues :

CREATE TABLE IF NOT EXISTS varValues(
    timestamp INTEGER,
    varValue  FLOAT,
    id        INTEGER,
    FOREIGN KEY(id) REFERENCES varNames(id) ON DELETE CASCADE
);

It looks like this:

timestamp  |  varValue  |  id
------------------------------
1          |  1.0345    |  1
4          |  3.5643    |  1
1          |  7.7866    |  2
3          |  4.5668    |  2
...        |  ....      | ...

First table contains varName with ID. The second contains the values of each varName as time series (per timestamp). A foreign key links the tables. Insert into varNames looks like this:

INSERT OR REPLACE INTO varNames (
    varName
) VALUES (
    @name
);

I insert values for a specific varName to the second table like this:

INSERT OR REPLACE INTO varValues (
    timestamp,
    varValue,
    id
) VALUES (
    @timestamp,
    @value,
    (SELECT id FROM varNames WHERE varName = @name)
);

I don't know the varName's ID for the corresponding varValues record at the time of insert. This is why I use :

(SELECT id FROM varNames WHERE varName = @name)

Seems slow compared to addressing by ID directly. How can I improve INSERT performance into the second table?

Depends on how you do things... It shouldn't be slow if you have little amount of data. You could store the value of the id in your .net code if that's possible or get the [last inserted row id](https://stackoverflow.com/questions/2127138/how-to-retrieve-the-last-autoincremented-id-from-a-sqlite-table). — the_lotus, Jun 16 '17 at 12:09

score 1 · Answer 1 · answered Jan 23 '18 at 06:10

… improve the INSERT performance into the second table?

Use a transaction (preferably combine multiple INSERT):

BEGIN TRANSACTION;

INSERT OR REPLACE INTO varValues (
    timestamp,
    varValue,
    id
) VALUES (
    @timestamp,
    @value,
    (SELECT id FROM varNames WHERE varName = @name)
);

INSERT OR REPLACE INTO varValues (
    timestamp,
    varValue,
    id
) VALUES (
    @timestamp,
    @value,
    (SELECT id FROM varNames WHERE varName = @name)
);

etc. ...

END TRANSACTION;

Related.

SQLite relational INSERT with unknown foreign key

1 Answers1