Lessons learned: XML parsing & database performance

[Chris Garrett]

Hi all,

Two things I learned this week:

1)  Don't follow Apple's example with the SAX parser.  I'm new to Cocoa, so I didn't appreciate the cost of creating and releasing Autorelease pools.  In the SeismicXML example, they are creating an autorelease pool for every XML tag or character data the parser encounters.  This is absolutely the wrong thing to do.  Instead, create and release objects explicitly, and reuse objects as much as possible.

In my case I was parsing a SOAP response and storing it in a SQLite database.  Once I figured out the xml parsing issue, it was on to my SQLite performance lesson:

2)  Any time you will be doing multiple SQLite calls, wrap it in a transaction.  If you don't do this, then SQLite will create a transaction for every database statement you do.  This is a pretty expensive operation.  In my case I was inserting rows based on the SOAP response, so I had quite a bit to do - the database had about 6000 records in it.  It was taking 12 minutes without wrapping in a transaction, and it now takes about 25 seconds.

Transactions are simple to use:

sqlite3_exec(database, "begin transaction", NULL, NULL, NULL);
// Go do my database stuff
...
sqlite3_exec(database, "commit transaction", NULL, NULL, NULL);