SQLite Forum

SQLIGHT performance

SQLIGHT performance

(1) By akreidly on 2020-06-02 12:45:13 [link] [source]


I have installed and using SQLITE on Android Machine, im facing performance issues especially during an insert statements as the below example: 
 im inserting 10000 records: using BEGIN TRANSACTION the first 2000 inserts are done in sec and the second 2000 take more time and the 2000 take more and the last 2000 might take up to 30 minutes. 
any help is appreciated. 
moreover, is it parctically better to install multiple instances of sqlite if i have this amount of data (i have millions of records) and what im experiening might be caused by this amount of huge data. 

im using index on some columns as they are needed, i tried to remove the index it helps a little but i have to use indexes , knowing that even if i remove the index the performance is not acceptable as its taking time to insert

(2) By Larry Brasfield (LarryBrasfield) on 2020-06-02 15:20:15 in reply to 1 [source]

You should consider dropping the index before the insert and reinstating it (aka "rebuilding the index") after the mass-insert is done.

It seems a bit odd that you would use an "Android Machine" for working with large amounts of data. You might be exposing file system limitations.

Is it the case that your insert data is already ordered in a way that forces b-tree rebalancing disproportionate to the log of the record count? If that is so, a different insert order could help.

And if it is really true that dropping the index only "helps a little", then something strange is happening, such as excessive rebalancing or something else not evident in your problem statement.

(3) By akreidly on 2020-06-02 16:08:44 in reply to 2 [link] [source]

thank you for your post. i would like to ask you is there any adminisration GUI for SQLITE that might help in this matter .

Knowing that SQLITE is implemented on POS machine, we are expecting high load and no oher db engine could be deployed on a POS machine.

(4) By Ryan Smith (cuz) on 2020-06-02 16:44:22 in reply to 3 [link] [source]

I don't think there are any GUI's that will run on Android currently, and what do you mean with POS machine? (I'm crossing fingers that it means Point-of-Sale) :)

If you have access to a proper computer/OS, you can try DB Browser for SQLite on Mac, Linux or Windows, also SQLiteSpeed if it's specifically a Windows machine. A quick Google would probably list a few more.

I'm not sure if the sqlite cli can run on Android terminal/command line, One would think it should easily be able to - but I haven't tried it.

I have one question about your original post - You stated that it does not help when removing the Indexes, and Larry pointed out why that should make it quicker. My question is, if you do remove the indexes, do the inserts still take exponentially longer the more you insert? (i.e. doe the first 1000 and the last 1000 inserts take significantly different time to complete?)

(5) By Richard Hipp (drh) on 2020-06-02 20:38:40 in reply to 4 [link] [source]

I'm not sure if the sqlite cli can run on Android terminal/command line

It does. Just compile the CLI, "adb push" it, then us "adb" to log on and use it.

(6) By Richard Hipp (drh) on 2020-06-02 20:46:59 in reply to 1 [link] [source]

We can help you more if you:

  1. Send us your database schema.

  2. Send in the exact text of the INSERT statement you are using.

  3. Tell us specifically which version of SQLite you are using.

(7) By anonymous on 2020-06-02 23:47:50 in reply to 1 [link] [source]

Your bottleneck might be at the storage level. Does your process write to external SDcard? You could try to run it on 'internal'/system storage if there's such, usually it's faster.

I'd try to do the whole thing on a normal PC system, to rule out possible side-effects of mobile target environment. You get better tooling too for further diagnostics.