Slow MongoDB Atlas SQL ODBC query to Power BI

MongoDB Atlas
1

i am using MongoDB atlas sql with data faderation on M10 cluster with aws 5gigabit bandwidth and powerbi connector and conecting to it from power bi using the mongodb atlas odbc connector
the cluster and connector and gateway and machine are all in the same region and the connection speed on the machine running power bi is 800MB/s
the connection is established
i can read my tables i can refresh it everything is working fine

My problem is loading the data and refreshing it is taking a lot of time for example:
loading 86000 rows is taking around 7-9 minutes to load or refresh
and during the loading phase i can see that it is loading the rows at a speed of 260-280 rows per second
and after around each 2600 rows the loading hangs for about 2 seconds then continues .

i tried to switch to mongodb connector instead and that was a lot worse it was loading 100 rows each 30 seconds

looking at the mongodb video shared on youtube i can see that they were able to load the table using the same setup at 2500 rows per second and then 27000 rows were loaded in 4 seconds somehow

has anyone faced this slow query issue ? or how to solve it ?

2

You have to upgrade your MongoDB Atlas cluster size higher tier that offer more resource like CPU and memory capacity. This increase the query performance for handling large dataset more efficiently.

3

we tried that resources is not the issue we upgraded to M30 still nothing changed we even downgraded to M0 and still had the same performance and if i monitor reatime resrources i can see that it is barely using any of the availble resources on each query

4

Did you manually create a federated database or are you using a SQL Quickstart one? If you manually created a federated db, I would create a view on the federated db (you can use Compass to do this) where you are reshaping the data here (maybe doing some flattening, unwinding, and limiting of fields not necessary for reporting). Just do a quick POC with this to see if your results change for loading times. While you can and do have the option to to flatten, unwind and transform the data within Power Query, this isn’t always the most efficient method depending on your success criteria and the shape of your MongoDB data (lots of nesting etc.).