Hi everyone.
I'm developing an app in witch I retrieve a list of countries and cities from a csv file that I have uploaded on my MIT app inventor. I'm testing it with companion.
The problem is that I have to wait about 58 seconds in order to see my spinner populated with data... How can I solve this?
Is it a problem that I will have when I will install the final app on my phone?
Please help me 
Thank you!!!
Use SQLite.
Is it a database?
Is it free?
How can I use it?
I use in my app tinywebDB... Does it work in the same way?
That's unusual. It should be instantaneous, if coming from a Media text file.
Export your aia and post it here?
Hi ABC and very thank you for your message!!
I would like to better explain my app.
I need to populate 2 spinners in witch first selects the country, second selects the city between the cities of selected country.
In design, media section, I upload 2 csv files
- countries.csv : 252 rows, 1 column
- countries_cities.csv :159068 rows, 2 columns
In design, I add 2 component "File" that you can find in storage section. I name these
- File_da_countries
- File_da_countries_cities
Attached 4 screenshot of blocks used.
When I select country, I have no delay, but I have 58s delay when I ask to populate the second spinner with the cities of selected country.
My questions are
- Is the problem the csv file dimension?
- Is the problem the check I ask to do in second list that I need to populate the second spinner?
- Will I have the possibility to use csv approach with no delay? Or other solutions are mandatory?
Thank you very much to all for the help!!!
Why did you need to white out the spinner names?
Why not upload the aia export to make it easier to help?
You only need to load the city table once and index it into a permanent data store like TinyDB namespaces.
Why the clock timer? Are you putting on a show while loading?
Read the nb191 advanced list blocks article.
Many thank you for your help ABG and for link to nb191.
If I have well undertood, you suggest to put all cities csv data in a tinydb and populate spinners from tinydb?
Why? Filter and retrieve from tinydb is faster then from csv?
Are data to much for a tiny DB to save on smartphone of users ( countries_cities.csv :159068 rows, 2 columns)?
Will the tiny db be create every time the app will be opened?
Not if you check first to see if they are already loaded.
https://groups.google.com/g/mitappinventortest/c/AX1gPrAcuvY/m/2PWHn_NxrrsJ
The benefit from TinyDB would come from its ability to index data. So if you made a TinyDB Namespace that had countries as tags and lists of city names for that country as values, you might get a benefit of a speedup at query time.
Alternatively, you could set up a TinyDb Namespace that uses city names as tags, and a list of associated countries that have a city with that name as values.
Have you tried just loading the raw csv table into a table (list of lists) at startup and measuring the time needed for that, without any filtering or other processing, leaving that for run time queries?
Regarding your spinners, it is unrealistic to load such a large list into a spinner.
Check out this project that allows search by name range:
This is a Divide and Conquer approach, which gets to your selection in logarithmic time.
TinyDB can't filter, only index.
It has yet to be determined if the native AI2 advanced filter blocks would be slower than a preloaded SQL approach, which I have not used.
Yes, sqlite would be much faster than TinyDB
See the faq, question 9 here App Inventor Extensions: SQlite | Pura Vida Apps for a speed comparison.
SQLite is many many times faster. For example fillup 100,000 lines: TinyDB over one hour, SQLite 6 minutes. And fully fledged work with SQL commands - priceless. Thanks for your Extension
Taifun
Many thanks Taifun and ABG!
As soon as possible I would like to do a couple of tests on my app beginning from your precious tips. I will come back soon!
Dear all, after a lot of tests I'm sure that the time delay is due to the 'compare text' section of my blocks.
(not due to csv import, or spinner population, etc...)
I attach 2 simplified project.
TEST_CSV
After you select 'scegli country', the button 'crea lista filtrata' only populate the variable... and here I have a 40s time delay
Whit other buttons you can after populate a label or the spinner, or select one element from the countries_cities list
TEST_CSV_con_TINYDB
Here I have modified TEST_CSV App by introducing TinyDB. My intentiin is to populate the DB at initialize, with a tag for one country and a list of cities as value for a tag.
... But here I have an INFINITE delay
How you suggest to solve my problem?
TEST_csv_con_TINYDB.aia (780.3 KB)
TEST_csv.aia (779.8 KB)
Use a sqlite extension, for example App Inventor Extensions: SQlite | Pura Vida Apps
There also exist other sqlite extensions
Prepare your data in a table inside the database and import the database on first run of your app, for an example follow the link above
Then use select statements to retrieve the data you need
Taifun
1 Like
Thank you Taifun. I asked info regarding SQlite at the beginning of this discussion...
I will use my app for business.
Can I free use SQlite and SQlite extension for business?
Can I free use SQlite without time limits ?
What about a server for DB hosting that is free without time limits?
There are Free and Paid SQLite extensions
You can use them for whatever purpose you choose, within the requirements of any licence set on the extension.
Free or Paid, there are available for use without time limits
There are many "free" hosting servers available, you will need to find one that suits your needs. That is beyond the remit of this community.
Yes, the license is per app developer and you can create as many projects as you like with the extension
You can search the internet for free web hosts, but frequently they only offer a webpage but not accessing data in a MySQL database
EDIT: see for example Can anybody help me with this? Web Component not sending data to php - #3 by Juan_Antonio
Also your data is static and does not change, isn't it? So no online database would be required... also a local solution as the local sqlite database does not require internet access and could be faster than querying an online database because you can save the transfer time from the app to the database and back again
Taifun
Please Taifun, how can I create and use a local SQ lite? It seems the best solution
Here is a basic sqliteDB of your data
cc.txt (4.1 MB)
Rename cc.txt to cc.sqlite.
What you should really do it setup a relational database, so that the cities are represented by the index of the country, e.g. Vila,Andorra -> Vila,1 this will help to speed things up even more.
You can find information on my webpage App Inventor Extensions: SQlite | Pura Vida Apps
Also a sqlite tutorial might help
Taifun