M query Join and FirstN taking infinite time to process

[abksharma]

Hi Team
we have an m Query which is as below. Number of records are 10k (two columns emails and business)

But below query is hogging our server. what can be the reason for this.

Custom1 = Table.TransformColumns(
Table.NestedJoin(TableDistinctDataEmail, 
{"Email"}, #"Filtered Rows",{"Email"},"Business", JoinKind.LeftOuter),
{{"Business", each Table.FirstN(_,1), type table}}),

 Thanks

[v-hashadapu · Accepted solution]

Hi @abksharma , thank you for reaching out to the Microsoft Fabric Community Forum.

Please consider potential reasons and their solutions provided below:

1. Performing a NestedJoin can be resource-intensive, especially if the tables involved are large or the join keys are not indexed. The complexity of the join operation increases with the size of the tables. Ensure that the columns used in the join operation (Email in this case) are indexed. This can reduce the time complexity of the join operation. Consider whether a different type of join might be more efficient, or if the join can be performed earlier in the data pipeline to reduce the number of rows being joined.
2. Table.TransformColumns is applied to the entire table, which can be quite expensive if the table is large. The transformation operation is applied to each row, further increasing the load. Apply any necessary filters as early as possible in your query to reduce the number of rows being processed in subsequent steps. Ensure that the transformation logic is as efficient as possible. For example, avoid nested transformations and use built-in functions that are optimized for performance.
3. If your server has limited memory, processing large tables and complex transformations might cause excessive memory usage, leading to performance degradation. If possible, reduce the number of columns or rows being processed by removing unnecessary data before performing the join and transformation operations. If feasible, consider increasing the memory and processing power of your server to handle the data more efficiently.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.