JoP JoP -3 years ago 82
Vb.net Question

SQLite query with unknown foreign key

I am playing around with a SQLite database in a vb.net application. The database is supposed to store time series data for many variables.
Right now I am trying to build the database with 2 tables as followed:


  1. 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
... | ...



  1. 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
... | .... | ...


The first table contains all variable names with IDs. The second table contains the values of each variable for many time steps (indicated by the timestamps). A foreign key links the tables through the variable IDs.

Building up the database works fine.

Now I want to query the database and plot the time series for selected variables. For this I use the following statement:

select [timestamp], [varValue] FROM varValues WHERE (SELECT id from varNames WHERE varName= '" & NAMEvariable & "');


Since the user does not know the variabel ID, only the name of the Variable (in NAMEvariable) I use the ..WHERE (SELECT... statement. It seems like this really slows down the performance. The time series have up to 50k points.

Is there any better way to query values for a specific variable which can only be addressed by its name?

Answer Source

You probably should use a join query, something like:

SELECT a.[timestamp], a.varValue
FROM varValues AS a, varNames AS b
WHERE b.varName = <name>
  AND a.id = b.ID

edit: To query for more than one parameter, use something like this:

SELECT a.[timestamp], a.varValue
FROM varValues AS a, varNames AS b
WHERE b.varName IN (<name1>, <name2>, ...)
  AND a.id = b.ID
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download