3 ms·

I really like seeing people's thought process as they decide to switch something major, but if I were doing this same task, I think I would have tried more on s

I really like seeing people's thought process as they decide to switch something major, but if I were doing this same task, I think I would have tried more on speeding up the sql solution before scrapping the db. There are several ways you can calculate a rank, and some (e.g. self join) are inherently slow. Maybe the problem is as simple as not having the right clustered index? Or maybe sql dbs can do more optimization if you tell them to use their rank function instead of selecting out a rownum? Postgres 8.4+, sql server 2005+, and Oracle all have rank functions (one of the 'window functions' in the SQL:2003 standard http://en.wikipedia.org/wiki/Window_function_(SQL)#Window_function http://en.wikipedia.org/wiki/Window_function_(SQL)#Window_fu... )

teh 15y ago
The critical functionality in this application seems to be online calculation, i.e. he wants to know a player's exact rank immediately after insert. A sorted set is good for that. In general I agree though. If you relax the consistency requirement slightly you can get away with regenerating the rank table every minute or so. It takes ~500ms on my machine to generate the ranked table for 500k rows on PG9. Unlogged tables in 9.1 would probably make it faster.
- latch 15y ago
  as sbov mentioned in another thread, the pro-RDBMS/SQL argument is that if i only reduce my expectation (admittedly slightly), SQL is a great choice doesn't seem too convincing. It's true that approach would be slightly less complicated (only 1 store, but now you have transformation jobs).