B4J Tutorial [B4X] SQlite with 6 million rows

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

  • SQLiteExample.zip
    4.8 KB · Views: 1,013

LucaMs

Expert
Licensed User
Longtime 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
 

KMatle

Expert
Licensed User
Longtime User

keirS

Well-Known Member
Licensed User
Longtime 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