{"id":512,"date":"2013-07-13T16:25:53","date_gmt":"2013-07-13T14:25:53","guid":{"rendered":"http:\/\/blog.ansuz.nl\/?p=512"},"modified":"2013-07-13T16:25:53","modified_gmt":"2013-07-13T14:25:53","slug":"sqlite-on-android","status":"publish","type":"post","link":"http:\/\/blog.ansuz.nl\/index.php\/2013\/07\/13\/sqlite-on-android\/","title":{"rendered":"SQLite on Android"},"content":{"rendered":"

In the past week I started working with SQLite databases on Android.<\/p>\n

It can be quite cumbersome to explore the data stored in the DB on an actual device. What has helped me a lot is grabbing the DB from the device and loading the SQLite DB in a third party SQLite browser \/ manager. This will give you a lot more flexibility on exploring the data that is stored and will also be a great help for testing your queries.
\nYou can retrieve your database from the device using ADB: <\/p>\n

\n\n\t\t
\n\n\t\t
\n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
code<\/div>\n\n\t\t\n\t\t
\n\n\t<\/div>\n\t\n\t
\n\t\t
  1. adb pull \/data\/data\/[app_package]\/databases\/[db_file] .<\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n

    I’ve been using SQLiteBrowser<\/a> to explore the DB and test my queries.<\/p>\n

    One thing I noticed is that inserting around a hundred rows took quite some time, about 350ms. I was inserting one row at a time and after some Googling on how to insert multiple rows in one query, I found an answer on StackOverflow<\/a>. The suggestion was to do something like the following:<\/p>\n

    \n\n\t\t
    \n\n\t\t
    \n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
    sql<\/div>\n\n\t\t\n\t\t
    \n\n\t<\/div>\n\t\n\t
    \n\t\t
    1. INSERT<\/span> INTO<\/span> 'tablename'<\/span><\/div><\/li>\n
    2.     SELECT<\/span> 'data1'<\/span> AS<\/span> 'column1'<\/span>,<\/span> 'data2'<\/span> AS<\/span> 'column2'<\/span><\/div><\/li>\n
    3.     UNION<\/span> SELECT<\/span> 'data3'<\/span>,<\/span> 'data4'<\/span><\/div><\/li>\n
    4.     UNION<\/span> SELECT<\/span> 'data5'<\/span>,<\/span> 'data6'<\/span><\/div><\/li>\n
    5.     UNION<\/span> SELECT<\/span> 'data7'<\/span>,<\/span> 'data8'<\/span><\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n

      After trying this approach it cut the insertion time in half near the 170ms mark, still quite slow.
      \nSome more searching brought me to an article about “      Speeding up SQLite insert operations<\/a>“. What this article advises is to use transactions, an example:<\/p>\n

      \n\n\t\t
      \n\n\t\t
      \n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
      java<\/div>\n\n\t\t\n\t\t
      \n\n\t<\/div>\n\t\n\t
      \n\t\t
      1. database.beginTransaction<\/span>(<\/span>)<\/span>;<\/span><\/div><\/li>\n
      2. for<\/span> (<\/span>...)<\/span> {<\/span><\/div><\/li>\n
      3.     database.insert<\/span>(<\/span>...)<\/span>;<\/span><\/div><\/li>\n
      4. }<\/span><\/div><\/li>\n
      5. database.setTransactionSuccessful<\/span>(<\/span>)<\/span>;<\/span><\/div><\/li>\n
      6. database.endTransaction<\/span>(<\/span>)<\/span>;<\/span><\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n

        Using this technique the insertion was sped up to roughly 25ms, much better!<\/p>\n

        As an alternative to using a database, you could just store the data on disk. This works fine especially when you just use a database to store data and don’t need to sort it or do more complex queries. Even if you needed sorting, you could use the SortedMap<\/a> class.<\/p>\n","protected":false},"excerpt":{"rendered":"

        In the past week I started working with SQLite databases on Android. It can be quite cumbersome to explore the data stored in the DB on an actual device. What has helped me a lot is grabbing the DB from … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-512","post","type-post","status-publish","format-standard","hentry","category-random"],"_links":{"self":[{"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/posts\/512","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/comments?post=512"}],"version-history":[{"count":2,"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/posts\/512\/revisions"}],"predecessor-version":[{"id":514,"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/posts\/512\/revisions\/514"}],"wp:attachment":[{"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/media?parent=512"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/categories?post=512"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.ansuz.nl\/index.php\/wp-json\/wp\/v2\/tags?post=512"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}