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?
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?
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.
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
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
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.
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
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.
Even compiled, I got a stack overflow.
(Note I had to use the file name helper blocks for the CSV files. The Companion was more forgiving on case mismatches between file names.)
So the elegant approach fails.
Next attempt would be a trudge through the input csv table, after conversion. to load a dictionary of country cities.
Interesting. Which of the AIAs were you using to test this? This should result in tail call recursion being applied to prevent a stack overflow, so I'd like to investigate further.
I stripped the blocks down to the bare essentials.
Load the two files into global tables, one after the other.
Load the Country name picker
After picking a country, load the city picker from the city-country table.
That's where it gets the 8mb java stack overflow error.
