We have a stored procedure in Azure SQL database (Pricing tier is premium with 250 DTU) which processes around 1.3 billion records and inserts results in tables which we display in reporting page. To run this stored procedure, it takes around 15 minutes and we have scheduled it weekly as Azure webjobs because we use same database for writing actual user logs.
But now, we want real time reporting max 5 minutes of differences and if I schedule webjobs to execute the stored procedure every 5 minutes then my application will shutdown.
Is there any other approach to achieve real time reporting?
Is there any Azure services available for it?
Can I use azure databricks to execute the stored procedure? Will it help?
Yes, you can use read queries on Premuim replica databases, by adding this to your connection string:
ApplicationIntent=ReadOnly;
https://learn.microsoft.com/en-us/azure/sql-database/sql-database-read-scale-out