Data loading is very slow and taking 30 minutes

[ashwinijain932]

Hi,

I have a query as below. It is taking 3 seconds to load 40K rows in SSMS but taking more than 30 minutes to load in Power BI once I do "Close & Apply" in Power Query. I am not able to understand why it's happening and what can I do to improve the performance.

WITH cte_mths AS (
-- Get distinct months for each contract, trimming the Contract field
SELECT DISTINCT
JCCo,
TRIM(Contract) AS Contract,
ThroughMth = Mth
FROM brvJCCostRevenue
),
cte_jc AS (
-- Join contracts and revenue data for months less than or equal to ThroughMth
SELECT
m.JCCo,
m.Contract,
m.ThroughMth,
r.Mth,
r.ProjMth,
r.ActualCost,
r.ContractAmt,
r.CurrEstCost,
r.ProjCost,
-- Calculate estimated cost completion at the record level
EstCostCompletion = CASE
WHEN r.ProjMth <= m.ThroughMth THEN r.ProjCost
ELSE r.CurrEstCost
END
FROM cte_mths m
INNER JOIN brvJCCostRevenue r ON m.JCCo = r.JCCo
AND m.Contract = TRIM(r.Contract) -- Trim only in the join condition
AND r.Mth <= m.ThroughMth -- Match Crystal filtering
),
cte_sum AS (
-- Aggregate data for each contract and month
SELECT
JCCo,
Contract,
ThroughMth,
SUM(ContractAmt) AS ContractAmt,
SUM(ActualCost) AS ActualCost,
SUM(EstCostCompletion) AS EstCostCompletion
FROM cte_jc
GROUP BY JCCo, Contract, ThroughMth
),
cte_earned AS (
-- Calculate the earned amount to date for each contract and month
SELECT *,
EarnedToDate = CONVERT(DECIMAL(18, 2),
CASE
WHEN ContractAmt - EstCostCompletion <= 0 THEN ContractAmt + ActualCost - EstCostCompletion
WHEN EstCostCompletion <> 0 THEN (ActualCost / CONVERT(FLOAT, EstCostCompletion)) * ContractAmt
ELSE 0
END)
FROM cte_sum
)
-- Final SELECT with LAG function for prior EarnedToDate and ThroughMth_EarnedToDate calculation
SELECT *,
priorEarnedToDate = ISNULL(LAG(EarnedToDate) OVER (PARTITION BY JCCo, Contract ORDER BY ThroughMth), 0),
ThroughMth_EarnedToDate = EarnedToDate - ISNULL(LAG(EarnedToDate) OVER (PARTITION BY JCCo, Contract ORDER BY ThroughMth), 0),
ContractKey = CAST(JCCo AS VARCHAR) + '-' + Contract -- Custom column with JCCo and Contract
FROM cte_earned;
 

[Anonymous · Accepted solution]

Hi @ashwinijain932 ,

Based on your query, the issue may be related to the fact that Power BI handles data transformations and loads differently than SQL Server Management Studio (SSMS).

• Sometimes clearing the cache in Power BI can help improve performance. This can be done in Power BI Settings under Options -> Data Loading -> Clear Cache.

• If possible, use DirectQuery mode instead of import mode; DirectQuery allows Power BI to query the data source directly and may be faster for large data sets.
• If you are dealing with large datasets, consider using incremental refresh. Refer to below document:Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ashwinijain932]

I cleared the cache but still the same issue. Any other way, I could fix this issue?

[nandic]

@ashwinijain932 i have one amazing recommendation: restart your machine 🙂
Do you use vpn or it is on your local machine?

Back to the point, i would start testing it part by part.
Firstly paste only script for the 1st cte table. Then for the next. Then for the next.
Once you check each cte table, you will see if one of them is causing issues.

If everything is fine, then start checking combination of these cte tables, adding one by one.
Adding filter condition to return less rows, just to make sure it works.
Finally, if lag function is the issue, maybe there is better way to handle it within Power BI.

Good luck 🙂

Cheers,
Nemanja