B4J Tutorial [B4X] SQlite with 6 million rows

KMatle

Expert
Licensed User
I've written a tutorial about database modelling and how long queries in a good designed db environment should take. This is a example about using huge databases. What it does:

- creates two tables (customers and orders)
- inserts 1 million customers and 5 orders per customer = 5 million orders = 6 million rows total
- creates one single index on orders (customer id to access every order as fast as possible)
- shows the time used for any query
- all db functions are inside an own module
- the code can be easily converted to B4A (just put it in a service/the starter service)
- note: don't use huge db's in an Android app as it is not a server :)

Just enter a customer id and see how long it takes. On my WIN 10 notebook (i5) with 12 GB RAM and SSD it is incredible fast. A query to show the orders of one customers takes 0.001 secs including the processing of the result set and building a list with the rows as maps. Please give it some time to insert all the rows (about 2-3 mins depending on your hardware). You will need about 420 MB of free disk space for the db.

I use the latest sqlite-jdbc-3.21.0.jar file which can be downloaded here: https://bitbucket.org/xerial/sqlite-jdbc/downloads/ Copy it to the additional libs folder.
 

Attachments

LucaMs

Expert
Licensed User
On my WIN 10 notebook (i5) with 12 GB RAM and SSD it is incredible fast
On my old Win 7 - i5 - 8 GBram - no SSD... same time, 0.001 sec.

but...

Please give it some time to insert all the rows (about 2-3 mins depending on your hardware)
199.36 sec = 3.323 min.

Creating the index after the SINGLE transaction (removed the lines that create a new transaction every 1000 insertions):
165.031 sec = 2.75 min
 

keirS

Well-Known Member
Licensed User
On my old Win 7 - i5 - 8 GBram - no SSD... same time, 0.001 sec.

but...


199.36 sec = 3.323 min.

Creating the index after the SINGLE transaction (removed the lines that create a new transaction every 1000 insertions):
165.031 sec = 2.75 min
It would work just as quickly with 1gb, 2gb or 4gb. The defaults for SQLite are a page size of 4096 bytes and a cache size of 2000 pages. So unless you change the cache size the maximum amount of memory used for a SQLite DB is 2mb.
 
Top