Execute stored procedure within Azure Logic App fails with Gateway Timeout

3
votes

I've been trying to develop an Azure Logic App that imports files from an FTP server and with a stored procedure in an Azure SQL service parse the contents.

Currently I've been struggling with executing this stored procedure from the logic app; the stored procedure can take up to 10 minutes to execute.

I've been trying a few solution setting up the Execute Stored Procedure Action in the Azure Logic App: - Add execute stored procedure as an action with an asynchronous timeout of (PT1H) - Surround it with a do-until loop that checks the return code.

None of these solutions seem to be resolving the issue. Does anyone have anything else I can try when developing this Azure Logic App?

1
azurestored-proceduresazure-logic-apps

1 Answers

0
votes

If you could reduce the time of SP by reducing the data payload in the tables under JOIN, then you could use pagination to achieve the successful execution via Logic App.

For example let's say you have a stored procedure like sp_UpdateAColumn which updates columns on tableA based on JOINs with tableB and tableC and tableD

Now this does run but takes more than 2 minutes to finish, because of the large number of rows in tableA.

You can reduce the time on this SP by say creating a new column isUpdated on tableA which is say boolean and by default has value =0

So then if you use 

SELECT TOP 100 * FROM tableA WHERE isUpdated =0

instead of whole tableA in the JOIN then you should be able to update the 100 rows in under two minutes.

So if you change your definition of SP from sp_UpdateAColumn to sp_UpdateAColumnSomeRows(pageSize int) then in this SP all you need to do is in the JOINs where you use TableA use (SELECT TOP (SELECT pageSize ) * FROM tableA WHERE isUpdated =0) instead.

Now you need to ensure that this new SP is called enough times to process all records, for this you need to use a do-until loop in logic app ( for total rows in TableA/pazeSize times) and call your SP inside this loop.

Try tweaking with PageSize parameter to find optimal paging size.