I have a big table containing multiple indicators per ticker. The table is partitioned by indicator. Each indicator has nearly 50 million rows and currently there are about 30 indicators and they will become more in the future.
In order to update indicators I use switch partitions. Instead of delete I insert the filtered data in new table. Then I make the necessary calculations and insert them in that same table. I rebuild indexes (the same as in the main partitioned table) and finally I switch partition so that the updated indicator is back in the main table.
Rebuilding the index though takes so much time that the saved time from deleting and calculation into the main table is pointless.
I am not a pro in sql I also do not have permissions to modify the database structure in any way. Basically I am looking for solution linked to either faster index rebuild or some advice (sources to read too) on what could be the best architecture of a table in which I insert values, and sometimes use current values to calculate new ones and insert them back. On top of that we talk about table with size 30 * 40 million rows.