|Many to Many SQL Inefficiency?||David Sanderson||7/19/11 2:56 PM|
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?
|Re: [cf-orm-dev] Many to Many SQL Inefficiency?||Marc Esher||7/19/11 3:03 PM|
Nature of the beast.
|Re: [cf-orm-dev] Many to Many SQL Inefficiency?||Brian Kotek||7/19/11 4:03 PM|
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:
|Re: Many to Many SQL Inefficiency?||David Sanderson||7/20/11 6:53 AM|
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.
|RE: [cf-orm-dev] Re: Many to Many SQL Inefficiency?||Steve Onnis||7/20/11 7:24 AM|
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
If it wasn’t the ORM doing the work, think about how you would do it
|Re: [cf-orm-dev] Re: Many to Many SQL Inefficiency?||Brian Kotek||7/20/11 8:24 AM|
Yeah I don't think you're following.
Say the database starts off with link records like this:
Now you modify this and go to save it with these values
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.
|Re: [cf-orm-dev] Many to Many SQL Inefficiency?||Joe Rinehart||7/20/11 9:44 AM|
+ 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."
|Re: [cf-orm-dev] Many to Many SQL Inefficiency?||Joe Rinehart||7/20/11 9:50 AM|
Found the Hibernate team's summary of it in best practices:
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.
|Re: Many to Many SQL Inefficiency?||David Sanderson||7/21/11 6:32 AM|
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.
|Re: [cf-orm-dev] Re: Many to Many SQL Inefficiency?||Brian Kotek||7/21/11 7:29 AM|
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. ;-)