Best approach to call 22 K api requests and update SQL server table on daily basis

Question

We have created an SQL database on Azure, and it contains around 1.5 million records. now on daily basis we need to call an external api on average each day there will be around 22 K api requests. then for each request we need to check if the item exists inside the SQL table using the Contract Number field, if so to check the status, if it differ to update the SQL item with the new data, if the the status is the same do nothing. Also if the contract number does not exists inside the SQL table to create it , and so on..

Now i am not sure on which approach to follow:-

1. Power Automate scheduled flow.
2. Azure Function

now using Power Automate might not be the best approach, as it will be slow and the service account running the power automate flow can only do 40 K requests per 24 hours. and we have other 10 flows running under the same service account. so is using Azure Function the approach to go?

Answer 1

@john john Pter

Welcome to the Microsoft Q&A Community.

Given the constraints and requirements you've outlined, using Azure Functions could indeed be a more scalable and efficient approach compared to Power Automate. Here’s why:

Why Azure Functions?

1. Scalability: Azure Functions can handle a large number of concurrent requests, making it suitable for your 22K daily API requests.
2. Cost-Effectiveness: You pay only for the actual compute resources used during execution, potentially reducing costs.
3. Flexibility: You can write custom code to handle your specific logic, including checking contract numbers, updating statuses, and creating new records.
4. Performance: Azure Functions can be more responsive and faster for the type of operations you described compared to Power Automate flows.

Implementation Steps:

1. Set Up Azure Function: Create an Azure Function App in the Azure portal.
2. API Integration: Write the code to call the external API and process the responses.
3. Database Operations:
   • Check if the item exists using the Contract Number field.
   • If it exists, compare the status and update if different.
   • If it doesn't exist, insert the new record.
4. Scheduling: Use Azure Logic Apps or Azure Scheduler to trigger the Azure Function on a daily basis.

Considerations:

• Error Handling: Implement robust error handling and logging to ensure smooth operation and easier troubleshooting.
• Throttling and Rate Limits: Be mindful of the API rate limits and implement retries with exponential backoff if needed.

You can start small and gradually scale up your Azure Function as needed. I hope these helps. Let me know if you have any further questions or need additional assistance.

Also if these answers your query, do click the "Upvote" and click "Accept the answer" of which might be beneficial to other community members reading this thread.