I have SQL Server 2008 and a table w/ hundreds of thousands of records and about 50 columns.
I run a sql statement like: Select rank() over(order by col1 desc) as Col1Rank, rank() over(order by col2 desc) as Col2Rank, etc, etc
I truncate Table_Ranks and then insert all of those ranks for all the columns for caching purposes. So my select statement had 50 rank() functions. As my table grows, this operation went from taking a few minutes to a hour, and it keeps getting worse, and affecting the overall performance of that table which has a ton of writes/updates coming in at the same time.
My question is, how can I increase the performance of this operation? Should I select all the rows out of my table and store it in a temp table and do the ranks on the temp table? Should I offload this to another server somehow?
My concern is that as I add more databases that will do the similar tasks, it will eventually make my server just unresponsive to user requests.