SQL and the Update Loop

SQL and the Update Loop

A common technology use for a word based iPhone game is the inclusion of an SQLite database containing the word list of the game, to be queried when checking for potential matches to a sequence of characters. While the ease of offloading data management to SQLite is certainly a boon (as opposed to creating your own data structure to load the word list at run time), it is important to keep several things in mind when doing so:

  • Normalize your data. If you are querying the database for words, for example, ensure that your input data form matches your database data form before you issue your query. Using a dictionary as an example, make sure the words in your dictionary are either all upper-case or all lower-case, and that your input data matches. The difference between SELECT word FROM dictionary WHERE UPPER(word) = UPPER(?) and SELECT word FROM dictionary WHERE word = ? is at least n + 1 calls of UPPER. Buy yourself that time with in advance data treatment.

  • Query on change. Chances are, your game state will only need to query when some user input or game event occurs. Should this be the case, your data structures should be such that they only query the database when absolutely necessary. For instance, using a word based game as an example again: if the user has spelled CAPITULATE, and the I in CAPITULATE is removed, your data structure should already recognize that CAP and LATE are still words (based on the previous query that also showed CAPITULATE to be a word), and not query the database again to rediscover this. While it places more of a burden on your game logic, you'll avoid the cost of another scan across your dictionary against each potential word candidate.

  • Indexes. This should be obvious, but your database should be aggressively indexed if it will be queried during the update loop. Create UNIQUE and PRIMARY indexes as relevant, and do so in advance.

  • VACUUM. The VACUUM statement cleans up fragments left behind from successive INSERTs and DELETEs. You should VACUUM before shipping your database to clean up any work you've done in development, and if your database will be modified by the game, it's a small thing to issue the statement during your game's quit-cleanup phase.

  • Cache failures or successes. Consider creating an in-memory cache of failures or successes ( whichever is more likely to happen based on your requests ), and use it often. You can whip together a simple ring buffer in no time, or you can create something more complex that expires stale data based on access count or time of last access.

Most of this advice has been inferred through repeated performance optimization of Text Tumble. If you've decided to do your database access in the update loop, this should help you shave off a few hours from your optimization work (and get closer to your target constant FPS :-) ).

comments powered by Disqus