Android Question Best paging approach with a remote DB

[Informatix]

I have often the same question from my ULV users:
"How can I read n records from my remote database, then read another n records and so on? How can I display all the records in my view (and not only the last query)?"

I see at least two methods to retrieve the DB contents:
- you execute a query of n records and you save the result in a local database, which is the main DB used by your view; then, when the records are downloaded (or when the user hits the bottom of your listview), you redo another query to get the next n records that you add to your local table, and so on;
- you retrieve all records with a single query and you use directly the recordset (a cursor or a DB result object). EDIT: that does not answer to the question above of course but it's what most people do.

At work, we are used to deal with huge databases and we never apply the second solution because it has important drawbacks (transfer time, no data availabilty while offline) so, from this professional perspective, I usually recommend the first one (a local SQlite DB), but it implies to synchronize the two DBs and that seems a bit complicated for most developers I talk to. So is there a better/easier approach under Android?

 

[sorex]

I all depends on what data the app has to deal with and how much.

I would just download all data (zipped) during the splash screen of the app and go through it as needed.

 

[Informatix]

I all depends on what data the app has to deal with and how much.

I would just download all data (zipped) during the splash screen of the app and go through it as needed.

In most cases, the remote database contains images and a few hundreds of records. Let's say that we have 300 records with an image of 100 Kb in each record. If the data are loaded in the background, retrieving them takes one or two minutes with a good connection and a lot more otherwise. With such a loading time, waiting for all the data is possible, of course, but not user-friendly.

 

[Informatix]

In most cases, the remote database contains images and a few hundreds of records. Let's say that we have 300 records with an image of 100 Kb in each record. If the data are loaded in the background, retrieving them takes one or two minutes with a good connection and a lot more otherwise. With such a loading time, waiting for all the data is possible, of course, but not user-friendly.

When you have JPG images in a database, or MP3 or videos, zipping the database does not save much. I tested with a real database (5.2 MB -> 4.8 MB once zipped).
Unzipping the file will take also time and space on the device.

 

[sorex]

I'm not a fan of blobbing images into a database, I prefer to store only the path to the image.

If you do that you can fetch the image if it ain't available in file.dirinternal or another local directory. just fetch on demand.

 

[Informatix]

I'm not a fan of blobbing images into a database, I prefer to store only the path to the image.

If you do that you can fetch the image if it ain't available in file.dirinternal or another local directory. just fetch on demand.

If the images are not extremely important to use the app, you can run two asynchronous queries: one for the images, and the other for the remaining fields. This way, you can download at two different speeds. With the example above, you would have typically a recordset of 30 MB for the images and 1 MB for the other data. But this solution can only be used with views like UltimateListView which allows to update an item already on screen (the image is replaced by a placeholder until it is downloaded).
Caching the images instead of storing them in a DB (with my Cache lib for example) can be a good idea indeed if the images are a bit big (you resize them and store them in the cache, and the unused ones are replaced by the most recently downloaded to save space).

 

[KMatle]

At work we store Images (TIFF, about 30 - 100K) as a blob because it's easy to handle. If the Images are bigger (true Color, x MB) I prefer storing them in the file System. It's easy to dump/backup index rows but it's tricky if they contain the Images.

The rest depends on the app, how many rows you fetch. One should only fetch the rows the user really needs at that Moment (except you want to dump/backup a table ).

 

[LucaMs]

Let's say that we have 300 records with an image of 100 Kb in each record. If the data are loaded in the background, retrieving them takes one or two minutes with a good connection

Two minutes? I think they are too much (I mean you should be able to download them in less time, unless the rest of the record is made up of hundreds of KB, but I do not think, or your query involves many tables with joins; but in this last case the download would not be the problem, of course).

Your first method is right, I think.

 

[b4auser1]

When I started my career, many times I heard from users: Please let us to scroll thru resultset with all the data from the table. But someimes it means to scroll millions and even dozens millions records. I couldn't understand them. So my approach is to set limit on number of records in the resultset, but
1) Inform a user, that there are more records.
2) Provide filter tools, which user can apply to get only records which are required. Sure, the filters must work on server side.

 

[LucaMs]

About images: you can store their path but just to retrieve them from server; since you have to send them to clients the problem of download speed remains.

 

[Informatix]

At work we store Images (TIFF, about 30 - 100K) as a blob because it's easy to handle. If the Images are bigger (true Color, x MB) I prefer storing them in the file System. It's easy to dump/backup index rows but it's tricky if they contain the Images.

The rest depends on the app, how many rows you fetch. One should only fetch the rows the user really needs at that Moment (except you want to dump/backup a table ).

I edited my first post because there was an ambiguity. The question is about the first method (retrieving n records many times and aggregating the result). The typical case is: a few records are downloaded to populate the list at the beginning, then the app fetches another bunch of records when the user reaches the end of the list. This continues while the user scrolls.

 

[Informatix]

Two minutes? I think they are too much (I mean you should be able to download them in less time, unless the rest of the record is made up of hundreds of KB, but I do not think, or your query involves many tables with joins; but in this last case the download would not be the problem, of course).

Your first method is right, I think.

Downloading a 30 MB DB in the background is not very fast (I suppose that the user is interacting with the app while the download takes place). With a transfer rate of 500 KB/sec, it takes 1 minute.

 

[LucaMs]

Downloading a 30 MB DB in the background is not very fast (I suppose that the user is interacting with the app while the download takes place). With a transfer rate of 500 KB/sec, it takes 1 minute.

You're right, I've not calculated and usually I don't download images (or at least not 300 records with an image) so my was a sensation


Rather, I wrote that the method is right; in fact it is, but it does not take into account any changes made on DB after the first "download".

 

[Informatix]

You're right, I've not calculated and usually I don't download images (or at least not 300 records with an image) so my was a sensation


Rather, I wrote that the method is right; in fact it is, but it does not take into account any changes made on DB after the first "download".

You have to implement a synchronization method, for sure, and you cannot use OFFSET in your SELECT.
For example, you can mark all your records with a timestamp and select them according to this timestamp. The first time, you take n records from the most recent record. In the subsequent queries, you take n records from the last timestamp retrieved but also every record newer than your most recent record in the local DB, so you don't miss changes.

 

[moster67]

Couldn't one just store image thumbnails on the remote DB which are being loaded in ULV and when user taps on it, ULV downloads the full image? Would of course create more storage needs and traffic on the server/DB but could probably improve performance?

 

[Informatix]

Couldn't one just store image thumbnails on the remote DB which are being loaded in ULV and when user taps on it, ULV downloads the full image? Would of course create more storage needs and traffic on the server/DB but could probably improve performance?

If your database contains millions of records (it's the size unit of my databases at work, for example), that does not solve the problem. You cannot afford downloading the whole DB, even without images. The user expects to use the application right from the start, without excessive waiting time. Many apps offer the possibility to access the data in a progressive way (the more recent records are displayed first, and if the user asks for more, you do other queries and you add the new recordset contents to the records already displayed). So my question is: what's the easiest method to achieve this?

 

[Lahksman]

I think you're looking for something like infinite scrolling.
Maybe this link can help you.

 

[Informatix]

I think you're looking for something like infinite scrolling.
Maybe this link can help you.

Not really.
Your link is about the right timing to query more records and explains how to implement an endless scroll on the view side. It's interesting but it's not the asked question.

 

[incendio]

I'm not sure if fully understand your question.

If I understand correctly, I will use this method
1. Table structure of my local & remote database must have a field ID as primary key & timestamp
2. I will use sqlite in memory for local database
3. Retrieve most recent n records from remote database, seek its ID in local database, if found, replaced data in local database with data from remote database, if not found, insert data to local database.

 

[walterf25]

I have often the same question from my ULV users:
"How can I read n records from my remote database, then read another n records and so on? How can I display all the records in my view (and not only the last query)?"

I see at least two methods to retrieve the DB contents:
- you execute a query of n records and you save the result in a local database, which is the main DB used by your view; then, when the records are downloaded (or when the user hits the bottom of your listview), you redo another query to get the next n records that you add to your local table, and so on;
- you retrieve all records with a single query and you use directly the recordset (a cursor or a DB result object). EDIT: that does not answer to the question above of course but it's what most people do.

At work, we are used to deal with huge databases and we never apply the second solution because it has important drawbacks (transfer time, no data availabilty while offline) so, from this professional perspective, I usually recommend the first one (a local SQlite DB), but it implies to synchronize the two DBs and that seems a bit complicated for most developers I talk to. So is there a better/easier approach under Android?

Hi Informatix, not sure if this would help in what you're asking but i created a wrapper for a DB library for a customer some time ago, anyhow this library is what they call a NoSQL database, and Replication & Sync database, i've found it very useful and much faster and it will do what you are trying to accomplish in less time than what the SQLite database takes.

Here's a link to the project and documentation in case you want to take a look at it.

I really liked using this approach as it will keep all the data synchronized, meaning whatever change you make on the database on the server side will be sent to your local database on your device, i found it to be much much faster than SQLite, take a look, the only downside i see is that you will need to implement a service on the server side, but once that is done everything else is a piece of cake. I've never really stored images in any database, with this approach, the images are stored as Base64, once you retrieve a record with an image is very simple to just decode the image in B4A and convert it back to a .jpg, .bmp, or whatever type of image you are using. I will look for the library if you are interested, I will have to go back on the documentation and try to remember how to log in into the cloudant dashboard and take a look at the sample database i had created.

Anyhow, just thought i'd give my two cents.

Cheers,
Walter