Paging technics and performance

Data Service
1

Dear backendless,

I am playing with paging and I have a question regarding performance.
I am querying a data table sorted by “created desc” order.

There are 2 technics which may be used to perform paging:

  1. page size + offset
  2. WHERE clause on sql

The advantage of the first method is that the client’s SDK offers method for easily page large queries.
The advantage of the second method is that it allows more control over how paging is done.
Also, I know that in MySQL it is very bad practice to use the OFFSET predicate as performance decreases dramatically with large offsets.

My question is whether there is a difference in performance with Backendless’s native database between using a WHERE clause and using an OFFSET predicate? Of course, as applied to large or very large tables (100,000s or even 1,000,000s of records).

Thanks!

2

How would you use the WHERE clause to accomplish paging?

3

Hi mark,

I want to retrieve records by descending created date. I would use the WHERE clause to start just before the last retrieved record, specifically:

WHERE created < sometimestamp

It is useful in the scenario where, for instance records are constantly added, in order to avoid having repeats in the response. To illustrate, take the following example:

  • Table contains N records as such:
recordN
recordN-1
recordN-2
...
record2
record1
  • client sends query SELECT FROM records ORDER BY created DESC
  • client received X first records (say 5)
recordN
recordN-1
recordN-2
recordN-3
recordN-4
  • a new record is added to the table:
recordN+1
recordN
recordN-1
recordN-2
recordN-3
recordN-4
recordN-5
...
  • client requests next page from offset 5:
recordN-4
recordN-5
recordN-6
recordN-7
recordN-8

Note the repetition of the first record (recordN-4) in this instance, hence the usefulness of using the WHERE clause to make sure you start where you left off, for instance:

SELECT FROM records WHERE created < created(recordN-4) ORDER BY created DESC

I hope this clarifies my question.

4

Hi Emmanuel,

In this example you’re not configuring paging, but simply create a condition in the where clause defining which objects should be loaded.

Regards,
Mark

5

Hi Mark,

I nevertheless achieve paging using this technic. My question is whether there is a difference in performance with Backendless’s native database between using a WHERE clause and using an OFFSET predicate, as applied to large or very large tables (100,000s or even 1,000,000s of records).

Thanks!

6

Hi Emmanuel,

You should not see any significant performance degradation with large or very large tables.

Regards,
Mark

7

Hi Mark,

I am new to android application development. Backendless is helping me a lot for my project. I was struck in middle. I need to load list of device for each user. I am not getting how to use paging… I tried my best for last 3 days. Please help me… Here is my code.
Device.java:
package com.anandkumar.mjira;
/*** Created by Anand on 6/3/2016.*/public class Device {
private String name;private String imei;private String owner;
public Device() {this.name = “”;this.imei = “”;this.owner = “”;}
public String getName() {return name;}
public void setName(String name) {this.name = name;}
public String getImei() {return imei;}
public void setImei(String imei) {this.imei = imei;}
public String getOwner() {return owner;}
public void setOwner(String owner) {this.owner = owner;}}
////////////////////////////////////////////////////////////////////////////////////////////////DeviceListFragment.java:
private void getDeviceList(String user) {
BackendlessDataQuery query=new BackendlessDataQuery();query.setWhereClause(String.format(“owner= ‘%s’”,user));
QueryOptions options=new QueryOptions();options.addSortByOption(“name ASC”);query.setQueryOptions(options);
Backendless.Persistence.of(Device.class).find(query, new AsyncCallback<backendlesscollection<device>>() {@Overridepublic void handleResponse(BackendlessCollection<device> response) {List<device> dList=response.getData();
int size=dList.size();Log.d(“No.of Devices:\t”, ": " +size );if(size==0){Toast.makeText(getActivity(), “No Devices!!”, Toast.LENGTH_SHORT).show();
}else {adapter = new DeviceListAdapter(dList);AlphaInAnimationAdapter alphaAdapter = new AlphaInAnimationAdapter(adapter);alphaAdapter.setDuration(1000);recyclerView.setAdapter(alphaAdapter);
}
linlaHeaderProgress.setVisibility(View.GONE);}
@Overridepublic void handleFault(BackendlessFault fault) {
}});
This is my code for calling device list.
Please help me to use paging technique in my project…
Thanks,

Code Runner data.rar (2.16kB)