3

In all optimization guides people talk about pragma's like JOURNAL_MODE or SYNCHRONOUS, but I never read anything about the TEMP_STORE pragma? I would expect it has a large impact, so why is it never mentioned?

It's purpose is to move all SQLite's internal temporary tables from disk (temp directory) to memory, which seems a lot faster than hitting the disk on every SELECT?

Maestro
  • 9,046
  • 15
  • 83
  • 116

1 Answers1

2

SQLite locks the entire database when doing writes, so I would imagine that it is better to get the data onto the platters before proceeding with your next task.

Putting the data in memory is most likely reserved for those occasions when you would only want a temporary data store (as the TEMP_STORE name implies); you would still need to provide a method for periodically flushing the data to disk (if you want to save it), and since the locking is not granular, you would have to flush the entire database.

In other words, TEMP_STORE is not a caching mechanism.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • If the pragma only influences in-memory databases (created using :memory:) then you're right. – Maestro Sep 26 '11 at 15:44
  • 3
    Looking at the [documentation for TEMP_STORE](http://www.sqlite.org/pragma.html#pragma_temp_store), it appears to influence the use of temporary tables and indices only. The temporary tables and indexes are only used if the page caches get full, see: http://www.sqlite.org/tempfiles.html. So the reason it's not mentioned in the optimization guides could be that it doesn't matter all that much under normal (i.e. not heavily loaded) conditions. – Robert Harvey Sep 26 '11 at 16:03