Datanory Production

Find out this query logic taking long time to run :

select * from #temp_mobile_txn_stock_count l1
left join #temp_mobile_txn_stock_count l2 on l1.row_no =l2.row_no + 1 and l1.customer_id=l2.customer_id and l1.product_id=l2.product_id
left join #temp_mobile_txn_stock_count l3 on l2.row_no =l3.row_no + 1 and l2.customer_id=l3.customer_id and l2.product_id=l3.product_id

Replace with below :

WITH OrderedTemp AS (
SELECT ,
LEAD OVER (PARTITION BY customer_id, product_id ORDER BY row_no) AS next_row_no
FROM #temp_mobile_txn_stock_count
)
SELECT l1.,
l2.*,
l3.*
FROM OrderedTemp l1
LEFT JOIN OrderedTemp l2 ON l1.row_no = l2.next_row_no
LEFT JOIN OrderedTemp l3 ON l2.row_no = l3.next_row_no;

Issue has been resolved and portal able to show report data. Escalate back to Azhar to verify with user on the resolution.

Root cause : Query Performance Issue