1

I'm at a stage of my project where I'm thinking about moving to NoSQL for performance reasons. I will definitely have tables with millions of rows, so NoSQL might be useful. But my problem is I'm also doing a lot of calculations with this data and I don't know if that would give me that much more performance if rails still has to do all the calculations.

Here's another question of mine where I describe what data I need and how I process it.

After I realized most of my code in SQL and match one user with 1000 other users it still took

Completed 200 OK in 104871ms (Views: 2146.0ms | ActiveRecord: 93780.5ms)
(on my local machine with sqlite)

And that's not acceptable for me. I would definitely be able to denormalize my tables into one for this to work. But will this give me a performance boost?

I also thought about storing the calculated match percentages IN the database, but that would result in 2.5 billion rows for just 50k users.

Community
  • 1
  • 1
Mexxer
  • 1,699
  • 5
  • 27
  • 40

3 Answers3

3

denormalization definitely boost the performance. add index for the column which is selected in the query. query id instead of string and have mapping (string to id) in the memory. denormalization boosts performance since you will not join with other tables.

Saravanakumar
  • 31
  • 1
  • I already got all indexes set and use only id's for my queries. What if I have to join one table with itself? like in my SQL query? – Mexxer Oct 19 '12 at 10:25
3

As mentioned by Saravanakumar denormalizing calculated data can definitely boost performance and I would recommend it for heavy calculations. And adding indexing is definitely a must when joining tables. Another option is using SQL-views which can boost performance on queries heavy relying on joins. (Take a look at: https://github.com/ryanlitalien/rails_sql_views)

Another point I would make is that moving from SQLite to NoSQL might be a huge step. A smaller step might be to Switch to MySQL which will already boost performance. Even better would be PostgreSQL which is, in my opinion, the best SQL DB around at the moment.

Novae
  • 1,071
  • 8
  • 17
  • Thanks I will look at SQL views. But I think I won't come around NoSQL for my matching system then. If it really boosts performance I have to take eveything I can get since I want to match 10k users on the fly. – Mexxer Oct 19 '12 at 10:38
  • 2
    Are you already using MySQL? Performance wise NoSQL isn't necessarily faster than a relational Database, see this answer for an excellent explanation: http://stackoverflow.com/a/6260887/1232728 – Novae Oct 19 '12 at 10:43
  • switching to MySQL right now to see how it performs after I denormalize my tables. Thank you! – Mexxer Oct 19 '12 at 10:55
0

If I were you I would go with mysql and postgresql and try to better denormalize the tables...

NoSql is not always faster than denormalized, one table queries...

before deciding to switch the db test the exact situation with both alternatives...

Serdar
  • 1,416
  • 2
  • 17
  • 43