0

I just want to know which of this two structures is the best for better performance:

  • Full HABTM (many to many), but with a very big join table (so only one association for query)
  • Or, HABTM + 1 hasOne (which reduce significantly the join table rows (equal to number of main entity, approximatively 50 000rows) ? But this method forced me to do a query with 2 associations.

So to sum up. Should I use a query with a single association but a big join table (120 000rows) or use a query for 2 associations but with a more lightweight join table ?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user3540430
  • 41
  • 1
  • 6
  • Try http://dba.stackexchange.com/help/on-topic or http://programmers.stackexchange.com/help/on-topic. I believe the main factor in the performance is the database architecture, the framework/language used being secondary. – pyb Jan 01 '16 at 00:28
  • 1
    Try both and measure. Only way to know. – Lightness Races in Orbit Jan 01 '16 at 00:30
  • Thanks for your advices. – user3540430 Jan 01 '16 at 00:35
  • As already suggested, the DBMS will have major impact on the performance for this type of problem. For n:m I would usually recommend Object Databases, they are much better for this kind of relations and do not need separate join-tables. They basically allow using non-first normal form (N1NF) by allowing (ideally indexable) direct references, without needing a 'join'. – TilmannZ Jan 02 '16 at 17:54

1 Answers1

0

After some reflexions, I think I will use a single join. I have just seen some benchmarks that shows single join will be more efficient than multiple queries even if the join table contain lot of rows. It's also less development effort for my application code.

I remain at your listening for any advice. Thanks

user3540430
  • 41
  • 1
  • 6
  • It's ok to accept your own answer. It helps people find questions without accepted answers. Would a better answer come up, you would be able to accept it later on. – pyb Jan 01 '16 at 00:45