0

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!

Improve this question
4
  • 2
    Why are you doing this? Do you really need rank() for all columns? What business requirement does this meet?
    – Colin 't Hart
    Mar 21, 2015 at 20:10
  • @Colin'tHart I absolutely do. The columns are video game stats and I rank their Kills, Deaths, Wins, Losses, then 45 other data points. Each row is a player.
    – bladefist
    Mar 21, 2015 at 20:26
  • Do you have appropriate indexing on the main table?
    – Hannah Vernon ♦
    Mar 21, 2015 at 20:34
  • @MaxVernon There is no where statement and I'm doing a rank on pretty much every column except the primary key. What kind of index would help?
    – bladefist
    Mar 21, 2015 at 20:41

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.