[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
On Sep 3, 2:03 am, mjc147 westmead...@yahoo.co.uk wrote: @skink - the gzip compression would only be useful for when downloading the data, wouldn't it? After installation/first-time- initialisation, wouldn't all the data be best stored uncompressed? well, if both key-value fields need to be searchable as you said, you are right: compression here is not a good option --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en -~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
I performed the complete import which took just over one hour on the Hero. Final db size (no indexes) was 24MB and zips to 11MB. I had to build the db in the default location (i.e. not on the sdcard) because it was much much faster. Once the import was finished, I simply copied the db over to the sdcard. So this db zip file would be downloaded when a user first runs the app. The db file would be automatically uncompressed and the indexes built. How does this sound? I would still like to try out an alternative to using SQLite, as Dianne suggested. Can anyone give me any pointers, please? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en -~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
Are you using bulkInsert? On Wed, Sep 2, 2009 at 8:47 AM, mjc147westmead...@yahoo.co.uk wrote: I need to insert approximately one million rows of data (spread over 4 tables) - each row has one or two numeric fields, and two or three text fields (normally quite short). Single-column indexes on all the fields. After doing some tests on the emulator with a small test set, I extrapolated (assuming the last row will be inserted with a similar speed to the first row) my results to figure out that this would take about 15 hours (I have a Core 2 Duo running Vista). However, then I tried running the small test set on my Hero and I was surprised to see it run more than four times faster. I would guess it would take about 3 hours on the Hero. Alternatively, I could make the pre-populated database available as a download on the first run but this is likely to be a 120MB download. I have a few questions I'm hoping someone can help me with: 1. SQLite performance on the actual device being 4-5 times faster compared to the emulator - is this normal/expected? 2. Do the figures above generally sound fast/slow/normal (sorry this is so vague)? 3. From a user perspective - would it be best to provide a 120MB download or a 3 hour first-time initialisation step (plus 6MB download)? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en -~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
Does the data NEED to be on the device? Can it not reside on a server? Certainly you are better to manage the synchonisation yourself rather than bundle the 120MB lib for downloading through the marketplace. This sort of download needs to be resumable and I would recommend users only download with a Wifi connection. What sort of data is this?! It would have to be incredible to justify that size to most users, I think with a 3 hour install you have lost 99% of the user base without even knowing if the app is any good. Gav On Wed, Sep 2, 2009 at 4:47 PM, mjc147 westmead...@yahoo.co.uk wrote: I need to insert approximately one million rows of data (spread over 4 tables) - each row has one or two numeric fields, and two or three text fields (normally quite short). Single-column indexes on all the fields. After doing some tests on the emulator with a small test set, I extrapolated (assuming the last row will be inserted with a similar speed to the first row) my results to figure out that this would take about 15 hours (I have a Core 2 Duo running Vista). However, then I tried running the small test set on my Hero and I was surprised to see it run more than four times faster. I would guess it would take about 3 hours on the Hero. Alternatively, I could make the pre-populated database available as a download on the first run but this is likely to be a 120MB download. I have a few questions I'm hoping someone can help me with: 1. SQLite performance on the actual device being 4-5 times faster compared to the emulator - is this normal/expected? 2. Do the figures above generally sound fast/slow/normal (sorry this is so vague)? 3. From a user perspective - would it be best to provide a 120MB download or a 3 hour first-time initialisation step (plus 6MB download)? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en -~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
Its a bilingual dictionary. The source data is a 2.5MB flat file once compressed. The database stores this data plus some tables to assist lookups. Most of the size comes from the db indexes. I've just done some more tests and I see that after removing the indexes the db size is more like 40MB (the indexes can be created pretty quickly on the device). And I originally forgot about compression which reduces the size to almost a third the original. So the options are now: 1. 15MB database download then build indexes on the device 2. 2.5MB flat file download then perform the lengthy insert statements - maybe I can get this down well below the 3 hours estimate but I don't know... Would a 15MB download be too unreasonable? On Sep 3, 12:30 am, Gavin Aiken gavin.ai...@imperial.ac.uk wrote: Does the data NEED to be on the device? Can it not reside on a server? Certainly you are better to manage the synchonisation yourself rather than bundle the 120MB lib for downloading through the marketplace. This sort of download needs to be resumable and I would recommend users only download with a Wifi connection. What sort of data is this?! It would have to be incredible to justify that size to most users, I think with a 3 hour install you have lost 99% of the user base without even knowing if the app is any good. Gav On Wed, Sep 2, 2009 at 4:47 PM, mjc147 westmead...@yahoo.co.uk wrote: I need to insert approximately one million rows of data (spread over 4 tables) - each row has one or two numeric fields, and two or three text fields (normally quite short). Single-column indexes on all the fields. After doing some tests on the emulator with a small test set, I extrapolated (assuming the last row will be inserted with a similar speed to the first row) my results to figure out that this would take about 15 hours (I have a Core 2 Duo running Vista). However, then I tried running the small test set on my Hero and I was surprised to see it run more than four times faster. I would guess it would take about 3 hours on the Hero. Alternatively, I could make the pre-populated database available as a download on the first run but this is likely to be a 120MB download. I have a few questions I'm hoping someone can help me with: 1. SQLite performance on the actual device being 4-5 times faster compared to the emulator - is this normal/expected? 2. Do the figures above generally sound fast/slow/normal (sorry this is so vague)? 3. From a user perspective - would it be best to provide a 120MB download or a 3 hour first-time initialisation step (plus 6MB download)? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en -~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
On Sep 3, 12:43 am, Marco Nelissen marc...@android.com wrote:
Are you using bulkInsert?
Actually, I don't. I looked at it but it seemed to be related to
ContentProvider and ContentResolver neither of which I'm using. Am I
missing something here?
I use execSQL(INSERT INTO my_table VALUES(?,?,?), new Object[]
{1,2,3}) and perform on average 10 calls per transaction.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
-~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
Is a database really the right thing at all? Generally a dictionary has very special/optimized look up (see for example the latin dictionary) and you are going to be able to get much much better space usage and lookup performance by having a dedicated data structure rather than relying on a very general-purpose SQL database. Especially if your database is fixed contents (not user editable), a database seems like significant overkill to me. On Wed, Sep 2, 2009 at 9:47 AM, mjc147 westmead...@yahoo.co.uk wrote: Its a bilingual dictionary. The source data is a 2.5MB flat file once compressed. The database stores this data plus some tables to assist lookups. Most of the size comes from the db indexes. I've just done some more tests and I see that after removing the indexes the db size is more like 40MB (the indexes can be created pretty quickly on the device). And I originally forgot about compression which reduces the size to almost a third the original. So the options are now: 1. 15MB database download then build indexes on the device 2. 2.5MB flat file download then perform the lengthy insert statements - maybe I can get this down well below the 3 hours estimate but I don't know... Would a 15MB download be too unreasonable? On Sep 3, 12:30 am, Gavin Aiken gavin.ai...@imperial.ac.uk wrote: Does the data NEED to be on the device? Can it not reside on a server? Certainly you are better to manage the synchonisation yourself rather than bundle the 120MB lib for downloading through the marketplace. This sort of download needs to be resumable and I would recommend users only download with a Wifi connection. What sort of data is this?! It would have to be incredible to justify that size to most users, I think with a 3 hour install you have lost 99% of the user base without even knowing if the app is any good. Gav On Wed, Sep 2, 2009 at 4:47 PM, mjc147 westmead...@yahoo.co.uk wrote: I need to insert approximately one million rows of data (spread over 4 tables) - each row has one or two numeric fields, and two or three text fields (normally quite short). Single-column indexes on all the fields. After doing some tests on the emulator with a small test set, I extrapolated (assuming the last row will be inserted with a similar speed to the first row) my results to figure out that this would take about 15 hours (I have a Core 2 Duo running Vista). However, then I tried running the small test set on my Hero and I was surprised to see it run more than four times faster. I would guess it would take about 3 hours on the Hero. Alternatively, I could make the pre-populated database available as a download on the first run but this is likely to be a 120MB download. I have a few questions I'm hoping someone can help me with: 1. SQLite performance on the actual device being 4-5 times faster compared to the emulator - is this normal/expected? 2. Do the figures above generally sound fast/slow/normal (sorry this is so vague)? 3. From a user perspective - would it be best to provide a 120MB download or a 3 hour first-time initialisation step (plus 6MB download)? -- Dianne Hackborn Android framework engineer hack...@android.com Note: please don't send private questions to me, as I don't have time to provide private support, and so won't reply to such e-mails. All such questions should be posted on public forums, where I and others can see and answer them. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en -~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
In addition to skink post - try to use prepared/compiled statements:
Object[] params = new Object[3];
SQLiteStatement statement = compileStatement(INSERT INTO my_table
VALUES(?,?,?));
// loop here
for(...) {
params[0] = ...
params[1] = ...
params[2] = ...
DatabaseUtils.bindObjectToProgram(statement, 1, params[0]);
DatabaseUtils.bindObjectToProgram(statement, 2, params[1]);
DatabaseUtils.bindObjectToProgram(statement, 3, params[2]);
statement.execute();
}
On Sep 2, 10:59 pm, skink psk...@gmail.com wrote:
On Sep 2, 7:18 pm, mjc147 westmead...@yahoo.co.uk wrote:Am I
missing something here?
I use execSQL(INSERT INTO my_table VALUES(?,?,?), new Object[]
{1,2,3}) and perform on average 10 calls per transaction.
did you try allocating params array once?
Object[] params = new Object[3];
// loop here
for(...) {
params[0] = ...
params[1] = ...
params[2] = ...
execSQL(INSERT INTO my_table VALUES(?,?,?), params)
}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
-~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
On Sep 2, 8:00 pm, Dianne Hackborn hack...@android.com wrote: Is a database really the right thing at all? Generally a dictionary has very special/optimized look up (see for example the latin dictionary) and you are going to be able to get much much better space usage and lookup performance by having a dedicated data structure rather than relying on a very general-purpose SQL database. Especially if your database is fixed contents (not user editable), a database seems like significant overkill to me. -- Dianne Hackborn Android framework engineer hack...@android.com i agree with Dianne, if you have string key/value data and #rows is quite large (hundrets of thousands) typical sqlite db would be huge - with simple gzip compression you could have your data 1/3 smaller pskink --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en -~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
On Wed, Sep 2, 2009 at 10:18 AM, mjc147westmead...@yahoo.co.uk wrote:
On Sep 3, 12:43 am, Marco Nelissen marc...@android.com wrote:
Are you using bulkInsert?
Actually, I don't. I looked at it but it seemed to be related to
ContentProvider and ContentResolver neither of which I'm using. Am I
missing something here?
I use execSQL(INSERT INTO my_table VALUES(?,?,?), new Object[]
{1,2,3}) and perform on average 10 calls per transaction.
BulkInsert is the provider's way of inserting data in bulk, usually
done by using a transaction.
Since you're already using a transaction, you should be OK, though you
could try increasing the number of inserts per transaction.
You might also want to reconsider doing that new Object every time.
Reusing existing objects would be gentler on the garbage collector.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
-~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
On Sep 2, 7:18 pm, mjc147 westmead...@yahoo.co.uk wrote:Am I
missing something here?
I use execSQL(INSERT INTO my_table VALUES(?,?,?), new Object[]
{1,2,3}) and perform on average 10 calls per transaction.
did you try allocating params array once?
Object[] params = new Object[3];
// loop here
for(...) {
params[0] = ...
params[1] = ...
params[2] = ...
execSQL(INSERT INTO my_table VALUES(?,?,?), params)
}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
-~--~~~~--~~--~--~---
[android-developers] Re: Inserting large volumes of data into SQLite - speed issues
I've done some speed tests and here are my findings (disclaimer, my test runs for just over a minute so timings are not extremely reliable): 1. Reusing SQL params object array - negligible speed improvement - test too short to say anything with confidence 2. Using prepared statements rather then execSQL() - 20% faster (BTW - there is no need to use an array at all here) 3. Removing log statements - 100% faster (whoops!) 4. Inserting 10 times as many rows (speed (per row average) stays about the same) 5. Running on the Hero instead of the emulator - 400%-500% faster There are quite a few other objects being created within the main loop so maybe there are some speed improvements to be made. However, after experimenting with point 1, I don't expect these to be as dramatic as points 2,3 and 5. So, assuming I stick with the database approach, the options are: 1. 15MB database download 2. 2.5MB download then perform a one-hour initialisation step I can see that second option is a bit weird... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en -~--~~~~--~~--~--~---
