Many to Many SQL Inefficiency?

[David Sanderson]

Lets say there's a many to many relationship between Accounts and
Roles. If you add a Role to an Account it deletes all of the Roles
from the linking table that's related to the Account and adds them all
back in, including the new Role. Is this the nature of the beast or do
I have something setup wrong?

[Marc Esher]

Nature of the beast.

> --
> You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
> To post to this group, send email to cf-or...@googlegroups.com.
> To unsubscribe from this group, send email to cf-orm-dev+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/cf-orm-dev?hl=en.
>
>

[Brian Kotek]

Otherwise Hibernate would have to query for the existing records and do two sets of nested loops to figure out what is new and insert it, and figure out what was removed and delete it. Bulk deleting them and inserting them is going to be faster most of the time.

On Tue, Jul 19, 2011 at 5:56 PM, David Sanderson <code...@gmail.com> wrote:

[David Sanderson]

It would have to figure out what is new and insert it? Why can't it
just insert the new record? If I wanted to add a Role and also delete
a Role to an Account in the same session, all it would have to do is
delete where RoleID = x and insert a new RoleID along with the
AccountID. I guess I don't follow.

On Jul 19, 7:03 pm, Brian Kotek <brian...@gmail.com> wrote:
> Otherwise Hibernate would have to query for the existing records and do two
> sets of nested loops to figure out what is new and insert it, and figure out
> what was removed and delete it. Bulk deleting them and inserting them is
> going to be faster most of the time.
>

[Steve Onnis]

It has to work out what to remove and what to add. To do this it would have
to either loop over the update relationships checking if it is in there or
not and remove it if not and add it if needed.

It is much easier to just remove all the relationships and then add them
back in again. If there were 3 roles to be removed, would you expect it to
remove them one by one? Or just remove them all in one delete statement?

If it wasn’t the ORM doing the work, think about how you would do it
manually. It is certainly how i do it.

[Brian Kotek]

Yeah I don't think you're following.

Say the database starts off with link records like this:

FK1 FK2

11 41

8 15

22 30

101 7

Now you modify this and go to save it with these values

FK1 FK2

11 41

211 90

22 30

408 267

You can't just insert everything in the new set...some of it exists already. Conversely, some of the old values aren't there any more; they have to be deleted. Which means you have to loop over both sets twice to figure out what needs to be inserted and what needs to be deleted. Far easier to just bulk delete it and re-insert it.

[Joe Rinehart]

+ 1 to "Nature of the Beast."

This is one of the reasons that ManyToMany is evil and should be avoided in favor of a relationship class (AccountRole) and two OneToMany's (Account OneToMany AccountRoleRelationship, and Role OneToMany AccountRoleRelationship).  The primary other reason is that the relationship itself often becomes a model as new requirements are discovered.  For example, what if you needed to track the date an account was added to a role?  You'd be up the creek without an AccountRoleRelationship CFC.

There's a much longer section on this somewhere in Java Persistence with Hibernate, but it boils down to "Yeah, we support ManyToMany, but don't use it, please."

-Joe

[Joe Rinehart]

Found the Hibernate team's summary of it in best practices:

Do not use exotic association mappings:

Practical test cases for real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, most associations are one-to-many and many-to-one. For this reason, you should proceed cautiously when using any other association style.

(http://docs.jboss.org/hibernate/core/3.3/reference/en/html/best-practices.html)

[David Sanderson]

That's an interesting find Joe. That does sound like a better way to
go, to create the linking table object.

It also makes sense to me now that it bulk deletes, and adds them all
back in. I wonder if it takes advantage of inserting all the records
back in with one INSERT statement that SQL Server 2008 supports now.

On Jul 20, 12:50 pm, Joe Rinehart <joe.rineh...@gmail.com> wrote:
> Found the Hibernate team's summary of it in best practices:
>
> Do not use exotic association mappings:
>
> Practical test cases for real many-to-many associations are rare. Most of
> the time you need additional information stored in the "link table". In this
> case, it is much better to use two one-to-many associations to an
> intermediate link class. In fact, most associations are one-to-many and
> many-to-one. For this reason, you should proceed cautiously when using any
> other association style.

> (http://docs.jboss.org/hibernate/core/3.3/reference/en/html/best-pract...
> )

>
> On Wed, Jul 20, 2011 at 12:44 PM, Joe Rinehart <joe.rineh...@gmail.com>wrote:
>
>
>
>
>
>
>
> > + 1 to "Nature of the Beast."
>
> > This is one of the reasons that ManyToMany is evil and should be avoided in
> > favor of a relationship class (AccountRole) and two OneToMany's (Account
> > OneToMany AccountRoleRelationship, and Role
> > OneToMany AccountRoleRelationship).  The primary other reason is that the
> > relationship itself often becomes a model as new requirements are
> > discovered.  For example, what if you needed to track the date an account
> > was added to a role?  You'd be up the creek without an
> > AccountRoleRelationship CFC.
>
> > There's a much longer section on this somewhere in Java Persistence with
> > Hibernate, but it boils down to "Yeah, we support ManyToMany, but don't use
> > it, please."
>
> > -Joe
>

[Brian Kotek]

Probably not, but the overhead of inserting 2 simple ID columns into a table is virtually 0.

In almost all situations you can trust Hibernate. There's nothing to see under this curtain. ;-)