We are a small family run business in Miami and currently use a few products in the Azure Cloud. We have a standard Azure SQL database but the performance is poor.
We run a daily report over a transactional table of 50 million rows but this can take HOURS to complete. We increased the DTUs to 100 which made a huge improvement but it is still too slow. We increased this again to 200 DTUs which is even better now but still slower than we would like. Increasing the DTUs to 400 seems to be the magic number as the report returns in a reasonable amount of time.
To get reasonable performance from the server the pricing tier needs to be at 400 DTUs. This costs $588 a month! That is an insane cost for a single hosted database. Even if we scale up the DTUs in a morning and down again in the evening it is still expensive.
The data cannot be optimized any more than it already has.
Would there be a better solution to hosting this data?