vendredi 12 juin 2015

Efficient way to load referenced data in one query

My application uses a database to save its data. I have table Objects that looks like

localID  |   title   |  content
1            Test       "1,embed","3,embed","5,append"

and another table Contents that looks like

localID  |  content
1           Alpha
2           Beta
3           Gamma
4           Delta
5           Epsilon

The main applications runs in the main thread, the whole database stuff in a second thread. So if my application loads, I want to pass each record (QSqlRecord) to the main thread where it gets further processed (loaded into real objects). I pass that record via signals. But my data is split up into 2 tables. I want to return a record containing both, perhaps similar to a join:

localID  |   title   |  content
1            Test       "Alpha,embed","Gamma,embed","Epsilon,append"

So this way, I would have all the needed information at once after only one thread return value. Without combining, I would have to call the database for each single referenced content. I expect the database to contain less than 100.000 records, yet some content may be big (files saved as blob, e.g. a book of size of 300 mb or so).

I have two questions:

  • (How) Can I join the tables this way inside a query (efficiently)?
  • Am I too concerned about threading and should make it single threaded? That way I would not need to bother with multiple read requests.

As a sidenode, this is my first post on Database Admins, I was not too sure about this site or Stackoverflow being the right place to ask this.

Aucun commentaire:

Enregistrer un commentaire