Performance of updates to to-many relationships
Performance of updates to to-many relationships
- Subject: Performance of updates to to-many relationships
- From: Ben Rister <email@hidden>
- Date: Fri, 28 Apr 2006 21:19:36 -0400
Hello list,
I'm running into some pretty severe performance problems that I've
tracked down to the way that to-many relationships seem to be
implemented in an underlying SQL store. Here's the story:
Take a stereotypical Department-Employee relationship. Each
department has a to-many employee relationship, which has an inverse
to-one department relationship. I create a new Employee, and set an
existing Department to be his department. This triggers an automatic
update of the employee relationship in the Department.
The problem is that I have a lot of "Employees" and a pretty good
number of "Departments"--current tests are on the order of tens of
thousands, but the intended result is hundreds of thousands. When
the inverse to-many relationship is being updated to add the new
Employee, CD faults to populate the set, and issues a SQL query that
searches every Employee to see if its department is the Department in
question. Apparently the to-many relationship is not explicitly
stored, but is calculated dynamically based upon its inverse! This
takes a *really* long time--anywhere between .3 to over 1.0 seconds
for this one query--and since this happens every time an Employee is
added, it's making my app's performance unbearably slow. Shark shows
100% of the execution time being spent performing this maintenance on
the relationship.
How can I get around this representation problem? The usual
solutions of batch faulting and whatnot don't buy me anything here,
because it already is issuing only one SQL query to fulfill the
operation, it's just that the way the underlying tables are
structured that's killing performance. Is there some way I can
restructure the model to force a more efficient representation, for
instance?
Thanks in advance for any help...
br
--
Ben Rister
Decimus Software, Inc.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Cocoa-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden