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.
- 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.