Hello,
I have a large number of queries (maybe up to a few hundred at a time) that I am making against a single table. When performing these queries, the table that I query does not change. The table itself is small (one of the rows is a foreign key). I am trying to get better performance out of this process; I had an idea and want to see if all of my assumptions are correct before I code it (rather than come up with another method).
- set the pragma "temp_store" to MEMORY - CREATE TEMPORARY VIEW temp_table AS (<select whole table>)
I am assuming that temp_table is completely in memory, and any queries against it will not go back to the disk. Since the table itself is small, I am hoping that the overhead of reading the entire table into RAM will be shadowed by the speed improvement of the largish number of queries that don't have to go to the disk. Does this sound reasonable?
--
In anticipation of someone asking what I am actually doing to see if there is a better way to approach my problem, I'll elaborate here, but this is extra credit. :)
Imagine a program that catalogs photographs and assigns keywords to them (think iPhoto). A simple way to describe this is to have three tables: photo, photo_to_keyword, and keyword. Simple. Now, if you think about it this information can be displayed in a hierarchical manner. Imagine the list of all distinct keywords. Select "Vacation" - this will filter the full list of photos to ones that have that keyword. Of all the photos that have the keyword "Vacation", some will be "Bermuda", some will be "Podunk, Nebraska", etc. The UI will display that subset, i.e. the list of all keywords of all photos with "Vacation" as a keyword. Selecting the next will drill down further.
The problem that I am having is "converting" the three relational tables into a hierarchical data structure. I can do it, but the it requires some fairly complex queries, and a large number of them. The result works, but it's horribly slow. Since searches are being done against "keyword"s (strings), the query essentially has to convert the keyword string into a row id, go to the join table, search for all photos with that keyword, get the list of all keyword row ids of those photos, and convert that list to strings for display. This search has to be done not only once for each keyword, but again for the second level since I will need to know if any of them "branches" to a second level. Hence the incredibly slow results.
So, in the case above the temporary table I was hoping to make would be:
CREATE TEMPORARY VIEW photo_to_keyword_temp AS SELECT ptk.photo_id, k.label FROM photo_to_keyword ptk, keyword k WHERE ptk.keyword_id = k.id
This would at least save the trip from and back to the keyword table.
I would appreciate any suggestions or tips!!
Cheers,
Demitri Muna
