Repository navigation
Fetch large data for reports slow #6543
Description
I'm trying to fetch more than 100,000 rows for report generation.
I tried these approaches,
- Recursive calls with 1,000 limit to fetch all rows
- Using query.each() and push row in array
Both the calls are slow,
For cloud code query
query = //new query
query.equalTo("SOMEKEY","SOMEVALUE")
For total of 1,500,000 rows
In cloud code,
In recursive call approach, there is approx 1 second waiting time per 1000 rows.
In query.each() approach this query takes approx 60 seconds to process, http waiting time
I assume this is fastest parse can work.
Can i see some improvement by indexing the mongodb objectId column?
Is there a better approach for processing such huge data.
One alternative I can think of is parse aggregate but this approach doesn't respect parse authentication, security, acl etc. and doesn't work with existing query logic.
Will this show any performance improvement and worth a try?
Activity
acinader commentedon Mar 27, 2020
This should not be slow. The most obvious reasons why it may be slow:
Considering your question, I suspect that the problem is the item 1.
Trying properly indexing your collection.
mtrezza commentedon Mar 28, 2020
@omkar-tenkale You can look at the logs of your database to find out how long the query takes on the DB side.
omkar-tenkale commentedon Mar 30, 2020
With mongo profiling i can see 58 queries are run for 58000 rows
The last 2 blocks of log
omkar-tenkale commentedon Mar 30, 2020
Parse is still taking upto 10 seconds for 58000 items without any query constraints
omkar-tenkale commentedon Mar 30, 2020
The query i simple,this is the cloud codee
async function indexTest(req,res){
console.time("timelog");
var Product = Parse.Object.extend("Product");
var query = new Parse.Query(Product);
query.limit(1000000)
var res = await query.find();
console.timeEnd("timelog");
console.time("timelog");
console.log("resp size=="+res.length)
console.timeEnd("timelog");
return res.length;
}
omkar-tenkale commentedon Mar 30, 2020
This is the log (pm2 logs 0)
0|app1 | timelog: 10.097s
0|app1 | resp size==58110
0|app1 | timelog: 0.145ms
0|app1 | info: Ran cloud function indexTest for user undefined with:
0|app1 | Input: {}
0|app1 | Result: 58110 {"functionName":"indexTest","params":{}}
mtrezza commentedon Mar 30, 2020
In your mongodb logs I don't see any excessive execution times.
Can you turn on profiling to only log queries that take longer than 100ms?
mtrezza commentedon Mar 30, 2020
Actually, the
execStatsare empty in the logs you posted here. The stats should give you more hints about the execution time and whether an index was used for the scan, etc.omkar-tenkale commentedon Mar 31, 2020
How can i include execStats?
omkar-tenkale commentedon Mar 31, 2020
Here's what i could find
Cloud code request from parse-dashboard console
omkar-tenkale commentedon Mar 31, 2020
acinader commentedon Apr 1, 2020
it looks like the getmores are occurring in just a few milis.
there is a bottleneck of some sort.
If your run a query against the database form the server the iterates through all the objects in you parouct tables, how long does it take?
what does a product document 'look like' (i.e. how big is the json text).
what is the network topology between the database server and the parse-server? Are they on the same network? What is latency and bandwidth between the two?
omkar-tenkale commentedon Apr 2, 2020
In mongoshell its quiet fast,1-2 secs
Closely similar to
{
"_id": "y2IEREsCJR",
"_p_createdBy": "Employee$nqzq73IJZn",
"_p_company": "Company$othercmpny",
"_p_brand": "Brand$5KUm1qj624",
"name": "SOME NAM NAME NAME",
"parentName": "SOME NAM NAME NAME",
"ERPName": "SOME NAM NAME NAME",
"description": "SOME NAM NAME NAME",
"quantity": "28",
"season": "SOME",
"sleeve": "",
"basicRate": 1899,
"ERPCode": "2423423523532",
"pattern": "",
"subCategory2": "SLIM FIT",
"HSCode": 0,
"content": "SOME NAM NAME NAME",
"source": "",
"category": "JEANS",
"exciseItem": false,
"subCategory": "SLIM FIT",
"EANCode": 24332543232532,
"neck": "",
"unitOfMeasurement": "SOME NAM NAME NAME",
"SellingPrice": 1899,
"_created_at": "2020-03-13T23:30:32.098Z",
"_updated_at": "2020-03-27T11:36:35.814Z",
}
Parse server specs
npm list
[email protected]
Server version 3.9.0 (From Parse dashboard app list)
db.version()
2.6.10 <<Can be an issue?
System Ubuntu 16.04
Mongodb at localhost:27017 same server
Mongouri (parse default)
51 remaining items
mtrezza commentedon Nov 8, 2021
Re-opened for further investigation; however, the issues reported by different users here may or may not be related to each other. The underlying issue may be the conversion from MongoDB document to Parse Object which in its currently implementation seems to be quite resource demanding; maybe there is something to be improved.
In any case, we would need someone doing the testing to gather performance data to compare, otherwise we cannot investigate the issue and we may as well close it again.
SebC99 commentedon Aug 27, 2022
Old one, but while inspecting our servers for perf diagnostic, I discovered this issue.
It is not directly related but the parseDate.js (from the JS SDK) seems to be the major source of performance issues here:
In our case, this code block is taking 1% of our total CPU time 😅
dplewis commentedon Aug 31, 2022
@SebC99 I use query.find({ json: true }) to return the raw data because the JS encode to Parse.Object is slow.
SebC99 commentedon Sep 2, 2022
@dplewis I didn't even know we could do that... all the cloudcode could use this, and return json to the clients (we just need to add the
__type: "Object"property).We only need Parse.Object when we have specific parse operations to do on the objects (save/fetch/dirty, etc...)
oallouch commentedon May 11, 2023
@SebC99 I saw similar performance issues with the parseDate function.
Its source is :
SebC99 commentedon May 11, 2023
True
But I don't even think we need the regex. An invalid date would be thrown which can be catch. I don't remember the fastest way to check if a string is a date + creating the date object, but if I remember well, it's not a regex :)
SebC99 commentedon May 11, 2023
And If I remember correctly, one issue of course is the parsing which is slow, but all the too many calls to this parsing, as every hooks (beforeFind, afterFind, etc.) create the string objects to log before checking if the log levels are met.
oallouch commentedon May 11, 2023
Oh. Nice find.
You could definitely do something here.
SebC99 commentedon May 12, 2023
I've just checked, and the regex is better outside for the function of course, but the impact isn't huge.
Replacing the regex by
isNan(Date.parse(string))ornew Date(string)is 5 times faster. It slightly less rigid as it would accept "2022201" as a valid date String, but I don't think it would be an issue for Parse.And as the encode/decode operations are very very very used, the impact can be significant.
oallouch commentedon Sep 8, 2023
@SebC99 any news on this optim ?
oallouch commentedon Apr 11, 2024
+1