Azure Analytics Synapse SQL very slow?

Question

Hi,

We recently moved to the lake. I was told we share resource within the region so it depends on usage, SQL queries' response time could vary.

Is it normal for this sample query below takes 7 minutes and 20 seconds? That's super slow.

SELECT 
  t1.Period,convert(date, left(t1.Period,4)+'-'+Right(t1.Period,2)+' -01') as transdate,
  SUM(t2.Savings) AS Balance, 'Savings Account' as Type
FROM 
  a t1
JOIN 
  a t2 ON LEFT(t2.Period, 6) < LEFT(t1.Period, 6)
GROUP BY 
  t1.Period
union all
SELECT 
  Period, convert(date, left(t1.Period,4)+'-'+Right(t1.Period,2)+' -01') as transdate,
  (
    SELECT SUM(openingBalance)
    FROM b 
    WHERE Period < t1.Period
  )  AS Balance, 'Beginning Daily Cash Balance' as Type 
FROM 
  b t1
  group by period 
ORDER BY 
  Period desc

Answer 1

Hi silver subie,

Thank you for posting query in Microsoft Q&A platform.

Performance of query depends on lot of parameters. I would encourage you to consider checking below two links and see which can fit in your case to increase performance.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool

https://techcommunity.microsoft.com/t5/fasttrack-for-azure/synapse-serverless-sql-pool-performance-and-cost-optimization/ba-p/3673286

Hope this helps. Please let me know how it goes and if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well.