SQLite3 performance question

[xtr33me]

I currently have a DB with my data spread across 12 tables. Because this is my first iphone app I am unsure as to the best practices on the iphone due to memory constraints. I have 4 tabs, all of which access data from the DB. Currently my db is loaded in my appDelegate and this is where I will be writing my methods to query the DB.

My question is, is it better to populate most of the data once and let it reside in memory across the tabs or is it better to just populate certain variables when I need them, keep them resident in memory and when they flip the view release the data and populate the info for that view?

I've scoured the net and found numerous articles, but none really for a more complex setup for a db. And then ofcourse all the articles on SQL, don't need to usually worry about memory so often they will keep the object around till they don't need it anymore.

I am really using this project as a learning project, so I would prefer to know the proper way of handling this. If anyone can help or give me a good swift kick in the right direction it would be greatly appreciated. Thanks everyone!!

[Lars]

I vote that you query the data when you need it and release it when you are done. If you see bad performance, you can think of changing the method, but I have a feeling performance won't be bad.

Just to give you an example, I created the game downWORD which is a tetris style game where letters fall and you place them trying to spell words. I needed to query for various matches both vertically and horizontally. If a word is found, the row disappears and all tiles slide down. Then I must query against all possible words in all directions. I'm doing this against the SOWPODS database with 189,000 words and soon with a French dictionary with 212,000 words. I'm really able to run upwards of 30 queries in less than a second so that the game doesn't show any slowdown checking for the words.

My guess is as long as you setup your indexes correctly you shouldn't have any problems. Even if you have inner joins on the tables, things should go okay. By keeping the memory clean, you won't have to worry about what to do when the phone suddenly sends you those out-of-memory warnings.

One last thing, I did have to break from tradition with an enterprise database and create and open the connection and leave it open. When I tried closing the connection and reopening when needed, performance was really unacceptable, especially as the board filled up with letters. But beyond that, it was just a matter of setting indexes.

Hope that helps, good luck.

[xtr33me]

Originally Posted by Lars

I vote that you query the data when you need it and release it when you are done. If you see bad performance, you can think of changing the method, but I have a feeling performance won't be bad.

Just to give you an example, I created the game downWORD which is a tetris style game where letters fall and you place them trying to spell words. I needed to query for various matches both vertically and horizontally. If a word is found, the row disappears and all tiles slide down. Then I must query against all possible words in all directions. I'm doing this against the SOWPODS database with 189,000 words and soon with a French dictionary with 212,000 words. I'm really able to run upwards of 30 queries in less than a second so that the game doesn't show any slowdown checking for the words.

My guess is as long as you setup your indexes correctly you shouldn't have any problems. Even if you have inner joins on the tables, things should go okay. By keeping the memory clean, you won't have to worry about what to do when the phone suddenly sends you those out-of-memory warnings.

One last thing, I did have to break from tradition with an enterprise database and create and open the connection and leave it open. When I tried closing the connection and reopening when needed, performance was really unacceptable, especially as the board filled up with letters. But beyond that, it was just a matter of setting indexes.

Hope that helps, good luck.

Thanks Lars! Great info! I'm surprised that you were able to perform what you did. After hearing that I don't think it is going to be an issue at all. My biggest hurdle will be the fact that I am still learning how to create db's, use indexes and trying to extract decimals, images and arrays from SQL. I took a course at work on querying SQL but nothing really on creating them. Google and these forums have become my closest friends in these past couple weeks. Your info is one more hurdle I've gotten over. Thanks again!! Have a great holiday weekend!

[raheel]

This takes off some burden i had too! I'm struggling to find some resources using indexes, basically good handling of memory in SQL.