Count doesn't match the number of rows in a query?

UI Builder
1

If I don’t change the WHERE clause on a query, why would the count return a number larger than the rows of data? At least I think that is what is happening.

Table: Sellers
WHERE: SellersFundraisers[Sellers].Fundraisers.objectId = ‘856FC1D6-6BC3-4FC5-B0FF-F0840CFBEC9F’

Count says 243 -

image
image1214×522 22.9 KB

If I try to get 1 row at offset 241, there is no data -

image
image1318×615 22.9 KB

2

@Tim_Jones,

Your where clause condition contains relation.
In this case Backendless will make the join request which will return the number of occurrences from the resulting Cartesian set instead of the number of entries.

Regards,
Olha

3

Hi @olhadanylova - Thanks for the explanation. I don’t follow that 100%, but that is OK. How do I go about fixing the issue? I have more than 100 records I need to loop through, and getting the count is how it is done in this example -

Thanks,
Tim

4

Hi Tim,

Instead of relying on the “Count” function (which is not great from the performance perspective), use the following algorithm:

Codeless - BL - ConsoleDemo - Backendless 2022-11-15 19-31-44
Codeless - BL - ConsoleDemo - Backendless 2022-11-15 19-31-44431×636 99.5 KB

The key element in the algorithm is to loop through the entire data set using pageSize and offset and check if the size of the response is less than the page size.

This algorithm will perform much better as it does not have any dependency on the API call obtaining the size of the collectoin (i.e. count).

Regards,
Mark

5

Thanks @mark-piller

6

Hi Mark,

I need some guidance. Using your algorithm on a semi-large table, say 9000 rows (completely reasonable for a city table), would result in 90 rapid API calls.

So am I stuck between a rock and a hard place here? I either use count, which isn’t great for performance (per your comment) but is a single API call, or make 90 calls in a few seconds.

I am looking for suggestions on performance best practices as those will become very important shortly.

Thanks,
Tim

7

Hi Tim,

I am a bit confused. Do you need to load 9000 records or do you need to know how many are there (i.e. the count)?

Regards,
Mark

8

Hi Mark,

I want to know how many records are in the table.

Tim

9

In this case use the count API. My point earlier that relying on count for the purpose of performing paging is not recommended.

10

Thanks!