There is a news aggregator. Articles are downloaded from the network, and stored in local SQLite . The UI is organized in the standard way: RecyclerView , whose elements are previews of articles.

The question is: how to optimally organize the loading of data into RecyclerView from SQLite , if ≈ 3 elements fit on the screen?

Let me explain: in this matter, the time for loading articles from the network, their placement in the database and the time for converting articles into previews is ignored. Interested in the mechanism of loading elements in RecyclerView for one request and the organization of this loading. The standard mechanism (we load some elements -> the user scrolls the list to the end -> we load a new portion of elements) does not fit.

For example, maybe you should catch the scrolling in the middle of the list and then start uploading new data ? In addition, it is possible that a respected community has experience on how many elements from SQLite optimally collected in a single query? Because there is a suspicion that the loading of 3 elements from SQLite will be approximately equal to the loading time of 30 elements, since Most of the time is spent on the transaction itself, but let's say, it’s not optimal to request 300 items per request.

  • 3
    for one request you need to "take" all the necessary data, there is no point in dividing them into small parts. This applies to network queries, but not to local databases. Cursor himself will load what he needs, in the quantity he needs, as far as I can remember. In any case, you should not make a problem out of it - pavlofff
  • you somehow bother. The last pixel or samsung will load you in a second from the base of over9000 elements. the cheap maze will start puffing already at five - Sviat Volkov
  • 3
    All these "load more" were invented in order to save network traffic. load all locally at once - Sviat Volkov
  • @SviatVolkov and @pavloff generally agree with you, I really bother a little and it is for optimization on low-power devices. In order to load "all the necessary data", but not in portions, I do not agree. Because even if the device is powerful, at least you need to look at the size of this data. SQLiteCursor has a CursorWindow buffer, about 2 MB in size. And if your query pulls out> 2 MB, there will already be a relatively large overhead in time from the SQLiteCursor . - Alex Kisel
  • For performance issues with SQLiteCursor with large queries, see this article - Alex Kisel

2 answers 2

@pavloff, of course everything is correctly painted:

for one request you need to "take" all the necessary data, there is no point in dividing them into small parts. This applies to network queries, but not to local databases. Cursor himself will load what he needs, in the quantity he needs, as far as I can remember. In any case, do not make problems out of it.

But the problem is that the RecyclerView out of the box does not know how to support the adapter with the cursor, that is, you have to bother with the invention of the adapter that will read from the database or come up with an adapter that runs on Cursor , similar to the CursorAdapter , which is included in the ListView .

Fortunately, smart people have already written everything. Take the RecyclerViewCursorAdapter - give him a Cursor prepared by him at the entrance and you will be happy

  • Thank! Good decision. So far, so as not to reinvent the wheel, I look in the direction of the Paging Library - Alex Kisel
  • one
    There are opinions that the paging library is a bit faster than the Cursor - Alex Kisel

Understood the problem. I will provide my answer.

First, why for large enough lists you should not load all the data for RecyclerView from SQLite once:

  1. It makes no sense to load data that the user may not see.
  2. If, with the same responsiveness of the interface (without brakes), we can ensure a smooth loading of data and, at the same time, our application will eat less memory , then why not do so?
  3. Using SQLiteCursor has the peculiarity that when sampling data larger than 2 MB, there are additional time costs for sampling each subsequent 2 MB of data. The point is that the size of the SQLiteCursor buffer used by SQLiteCursor ( CursorWindow ) is about 2 MB. For more information about this issue, see this article: original (en) and translation into Russian .

There are solutions for smoothly loading data into SQLite RecyclerView : see, for example, this series of articles (en).

There is evidence that it is optimal to load at least 10 times more RecyclerView elements at a time than is displayed on the screen. That is, if there are 3 elements on the screen, we load about 30 at a time. It is necessary to start loading the next portion of data in half or 2/3 of the scrolling to the end of the list. These are general tips, because it is worth considering the size of the data, the possible conversion of them into POJO-objects for the list, the features of the RecyclerView implementation

Finally, there is a library from the Google Paging Library (not yet released in release 1.0), which is designed, among other things, to solve the problem raised in the question.

Another example is the implementation of loading using RxJava ( 1 part , 2 part ).

  • one
    I realized that although CursorWindow has some flaws in the algorithm, it still does the task of loading data better than you write your load algorithm in chunks (not to mention that you cannot avoid 90% of problems CursorWindow, listed in the article or it is necessary to write a very complex buffer, which will give a very small advantage in speed, since the vast majority of modern devices are powerful enough for such tasks). In general, I am still confident that there is no practical meaning to “bother” with loading data when running SQLite. - pavlofff
  • one
    about PagingLibrary as missed, I will look at a leisure. It is also worth noting that most of the problems from the article are solved using the CursorLoader (background loading and cursor management). I will put a plus for the work done, which contains a lot of useful information, although I disagree with the conclusions - pavlofff
  • one
    If we talk about a bunch of SQLite and POJO, then there are all reasons to refuse this bundle and use nosql DB Realm or ObjectBox (for simpler tasks) or the same ORM Room, which Google is now actively moving (although it essentially loses nosql speed solutions). ), although taking into account your desire to save on matches, they may not be satisfied with you (after the conversion of the relational structure into POJO "into manual" will take more resources than low-level implementations in nosql DB). - pavlofff
  • Thanks for the advice. I SQLiteCursor not going to rewrite SQLiteCursor , of course. You are right about Realm, just considering this ORM as a replacement for SQLite for the future. Regarding CursorLoader , I saw it in conjunction with ContentProvider - it is really convenient, but it does not gain in speed. Google Paging Library is just in conjunction with Room and promotes to the masses. - Alex Kisel