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?