2

I'm working on a dictionary app based on an SQLite database with well over 300,000 rows.

The problem is that the database file in its final form consists of full-text indexed tables and weighs well over 150Mb.

I've managed to bring the .db file size to a minimum by creating a contentless fts4 tables. The database cannot be any smaller. I also managed to put the pre-populated database in the app and it works fine.

The problem is that I can't just keep the final .db file in /assets and copy it to sdcard on first run because it's too big. I also don't want to download it on first run.

Bulk INSERTing the data, even with transactions and sqlite optimized and no indexes at start takes forever so it's also not an option.

The good thing is the raw data used to build the database, in CSV format and compressed, is 30Mb and sqlite's command line .import option is very fast (100,000 rows in ~1s) but... it can't be accessed from the app without root permissions.

I would love to bundle the app with compressed CSV files in /assets, decompress them, create the database on sdcard and then import the CSV but that seems to me to be impossible. Although there are many dictionary apps that appear to be doing exactly this. (The downloaded app is a dozen megabytes, builds database on first run, and takes hundreds of megabytes of space on the sdcard).

How do I accomplish this?

I've been working on this for past two weeks and simply ran out of ideas. I'm new to Android development so any help would be much appreciated.

MarshallBananas
  • 129
  • 1
  • 10
  • transaction **s** or transaction? Only using a single transaction for all inserts is fast. – CL. Aug 07 '13 at 13:16
  • I meant single transaction. It really does take minutes. – MarshallBananas Aug 07 '13 at 14:34
  • Have you profiled your app using PRAGMA statements (invoke before all other statements, first command on sqlite database) to turn off the sqlite synchronous mode (synchronous=off) and rely solely on the operating system? Also change journal mode to memory: http://www.sqlite.org/pragma.html#pragma_synchronous – Jan Weitz Aug 07 '13 at 21:56
  • Yes. This speeds things up significantly but still is nothing compared to an sqlite command line `.import`. – MarshallBananas Aug 08 '13 at 14:37

2 Answers2

2
  • Plan A: ship a compressed SQLite database file and decompress it after installation; you should be able to omit indices and rebuild them later.
  • Plan B: copy the relevant parts of the CSV importer into your application, ship a compressed CSV file and load it into an empty database like the command line tool would. Official documentation page.
Lorenzo Gatti
  • 1,260
  • 1
  • 10
  • 15
  • I was actually implementing _Plan A_ right after I posted the question. But _Plan B_ sounds much better. Thank you! I'll post the results when I'm done. – MarshallBananas Aug 07 '13 at 14:12
  • Turns out _Plan A_ reduces the size of necessary data just as much as _Plan B_. And there's no need to compile sqlite with the csv add-on. _Plan A_ is the best option. Once again, thank you. – MarshallBananas Aug 07 '13 at 19:20
0

Create entire content into csv file possibly multiple csv files and compress into zip. I have checked with 32 mb csv file after compression the file become 482KB.

For Example

data1.csv data2.csv data3.csv data4.csv data5.csv

compress the files into data.zip and put file into asset folder, when importing - extract file from the zip one by one and insert into sqlite db.

Shlublu
  • 10,917
  • 4
  • 51
  • 70
MadhuSudananBS
  • 1
  • 1
    But how do I import the csv files? Turning them into SQL on the fly and bulk inserting takes hours and crashes the app. The only viable option is using the `.import data.csv` sqlite command but that can't be accessed from within the app. – MarshallBananas Aug 07 '13 at 14:10