Jan 27
jeffSqlite, Titanium Mobile
A while back, I wrote about how multiple INSERT statements in SQLite can be pretty slow. Today, I saw the following tweet from Tobias Reike:
cool,just decreased the time to save data to db in my android app from about 1.5min down to 10sec by using only one sqlite transaction
After contacting Tobias, he pointed me to the BEGIN / COMMIT documentation in SQLite:
@ look here: http://t.co/4XW2DRwn I’ve just done a db.execSQL(“BEGIN”) before and db.execSQL(“COMMIT”) after my inserts.
Sure enough, I modified my code to use BEGIN / COMMIT and the results were pretty awesome. Inserting 105 records went from 585ms to 44ms – more than 10x faster!
I’m sure this falls into the category of something I should have known, but it was news to me that SQLite had BEGIN / COMMENT. Here is my code of before and after:
// BEFORE
var dbLog = tracer.createTracer('db');
dbLog.info('opening database');
var db = database.open();
dbLog.info('deleting old items');
db.execute('DELETE FROM ITEM');
dbLog.info(db.rowsAffected + ' rows deleted');
dbLog.info('starting insert');
for( var i = 0; i < data.length; i++ ){
var item = data[i];
db.execute('INSERT INTO ITEM (SERVERID, TITLE, TYPE, Q1, Q2, MAINPHOTOURL, ALBUMPHOTOURL, THUMBPHOTOURL, AUTHOR, AUTHORID, AUTHORPHOTO, COMMENTCOUNT) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)', item.id, item.title, item.type, item.q1, item.q2, item.mainPhotoURL, item.albumPhotoURL, item.thumbPhotoURL, item.authorUsername, item.authorId, item.authorUsername, item.commentCount);
}
dbLog.info( 'finished inserts' );
dbLog.info( 'closing database' );
db.close();
dbLog.info('database save complete');
// AFTER
var dbLog2 = tracer.createTracer('db2');
dbLog2.info('opening database');
var db2 = database.open();
dbLog2.info('deleting old items');
db2.execute('DELETE FROM ITEM');
dbLog2.info(db2.rowsAffected + ' rows deleted');
dbLog2.info('starting BEGIN');
db2.execute('BEGIN');
dbLog2.info('starting insert');
for( var i = 0; i < data.length; i++ ){
var item = data[i];
db2.execute('INSERT INTO ITEM (SERVERID, TITLE, TYPE, Q1, Q2, MAINPHOTOURL, ALBUMPHOTOURL, THUMBPHOTOURL, AUTHOR, AUTHORID, AUTHORPHOTO, COMMENTCOUNT) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)', item.id, item.title, item.type, item.q1, item.q2, item.mainPhotoURL, item.albumPhotoURL, item.thumbPhotoURL, item.authorUsername, item.authorId, item.authorUsername, item.commentCount);
}
dbLog2.info( 'finished inserts' );
dbLog2.info( 'starting commit' );
db2.execute('COMMIT');
dbLog2.info( 'finished commit' );
dbLog2.info( 'closing database' );
db2.close();
dbLog2.info('database save complete');
And the results:
[INFO] db 0ms opening database
[INFO] db 0ms deleting old items
[INFO] db 4ms 105 rows deleted
[INFO] db 4ms starting insert
[INFO] db 589ms finished inserts
[INFO] db 589ms closing database
[INFO] db 589ms database save complete
[INFO] db2 0ms opening database
[INFO] db2 0ms deleting old items
[INFO] db2 10ms 105 rows deleted
[INFO] db2 10ms starting BEGIN
[INFO] db2 10ms starting insert
[INFO] db2 25ms finished inserts
[INFO] db2 25ms starting commit
[INFO] db2 53ms finished commit
[INFO] db2 54ms closing database
[INFO] db2 54ms database save complete
I hope this helps!
Jan 25
jeffTitanium Mobile
This is all the Titanium Mobile development news I’ve gathered from the Twittersphere and Interwebs for the past week(s). I’ve been away so am a little behind!
News
If you missed Titanium week, you can catch up on all the Webcasts On-Demand. The code from the Todo list best practices is posted at github.
Jose from the Electric playground posted a Titanium Overview video.
Appcelerator opened their London headquarters – they issued a standard press release. The bigger news is two big guns from Adobe join the Appcelerator team to work in Europe.
Tim Anderson interviewed Appcelerator CEO Jeff Haynie to try and get his perspective on the EMEA expansion, WebKit and his views regarding Titanium and PhoneGap.
Sharry from LearningTitanium.com interviewed developers Kazuaki Konno and Kota Iguchi.
Tips
Aaron Saunders being as resourceful as ever posted a link to another quickie: Simple Location Information on Kindle Fire or Nook.
LearningTitanium.com created a new Twitter Account with access to new Q&A questions from the forums – updated every 15 minutes. Follow @AppC_QA
Appcodingeasy.com posted an article that shows you how to use raw accelerometer data in titanium. There’s a snippet of code and neat video to show you how it works.
Here is a nice article on Extending a titanium project with iOS module development – Implementing the Bit.ly API. Really good stuff if you are interested in module development.
Bill Dawson posted a way to get the NDK crash from your Android emulator after a crash. Great for debugging when things aren’t going right.
Matt Apperson shows us how to move the tabbar on android to the bottom.
Tony Lukasavage posted another video with the results of his first steps in building a pimped out table view in Titanium. I can’t wait for the finished product.
Lance Spellman updated hisTitanium Zipfile module over on Github.
A question about drop shadow effects on iOS came up on the Q & A Forum. Olivier Morandi came up with a quick module for it.
A 2D gaming API in case you want to create a couple simple games
Pinehead.tv has a nice video tutorial on getting & adding results to the Tableview in SQLite.
The legendary Dawson Toth shared Version 2.0 of TiAir 2.0 with examples, documentation, and way short initialization.
Arthur Evans (@DevDocDude) updated the Titanium API documentation on the Appcelerator site so you can now show/hide of inherited members & property accessors.
Apps
TweetMic allows you to record your tweets instead of typing them, now your followers can hear you!
Dubplate.fm is an audio streaming app that was recently approved.
Porsche uses a Titanium app to deliver immersive experience for their 911 models.
Wiki Dip makes it easy to discover random articles on Wikipedia and share them with friends.
SNMP Scan looks for SNMP (Simple Network Management Protocol) enabled devices on a network. Amazing that this was built with Titanium.
If you’ve got any news, tips, or apps I’ve missed, please leave them in the comments below!
Jeff
Jan 20
jeffTitanium Mobile
This is all the Titanium Mobile development news I’ve gathered from the Twittersphere and Interwebs for the past week.
News
Socialize asked if people wanted a Titanium Module – and were overwhelmed with the response. Follow the link to get updates on the new Socialize titanium module!
It’s Titanium Week! Daily webcasts about every aspect of Titanium development.
Tips
Aaron Saunders was kind enough to put this example code together: using Cocoafish + Appcelerator iOS Module to login or create an account with Twitter and this Titanium Appcelerator Quickie: StackMob API Module Part Four gives you access to the StackMob API Module for Facebook Integration
Pratik posted a video on a tool he has created for Rapid development with Titanium Studio and TiREPL – this is awesome stuff!
Tony Lukasavage shared a YouTube video of a cross platform Path menu for Appcelerator Titanium. UI Goodness!
Ivan Škugor has posted a long article about the entire framework in his Zenborgium blog. A great intro for beginners.
Philippe Elsass created this amusing post on his blog, detailing his revisit to “the good, the bad and the ugly” with regards to Titanium. A good read about someone returning to Titanium after being away from it for a while.
David Bankier shared a cool video on iPad to OS X drawing using Titanium Mobile and Titanium Desktop over WiFi. Cool stuff
Tim Poulson (Skypanter) shared a cross platform tabbedbar on github.com. Nice to see as had to implement my own a couple weeks ago – this would have saved me some time.
Appcodingeasy.com has started blogging a whole bunch of Titanium stuff:
I’ve never heard of taffy.db, but here is a wrapper for TaffyDB to use in Titanium.
Josh Miller shared a code snippet on how to insert an array into a SQLite DB. Good to see new people blogging about Titanium!
Codecookie.net shared their experiences with Titanium Drag Problems and ultimate resolution. It’s useful to see how others have worked their way around some of their problems they encountered.
Building Titanium from source? Bill Dawson tells you how to speed things up.
Matt Apperson (who just left Appcelerator) shared a make file for building your titanium mobile applications from the command line.
Apps
Dan Giulvezan released MediaDrop – a way to send photos, videos and audio notes directly to dropbox.
Boydlee Pollentine did a little Jam & Preserves Cookbook that mirrored a WP7 project he did.
If you’ve got any news, tips, or apps I’ve missed, please leave them in the comments below!
Jeff
Older Entries
