I’m developing an app for a client which has to load hundreds of rows (for now because we’re still in testing phase, it will increase in the future).
To briefly sum up how it works, the application firstly loads a list of Cities (first data table), and when an user selects one city, the application loads all the Points (second data table) which are related to the selected city.
The Points table has 2 other relations : coordinates (GEOPOINT), and type (PointType, third data table).
This is where the problem is happening. Currently I have a loop in my code (javascript application) which uses the javascript backendless app to retrieve the 332 Points records. As there’s a limit of 100 objects per request, the request is divided in 4 sub-requests.
And here’s what is weird : a single request retrieving 100 Points is taking approximately 7~8 seconds. And I suppose the slowing madness is coming from the relations because retrieving rows without loading those relations takes “no time” (at least a reasonable amount of time).
I’ve looked for other topics about slow query without finding any solution.
I’ve tried to reproduce the same request in the REST console of the table : the exact same thing is happening. (PS : and I have to re-authenticate very very very often in the REST console to test the request…)
Have you tried (or are you aware of) the approach where you load related entities (coordinates and type) using a separate call, that is the “loadRelations” API?
Do you mean using the loadRelations javascript function on each row that i previously retrieved (relation-less) ?
Because from what I just tested it’s supposed to be used on a single object and not on a collection.
So it’s going to make 332 ajax calls and it’s getting even worse…
Do I miss something in this approach ? (there’s not a lot of information on the javascript documentation about this part)
You’re correct, it would be one at a time and I understand it is not optimal. Let’s approach from another perspective: when user selects a city and you load all the points, what does the app need to do with the coordinates and type for each point? Does the app logic require all the coordinates to be present for all points at once?
That’s where it’s going to be a bit problematic : I do need to have all the points at once (at least it was the best way to have what I wanted.)
When selecting a City, I retrieve all the points linked to that City. Having all those points, I then know all the PointTypes available for this City.
This allows me to create the second selector of PointTypes, allowing the user to choose the desired PointTypes to display all the Points of this PointType and of this City.
My question was mainly to know if this response time seems normal from your side or if something could be wrong, maybe in the way I configured the database, resulting in this response time.
If you tell me that for that much rows/relations, it’s “normal” a request takes that time, it only means that I’ll have to change the way the algorithm on my side was conceived and there’s no problem with that (and I won’t bother you about that much longer ! (thanks for answering this way though !))
It is hard to say whether that response time is normal or not without knowing how many coordinates each point has (I assume it is a one-to-one relationship between a point and a type). Currently all related objects are returned for each of the 100 points in request. Which means you could have a rather large payload. However, what I see from the screenshot is each response is on average 75-80kb, which is not a lot. So perhaps it is something in the schema that might be optimized.
Perhaps it might yield a better performance to know all the PointTypes for a city in advance, or perhaps get that collection first and then go from there.
Regarding the schema, each relation listed above are one-to-one (Point <-> PointType, Point <-> City, and of course Point <-> GEOPOINT)
Having a City list and PointTypes list in advance would have two consequences :
I could make more filtered search of points having one particular City and one particular Type
Some PointTypes would lead to no results for one particular City
That's why I firstly made the opposite way : getting every points of a particular City then creating a PointTypes list from the collection of Points I previously retrieved.
I just made some tests of your approach (using REST console directly on the Data table), which would imply to have both City and PointType before searching Points, and it’s still taking an important amount of time.
For a query retrieving 99 Points objects (~60kb), the average time of the request is between 3.5 and 4s, more than 95% of it being waiting the response of the server…
(for this test I was loading Points + Coordinates (GEOPOINT) relation, narrowing the request to the minimal amount of things I need here)
I don’t see anything else to optimize more than this (“simple” request with 2 WHERE clauses and one relation loaded).
If you don’t have any idea of what could be wrong (or not, if it sounds normal) I guess i’ll have to look for caching implementation on my side.
What if the PointTypes collection would not be derived, but instead explicit as a separate table related to City? In other words, there would be a City-to-PointTypes (one-to-many) relation. That way you do not need to load all the points in order to extract relevant PointTypes.
Another optimization you could introduce is to move the data you currently store in the Point table to metadata of the corresponding Geopoint.
Please let me know if any of these sound reasonable and if you need any further guidance.
(thanks for answering this way though !))
