Forum

[FerrariX]

Hi,

I know there's many threads on this great forum regarding sqlite3 memory but my situation is different.

I'm using sqlite3 as a dictionary for a word game (like Twisty Text).
Every time the player double taps, the word is checked to see if it exists in the dictionary sqlite file. Currently, I'm opening and closing the database for every single query. I do use the reset, finalize and close statements after the prepare statements in the WordQuery() function.

I notice that as the game progresses and more queries are performed, my app becomes very sluggish on 3GS 3.1.2 and crashes with memory error on 3G 3.0. If i let the game run w/o using any query, I don't run into memory issue as bad as using the queries.

My first thought is to speedup the sql lookup by passing the wordlength as well was the word.

What would be the correct sql statement to search an existence of a word given the word and its length?

Another thought, as recommended by others on this forum, is to open the database on application launch and and only close it when application exists?
I know that memory will be higher but will it level off?
What will this memory be given a database file of 2mb?

My other question is how does sqlite3 work on the iphone?
Does it load the entire database and access it as a memory or only load certain portion of data like regular SQL? In other words, does the iphone treat the database just like any other file?

For example, if my sqlite file is about 2mb. when opening this database does it occupy 2mb or more?

This is what I've noticed after a quick test:
The time it takes loading and grabbing 100 words from a database of 183,000 words vs 9,400 on iphone 3G 3.0 and iphone 3GS 3.1.2
(the 3GS test only uses the 9,400 database)
I rebooted the 3G for every test, and exited and relaunched for 3GS

(Time in seconds)
Trial -----------3G 3.0 (183,000) -------3G 3.0 (9,400)---- 3GS 3.1.2 (9,400)
1----------------------3.9s-------------------2.9s----------------1.1s
2----------------------3.5s-------------------3.1s----------------1.4s
3----------------------3.9s-------------------3.0s----------------1.1s


I apologize for the lengthy post but I thought it would be easier to keep it in one post.

Please share your thoughts and experience regarding sqlite performance and memory. (how do you set up sqlite from cvs file, indexing (ascending?), etc)

Thanks,

-FerrariX