Poor Query performance in Data Federation

Data_Services · 2024-02-07T01:17:00.501Z

SELECT * FROM FLATTEN(orders)
WHERE
    updatedAt >= CAST('2024-01-29T12:56:05.408301+00:00' AS BSON_DATE)
    AND updatedAt <= CAST('2024-02-29T12:56:05.408301+00:00' AS BSON_DATE)
LIMIT 100
OFFSET 0

This query takes 21 Seconds to run. there are only 34k documents in the collection

if I now add an UNWIND to the mix - which is our Key reason for using the DATA federation system - the following query now takes 3 Minutes.

SELECT * FROM FLATTEN(UNWIND(orders WITH PATH => items) WITH SEPARATOR=> '__')
WHERE
    updatedAt >= CAST('2024-01-29T12:56:05.408301+00:00' AS BSON_DATE)
    AND updatedAt <= CAST('2024-02-29T12:56:05.408301+00:00' AS BSON_DATE)
LIMIT 100
OFFSET 0

I have created an index on updatedAt. and I can see it exists in the UI.

Indeed, if I run a find query normally using the following
{updatedAt: {$gte: ISODate('2024-01-29T12:56:05.408301+00:00'), $lte: ISODate('2024-02-29T12:56:05.408301+00:00')}}
The results are almost instantaneous.

This slowness feels wrong.

Irwin_Dolobowsky · 2024-02-20T16:32:54.049Z

Thanks for your question!

For this query, is it going against a single cluster or are there multiple data sources being queried at once?

techwebdeveloper · 2024-11-20T12:43:17.081Z

I am facing the same issue, and i have a federated instace configured under a single cluster, which has grouped collection from 2 databases within the same cluster, and the federated queries are taking huge time to run, are we missing something here ?