Best way to rank all the columns in a table and store those ranks in another table

Question

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.

Thanks!

Answer 1

You would need an index on each column in order to avoid a sort of every row and column during the ranking process. That would of course introduce significant overhead as scores are updated continuously. Probably not an option unless you have a high-end hardware configuration.

The ranking processes could be offloaded onto a read-only copy maintained via log shipping on a different box. That would avoid concurrency issues with hitting the live database system of record and provide more server resources for both the intensive batch process and live database. If you are running Enterprise Edition, you could use a database snapshot on the same box as the source if your hardware is sufficiently sized.

You mention truncate and insert. Just want to mention that you could also use SWITCH such that you truncate and insert into a staging table. Once the staging load is completed, truncate Table_Ranks and then SWITCH from the staging table into Table_Ranks in a single transaction. That way, Table_Ranks would never be empty.