bridgeburner
Well-Known Member
I've been tasked with looking into some database performance issues with an application that is using Azure SQL.
Basically, this is an ETL application that aggregates "raw" data, which is nearing approximately 800 million records. It calculates various things, mainly what's known as "monthly totals" which consist of various summations; like total "TotalAmountReimbursed" etc. These calculations span date ranges - so it calculates your TotalAmountReimbursed over a period of time. This is done in an Azure Durable Function and the results are persisted to a multi-tenanted SQL Server table with an ordered clustered column store index. This is then exposed through an API and served to the UI. The function/batch job creates around 50 million records in this table if its starting out from scratch - i.e it regenerates ALL the raw data.
This function runs daily on a cron job and has "incremental" runs and this is where the issues come in. Whenever new data is ingested into the "raw" unaggregated data, the function has to recalculate what these monthly totals are based on the given date range. It has some fairly complex code that calculates these ranges, but that isn't important. The important bit is that it currently needs to first delete the existing aggregations for that date range, redo all the calculations and then insert them into the table. I've seen that the delete performance has been absolute trash and seems to be a serious bottleneck for the function. Last week this took 20 odd hours to do with each batch delete of 1 000 rows sometimes taking minutes to complete and it could potentially delete up to 2 million records at a time...
Now I know column store indexes are bad for transactional work mainly due to compression. But I believe it was chosen because it maximises query performance as the UI has number of different query combinations which make designing an effective B-Tree index difficult. But the chickens have kind of come home to roost here.
So my question here is what can we do to make this better? Is this a fundamental architecture issue or are there some quick wins available? One thing I have done already in the immediate term which seems to have improved things is increase the batch deletes from 1 000 rows to 4 000 rows and reduce the concurrency as there seemed to be severe table locking going on. The inserts are also batched, and its performance isn't too bad.
An idea I have is implementing a table switching solution - so on each run, function loads up the data NOT TO BE DELETED into a staging table, the new aggregations get inserted into it too, and when complete, drop the "old" table and rename the staging table. This runs after hours so not concerned about schema locks at this point. I'm just concerned about how long it would take to copy all the NOT TO BE DELETED if the date range isn't wide as that means most of the data would need to be copied over - i.e. just shifting the problem from deletes to inserts..
I know about table partitioning but not sure how to effectively partition the data since the date ranges vary so wildly it could possibly span partitions if you partition on date.
Curious to know if anyone got any experience with this kind of thing?
Basically, this is an ETL application that aggregates "raw" data, which is nearing approximately 800 million records. It calculates various things, mainly what's known as "monthly totals" which consist of various summations; like total "TotalAmountReimbursed" etc. These calculations span date ranges - so it calculates your TotalAmountReimbursed over a period of time. This is done in an Azure Durable Function and the results are persisted to a multi-tenanted SQL Server table with an ordered clustered column store index. This is then exposed through an API and served to the UI. The function/batch job creates around 50 million records in this table if its starting out from scratch - i.e it regenerates ALL the raw data.
This function runs daily on a cron job and has "incremental" runs and this is where the issues come in. Whenever new data is ingested into the "raw" unaggregated data, the function has to recalculate what these monthly totals are based on the given date range. It has some fairly complex code that calculates these ranges, but that isn't important. The important bit is that it currently needs to first delete the existing aggregations for that date range, redo all the calculations and then insert them into the table. I've seen that the delete performance has been absolute trash and seems to be a serious bottleneck for the function. Last week this took 20 odd hours to do with each batch delete of 1 000 rows sometimes taking minutes to complete and it could potentially delete up to 2 million records at a time...
Now I know column store indexes are bad for transactional work mainly due to compression. But I believe it was chosen because it maximises query performance as the UI has number of different query combinations which make designing an effective B-Tree index difficult. But the chickens have kind of come home to roost here.
So my question here is what can we do to make this better? Is this a fundamental architecture issue or are there some quick wins available? One thing I have done already in the immediate term which seems to have improved things is increase the batch deletes from 1 000 rows to 4 000 rows and reduce the concurrency as there seemed to be severe table locking going on. The inserts are also batched, and its performance isn't too bad.
An idea I have is implementing a table switching solution - so on each run, function loads up the data NOT TO BE DELETED into a staging table, the new aggregations get inserted into it too, and when complete, drop the "old" table and rename the staging table. This runs after hours so not concerned about schema locks at this point. I'm just concerned about how long it would take to copy all the NOT TO BE DELETED if the date range isn't wide as that means most of the data would need to be copied over - i.e. just shifting the problem from deletes to inserts..
I know about table partitioning but not sure how to effectively partition the data since the date ranges vary so wildly it could possibly span partitions if you partition on date.
Curious to know if anyone got any experience with this kind of thing?
