Just a quick question:
Is it more efficient for me, in terms of memory and processor use, to open and close my database connection every time I use it, or simply open it at start up and pass it around when it needs to be used?
My app downloads and uploads a lot of stuff, so the database gets written to and read from quite frequently.
Thank you!
I’m not sure, but for me, I leave my database open the entire app session and haven’t had any issues.
However, one performance optimization that I did find made a very significant difference was to use the BEGIN TRANSACTION and END TRANSACTION. After you execute a BEGIN TRANSACTION query, any queries that write to the database get queued up in memory until you execute END TRANSACTION. This results in just one lump write to disk instead of lots of small writes to disk, which makes it much faster. If you know you’re about to execute lots of write queries (e.g., inserting new records one at a time), I’d highly recommend using this approach.
So it turns out that doing a collection of queries at once rather than single queries is faster? That’s interesting, but i suppose it makes sense.
Thanks very much! This really helps me plan how I’m going to attach the SQL side of my app 
Yeah, here’s a StackOverflow thread that talks about optimizing insertion queries: http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite.
Awesome, thanks!
I won’t quite have 800,000+ records (Or anywhere close) but anything I can do to make it more memory efficient is key!
I’m not sure, but for me, I leave my database open the entire app session and haven’t had any issues.
However, one performance optimization that I did find made a very significant difference was to use the BEGIN TRANSACTION and END TRANSACTION. After you execute a BEGIN TRANSACTION query, any queries that write to the database get queued up in memory until you execute END TRANSACTION. This results in just one lump write to disk instead of lots of small writes to disk, which makes it much faster. If you know you’re about to execute lots of write queries (e.g., inserting new records one at a time), I’d highly recommend using this approach.
So it turns out that doing a collection of queries at once rather than single queries is faster? That’s interesting, but i suppose it makes sense.
Thanks very much! This really helps me plan how I’m going to attach the SQL side of my app
Yeah, here’s a StackOverflow thread that talks about optimizing insertion queries: http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite.
Awesome, thanks!
I won’t quite have 800,000+ records (Or anywhere close) but anything I can do to make it more memory efficient is key!