We are facing an issue running three stored procedures (SPs) together using the Azure Logic Apps service and SQL databases.
Regardless of the run sequence of the SPs, the issue always shows up in the first SP that is run.
The processing initially used to take few seconds to run and execute successfully, but we noticed that the execution time and the required DTUs (database throughput units) were increasing day by day.
One of the SPs uses a variable that filters table TABLE1 by date, and shows only the “current day” data in TABLE2. The filter variable (used in the SP) is defined in the Logic App as SUBSTRING(startOfDay(utcNow()), 0, 10), which returns the current day value.
Our code consists of filtering the values of TABLE1 for the current day only, put it in TABLE2, run some calculations on it and store the results back in TABLE1. The WITH, Last_Value, First_Value and INSERT functions are used inside the SP code. The INSERT function is used to insert the resulting rows into TABLE1 (about ~74 rows).
The number of data rows recorded in TABLE1 increases everyday but the number of data rows in TABLE2 is constant since it is the result of filtering “current day”, so it only shows the current day's values.
Using SQL Server Management Studio, the SPs are manually executed without any problem.
At the beginning, when our database DTU was set to 400, the Logic App for the first SP was taking less than 10 minutes to execute, and the remaining two SPs took a few seconds each.
Lately the first SP is failing to execute, giving a GatewayTimeout error and the Logic Apps execution fails:
When we increased the DTU to 800, the Logic Apps runs successfully, but we still get a warning of GatewayTimeout.
The connection with the database was checked, the manual execution of each individual SP was tested, a change in the filter variable datatype from datetime to varchar (to match with the Logic Apps parameter) has been made, but the problem keeps occurring.
There is a tendency that this issue will recur if not solved. Any suggestions?
Thanks.
UPDATE
This is the code of one SP:
WITH enTotTable
AS
(
SELECT --TOP(100)
se.id
, se.gatewayName
, se.deviceId
, se.ts
, se.pointNameId
, case
when se.presentValue < 0 then 0 else se.presentValue end as presentValue
, DATEPART(hh,se.ts) as hTs
FROM dbo.SolarEnergyTable se
WHERE se.gatewayName IN ('DPJW', 'DAN1') and se.pointNameId = 7 and not se.presentValue = 0 and se.ts >= @tsFilter
)
, calculationTable AS
(
SELECT distinct
FORMAT ( tot.ts , 'yyyy-MM-dd' ) as dayTs
,tot.deviceId
,tot.gatewayName
, CASE
WHEN Last_VALUE(tot.hTs) OVER(partition by tot.deviceId, DATEPART(dd, tot.ts)
ORDER by tot.hTs ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FIRST_VALUE(tot.hTs) OVER(partition by tot.deviceId, DATEPART(dd, tot.ts)
ORDER by tot.hTs ) >=12
then 1
Else NULL end as ifAllDay
, CASE
WHEN Last_VALUE(tot.hTs) OVER(partition by tot.deviceId, DATEPART(dd, tot.ts)
ORDER by tot.hTs ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FIRST_VALUE(tot.hTs) OVER(partition by tot.deviceId, DATEPART(dd, tot.ts)
ORDER by tot.hTs ) >=12
then (LAST_VALUE(tot.presentValue) OVER(partition by tot.deviceId, DAY(tot.ts)
ORDER by tot.deviceId desc, tot.ts
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FIRST_VALUE(tot.presentValue) OVER(partition by tot.deviceId, DAY(tot.ts)
ORDER by tot.deviceId desc, tot.ts) )
else NULL
end as enTdy
,case
when LAST_VALUE(tot.presentValue) OVER(partition by tot.deviceId, DAY(tot.ts)
ORDER by tot.deviceId desc, tot.ts
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) < 0 then NULL
else
LAST_VALUE(tot.presentValue) OVER(partition by tot.deviceId, DAY(tot.ts)
ORDER by tot.deviceId desc, tot.ts
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
end as lastPvalueDay
, case
when FIRST_VALUE(tot.presentValue) OVER(partition by tot.deviceId, DAY(tot.ts)
ORDER by tot.deviceId desc, tot.ts
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) < 0 then NULL
else
FIRST_VALUE(tot.presentValue) OVER(partition by tot.deviceId, DAY(tot.ts)
ORDER by tot.deviceId desc, tot.ts)
end as firstPvalueDay
FROM enTotTable tot
WHERE not tot.presentValue = 0
)
, plantAndTdyTable
AS
(
SELECT
tdy.gatewayName
,tdy.dayTs as ts
,CAST(2 as INT) as deviceId
, CAST(20 as INT) as pointNameId
, Case When
tdy.ifAllDay = 1
then SUM(tdy.lastPvalueDay)-SUM(tdy.firstPvalueDay)
else NULL end as presentValue
FROM calculationTable tdy
GROUP BY tdy.dayTs ,tdy.ifAllDay, tdy.gatewayName
UNION
SELECT
tdy.gatewayName
,tdy.dayTs as ts
,CAST(2 as INT) as deviceId
, CAST(17 as INT) as pointNameId
,SUM(tdy.lastPvalueDay) as presentValue
FROM calculationTable tdy
GROUP BY tdy.dayTs, tdy.gatewayName
UNION
SELECT
tdy.gatewayName
, tdy.dayTs as ts
, tdy.deviceId
, CAST(6 as INT) as pointNameId
, tdy.enTDY as presentValue
From calculationTable tdy
)
INSERT INTO [dbo].[SolarEnergyTable]
SELECT
pt.gatewayName
,pt.ts
,pt.deviceId
,pt.pointNameId
,pt.presentValue
FROM plantAndTdyTable pt
-- SELECT *
-- From
-- plantAndTdyTable
-- -- calculationTable
-- -- enTotTable
