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
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)
What would be the correct sql statement to search an existence of a word given the word and its length?
If you're going to do this, it's probably best to create a separate "word length" column in your database. Asking SQLite to calculate the length of each word every time you query is likely to slow things down further.
Quote:
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?
I believe (but I can't find a supporting web page at the moment) that SQLite mem-maps its datafile. This is faster than loading it all into memory and means that the VM system is free to free up memory when it's not in use.
Also, I don't think that the overhead of keeping the database open through out your application will out weigh the speed bump you get by not having to open it each time you want to look something up, so do that.
What's your table schema like? If you're worried about performance, that's probably the first place to look. A quick Google found this page on optimising SQLite. I suggest that as a minimum you employ the UNIQUE keyword for your word column (although this will probably result in a slightly larger database file).
(But having typed all that.. for my word game "Lexical", I used a custom binary-tree like structure which was a little more complicated to set up but faster to work with and compressed about 50,000 words of seven letters or less into about 560k.)
__________________ SimCap - Simple iPhone and iPad Simulator screen capture
I too am working on a word game, however I am not having the issues you described. I open the database each time I query (160,000+ words, 6mb), and it runs fine on a 1st gen ipod touch (3.0 firmware). I of course have an index on my word column (which doubles the size of the db), but it is not a unique index (for various reasons).
Are you sure you are closing all results and the database? You could try using the FMDB wrapper of sqlite (trunk/fmdb - flycode - Project Hosting on Google Code), which takes care of the closing/finalizing crap for you.
If you're going to do this, it's probably best to create a separate "word length" column in your database. Asking SQLite to calculate the length of each word every time you query is likely to slow things down further.
Thanks for your help sjc,
I check for the word length first to see if the length >= 3, then also check to see if it also exists in AlreadyFound[], if both of these conditions are met, then I send it to Query function.
Please tell me what is the statement to check if the word exist by passing it the word and its length.
Quote:
I believe (but I can't find a supporting web page at the moment) that SQLite mem-maps its datafile. This is faster than loading it all into memory and means that the VM system is free to free up memory when it's not in use.
this is great , i don't have to worry about db size that much.
Quote:
What's your table schema like? If you're worried about performance, that's probably the first place to look. A quick Google found this page on optimising SQLite. I suggest that as a minimum you employ the UNIQUE keyword for your word column (although this will probably result in a slightly larger database file).
(But having typed all that.. for my word game "Lexical", I used a custom binary-tree like structure which was a little more complicated to set up but faster to work with and compressed about 50,000 words of seven letters or less into about 560k.)
Thanks for the link. Would you please explain the UNIQUE key word?
I will definitely try the bst once I get the simple sqlite working properly.
Hey, I just looked at your numbers... that definitely does not look right. Post your table schema.
Hi opetopic,
Thanks for your help
The numbers is not for just grabbing the 100 words from the database. It initializes variables among other things. For those tests, I've kept everything the same except changing the database size to see how it would affect speed.
I use two SQLite files, one is for dictionary and the other for word selection that has been randomized. I randomized the second database of words so the iPhone doesn't have to do this processing, just select a chunk of word from a randomly chosen starting index. Would it be better to combined the randomized table into the dictionary table as another column?
I know this will increase the size.
The Dictionary database is the one I send queries to.
I use Sqlite Manager pluggin for FireFox to create my Sqlite database.
I choose to import the cvs file then give it a table name "EnglishD"
then selected "First row contain name columns" and left everything default.
I then clicked okay. it then ask me if i want to modify it. I click okay.
Under Define column, my column names are word and length
I change the data type to TEXT, should I make this int? any gain in performance?
There is an option for "allow null", should I check that or not? I left it default as checked.
I then created an index and selected "Ascending" for both columns.
I guess my schema would be a column of unique words shorted in ascending order and another column with length also sorted in ascending order.
Is it acceptable for me to post sqlite file on this forum?
I don't think you need a separate database for randomized words. It isn't worth the extra space when there are easy ways to do it. First, do you select the 100 random words seldom (say at the beginning of a game)? If so, then you might be able to get away with the following query for getting 100 random words:
Code:
SELECT * FROM words ORDER BY RANDOM() LIMIT 100
This is usually a no-no, but with only ~150,000 the execution time should be negligible.
Also, drop the length column as it will not speed up your queries. If your columns are properly indexed searching for a word should be instant. Further, change the "word" column's data type to VARCHAR.
You say that you have an ASCENDING index on both the word column and length column. Such an index will be used only if you are using BOTH of those columns in an AND clause in your query. Just put one ASCENDING index on the word column.
Finally, after doing all of this run the VACUUM command on your database (I believe this is under Database > Compact Database in the Firefox extension you mentioned). See if that helps any...
P.S. I don't see any reason why you couldn't upload the sqlite file, but I don't know if the forum has attachment size restrictions.
I only select the 100 random words once during start up unless the player has used up all the words. I should've said scrambled words instead of randomized. They are randomly sorted in a column.
The scrambled words are what appear the beginning of each level.
For example, the word PERFORMANCE may appear as OMRPRECENA.
I do this for infinite mode (no timer).
Can you or anyone else suggest a different tool to for working with SQLite?
There's a 97.7kb limit for file attachment.
I'll find someway to link it.
I don't think you need a separate database for randomized words. It isn't worth the extra space when there are easy ways to do it. First, do you select the 100 random words seldom (say at the beginning of a game)? If so, then you might be able to get away with the following query for getting 100 random words:
Code:
SELECT * FROM words ORDER BY RANDOM() LIMIT 100
This is usually a no-no, but with only ~150,000 the execution time should be negligible.
Also, drop the length column as it will not speed up your queries. If your columns are properly indexed searching for a word should be instant. Further, change the "word" column's data type to VARCHAR.
You say that you have an ASCENDING index on both the word column and length column. Such an index will be used only if you are using BOTH of those columns in an AND clause in your query. Just put one ASCENDING index on the word column.
Finally, after doing all of this run the VACUUM command on your database (I believe this is under Database > Compact Database in the Firefox extension you mentioned). See if that helps any...
P.S. I don't see any reason why you couldn't upload the sqlite file, but I don't know if the forum has attachment size restrictions.
Hi opetopic,
i'm back with more quesitons (work was overwhelming yesterday, not complaining, it's good to still have a job nowadays)
This is the link for my sqlite file, I have not ran VACCUM yet:
You sqlite file actually looks fine ... I changed your word column to be a VARCHAR, and then compacted it and it reduced the file size by half, but I guess that is not necessary.
Perhaps you should post the code that is running slowly as I do not have any more ideas.
Also, I do _not_ think you should combine multiple languages into one table. You could combine them into one sqlite file but many tables if you wanted, or just keep it as separate sqlite files.
sqlite3 memory and performance tips
, i don't have to worry about db size that much.
Linear Mode
