Temporary table store in SQLite on the iPhone

Question

I use temporary tables in an SQLite based iPhone application. Performance was not as was required so I looked at various candidates for optimization. Most of these worked well and had the desired effect. However, I'd also like to try moving the temporary tables from flash into memory to see if there is any improvement.

On an actual device I try and configure this using:

PRAGMA temp_store = 2;

These executes without error. However, when I query the configuration using:

PRAGMA temp_store;

No results are returned so I am unable verify that this configuration is in effect. I can read other PRAGMA settings such as 'page_size'. Can anyone tell me:

• If this is expected behavior
• If this is an unsupported configuration in SQLite on the iPhone
• If such an optimization would be pointless on the iPhone even if it were possible (due perhaps to the hardware)
• How I can successfully move temporary table storage into memory in this scenario

Note: I am well aware of Core Data in SDK 3.0 but I have good reasons for not using it.

Update: jitter pointed out that the SQLite instance on the iPhone may have been compiled with SQLITE_TEMP_STORE set to always use temporary files. I wasn't aware of this setting - is there any way to confirm that this is the case?

Answer 1

I don't know but guess they compiled the sqllite with SQLITE_TEMP_STORE=0. And there is another guy who tried this and didn't succeed. SQLite optimization on the iPhone

• List item

I can't try it out myself but there seems to be a sqlite3TempInMemory() function in the source too (src/main.c). Don't know if you can call that.

This function returns true if main-memory should be used instead of a temporary file for transient pager files and statement journals. The value returned depends on the value of db->temp_store (runtime parameter) and the compile time value of SQLITE_TEMP_STORE. The following table describes the relationship between these two values and this functions return value.

Answer 2

I just added the SQLite source myself to my projects, because the SQLite on at least the iPhone OS 2.x is not thread safe.