1

It has been observed before, that concurrently writing to a SQLLite database is "slow" if we don't use transactions (see this ... not sure how this applies to Android where we write to an SD card though). And this was reported without several threads writing to the same database. I remember reading that in case of using multiple threads to write to the database locking slows things down even more (I will add the article if I find it again).

Now, I have a scenario where I write from fourteen threads to a single database. Each write starts at about the same time every second and consists of 1 to 60 entries. Each thread writes to a different table. Those tables are completely independent, i.e., I will never have to do a join or anything the like. Which makes mz scenario quite different from those, e.g., in What are the best practices for SQLite on Android?

My question is, while I have already optimized by caching entries and writing them to the database using transactions, I am still wondering if I could further speed up things by creating a separate database for each table. That is, threads should not be locking this way. But I am not sure if this would have the desired effect, i.e., getting rid of locking altogether.

Does any of you have experience with this or can explain to me why or why not my approach would be a good idea? Thanks in advance.

Community
  • 1
  • 1
Martin Becker
  • 3,331
  • 3
  • 22
  • 25
  • 1
    consider that your DB is on a phone, which uses flash memory. using different files means re-writing entirely different blocks of that flash, meaning performance may actually suffer MORE from using multiple files, rather than a single file where the writes to each of those tables can be combined into a write of a single flash block. – Marc B Mar 17 '16 at 22:02
  • Thanks for the input! I was thinking along the same lines, but the locking mechanism of SQLLite would prevent writes from different threads to be combined, I think, because for each write it locks until the file system confirms the update. At least that is how I understood it from the SQLLite FAQ linked above. What do you think? – Martin Becker Mar 18 '16 at 06:36
  • @MarcB The flash already does wear leveling, so all new data will end up in the same block. – CL. Mar 18 '16 at 08:28

1 Answers1

0

OK, I did some experiments in my scenario. The results are not really clear, but overall I would say, that either approach should be OK (using one or using separate databases). What I noticed is that, there is a slight trend towards separate databases for each table being slower initially but speeding up over time, while using a single database seems to be faster at the beginning but slows slightly over time. In this post, I will report my findings and accept it as an answer for now. If anybody has a more thorough explanation, possibly with some theory instead of just empirical evidence, I would change the accept.

Scenario in detail: I am working with the Microsoft Band 2 Android API, and for each sensor I am defining a listener using a HandlerThread and subscribe it to the sensor manager (always using max. frequency). Each listener gets a SQLiteOpenHelper which it uses to get a writable database "getWriteableDatabase". The fast sensors I cache 60 samples before writing to the database. Now, we can either define a different SQLiteOpenHelper for each sensor or use the same one.

Since we agree that using transitions makes things a lot faster (I was able to confirm speedups of a factor 20), I use transactions throughout the following experiments, that is for the sensors where I cache 60 samples before writing to the database.

Experiments

In the experiments I calculate the average over several insert for each sensor/thread. I report the time and the number of samples used for averaging. The format is

([average insert time in ms] / [number of runs] / [last insert time in ms]  ): [SENSOR]: [number of inserted values]

A single SQLiteOpenHelper for all threads (one database)

Run 1

(93.18840579710145 / 69.0 / 76 ): ACCELEROMETER: 60
(99.52307692307693 / 65.0 / 66 ): GYROSCOPE: 60
(35.323076923076925 / 130.0 / 134 ): AMBIENT_LIGHT: 1
(41.0735294117647 / 68.0 / 148 ): HEART_RATE: 1
(37.257234726688104 / 311.0 / 114 ): GSR: 1
(43.79032258064516 / 62.0 / 57 ): CALORIES: 1
(41.62903225806452 / 62.0 / 41 ): DISTANCE: 1
(49.233333333333334 / 60.0 / 50 ): PEDOMETER: 1
(43.896907216494846 / 97.0 / 47 ): RR_INTERVAL: 1
(35.76923076923077 / 65.0 / 12 ): BAROMETER: 1
(44.223880597014926 / 67.0 / 22 ): ALTIMETER: 1
...
(105.2179675994109 / 679.0 / 62 ): ACCELEROMETER: 60
(106.58554572271386 / 678.0 / 68 ): GYROSCOPE: 60
(41.799546142208776 / 1322.0 / 33 ): AMBIENT_LIGHT: 1
(48.519578313253014 / 664.0 / 35 ): HEART_RATE: 1
(36.98158768487775 / 3313.0 / 67 ): GSR: 1
(46.8031007751938 / 645.0 / 18 ): CALORIES: 1
(46.826219512195124 / 656.0 / 14 ): DISTANCE: 1
(56.807633587786256 / 655.0 / 18 ): PEDOMETER: 1
(45.56761729530819 / 1087.0 / 18 ): RR_INTERVAL: 1
(49.16944024205749 / 661.0 / 31 ): BAROMETER: 1
(60.404833836858 / 662.0 / 40 ): ALTIMETER: 1

Run 2

(88.95454545454545 / 66.0 / 128 ): ACCELEROMETER: 60
(75.109375 / 64.0 / 66 ): GYROSCOPE: 60
(31.906976744186046 / 129.0 / 16 ): AMBIENT_LIGHT: 1
(25.863636363636363 / 66.0 / 15 ): HEART_RATE: 1
(27.237942122186496 / 311.0 / 15 ): GSR: 1
(33.08064516129032 / 62.0 / 8 ): CALORIES: 1
(29.258064516129032 / 62.0 / 10 ): DISTANCE: 1
(39.0655737704918 / 61.0 / 15 ): PEDOMETER: 1
(32.406976744186046 / 86.0 / 11 ): RR_INTERVAL: 1
(32.640625 / 64.0 / 82 ): BAROMETER: 1
(41.292307692307695 / 65.0 / 89 ): ALTIMETER: 1
...
(94.10264385692068 / 643.0 / 61 ): ACCELEROMETER: 60
(77.9188767550702 / 641.0 / 77 ): GYROSCOPE: 60
(33.86671987230646 / 1253.0 / 19 ): AMBIENT_LIGHT: 1
(30.63809523809524 / 630.0 / 33 ): HEART_RATE: 1
(28.691152986266367 / 3131.0 / 12 ): GSR: 1
(32.55810147299509 / 611.0 / 22 ): CALORIES: 1
(31.56774193548387 / 620.0 / 41 ): DISTANCE: 1
(40.27786752827141 / 619.0 / 49 ): PEDOMETER: 1
(31.76108870967742 / 992.0 / 38 ): RR_INTERVAL: 1
(31.466453674121407 / 626.0 / 11 ): BAROMETER: 1
(40.610845295055825 / 627.0 / 19 ): ALTIMETER: 1

A different SQLiteOpenHelpers for each thread (one database for each sensor)

Run 1

(201.98076923076923 / 52.0 / 188 ): ACCELEROMETER: 60
(203.69230769230768 / 52.0 / 72 ): GYROSCOPE: 60
(100.16666666666667 / 102.0 / 37 ): AMBIENT_LIGHT: 1
(125.3    2692307692308 / 52.0 / 47 ): HEART_RATE: 1
(74.4563492063492 / 252.0 / 46 ): GSR: 1
(122.25490196078431 / 51.0 / 13 ): CALORIES: 1
(118.3921568627451 / 51.0 / 34 ): DISTANCE: 1
(127.82352941176471 / 51.0 / 29 ): PEDOMETER: 1
(112.72527472527473 / 91.0 / 34 ): RR_INTERVAL: 1
(106.27450980392157 / 51.0 / 18 ): BAROMETER: 1
(128.2941176470588 / 51.0 / 24 ): ALTIMETER: 1
...
(103.7621776504298 / 698.0 / 71 ): ACCELEROMETER: 60
(99.96418338108883 / 698.0 / 102 ): GYROSCOPE: 60
(27.61911764705882 / 1360.0 / 20 ): AMBIENT_LIGHT: 1
(31.57247437774524 / 683.0 / 37 ): HEART_RATE: 1
(24.713699031974187 / 3409.0 / 13 ): GSR: 1
(31.17921686746988 / 664.0 / 28 ): CALORIES: 1
(34.1037037037037 / 675.0 / 26 ): DISTANCE: 1
(36.275555555555556 / 675.0 / 31 ): PEDOMETER: 1
(30.485689505637467 / 1153.0 / 26 ): RR_INTERVAL: 1
(32.23494860499266 / 681.0 / 10 ): BAROMETER: 1
(36.75917767988253 / 681.0 / 16 ): ALTIMETER: 1

Run 2

(126.11940298507463 / 67.0 / 77 ): ACCELEROMETER: 60
(128.82539682539684 / 63.0 / 44 ): GYROSCOPE: 60
(58.16153846153846 / 130.0 / 29 ): AMBIENT_LIGHT: 1
(62.53731343283582 / 67.0 / 28 ): HEART_RATE: 1
(50.798701298701296 / 308.0 / 9 ): GSR: 1
(81.62903225806451 / 62.0 / 33 ): CALORIES: 1
(57.45161290322581 / 62.0 / 19 ): DISTANCE: 1
(60.56666666666667 / 60.0 / 14 ): PEDOMETER: 1
(61.56603773584906 / 106.0 / 14 ): RR_INTERVAL: 1
(68.078125 / 64.0 / 23 ): BAROMETER: 1
(80.27272727272727 / 66.0 / 27 ): ALTIMETER: 1
...
(78.23076923076923 / 702.0 / 105 ): ACCELEROMETER: 60
(82.7560975609756 / 697.0 / 56 ): GYROSCOPE: 60
(27.407326007326006 / 1365.0 / 16 ): AMBIENT_LIGHT: 1
(25.03202328966521 / 687.0 / 17 ): HEART_RATE: 1
(22.503373423291286 / 3409.0 / 20 ): GSR: 1
(27.905263157894737 / 665.0 / 10 ): CALORIES: 1
(28.832592592592594 / 675.0 / 13 ): DISTANCE: 1
(30.034175334323923 / 673.0 / 17 ): PEDOMETER: 1
(25.60211267605634 / 1136.0 / 22 ): RR_INTERVAL: 1
(29.501466275659823 / 682.0 / 18 ): BAROMETER: 1
(33.88157894736842 / 684.0 / 23 ): ALTIMETER: 1
Martin Becker
  • 3,331
  • 3
  • 22
  • 25