Re: advanced EOF: batch fetching relationship: can't fetch certain relationships
Re: advanced EOF: batch fetching relationship: can't fetch certain relationships
- Subject: Re: advanced EOF: batch fetching relationship: can't fetch certain relationships
- From: Colin Clark <email@hidden>
- Date: Wed, 18 Feb 2004 13:04:54 -0500
Hi Jonathan,
We ran into this problem with Oracle and CLOBs. It's simply not
possible to fetch an entity containing CLOB columns when using
DISTINCT, and like you, I've also not been able to figure out why EOF
generates queries with DISTINCT for batch fetches and prefetches.
Perhaps I'm missing something relatively obvious.
Our solution was to stop using CLOBs in a lot of cases where they
weren't really needed. I'm not suggesting this is a real solution for
you, because I'm assuming you actually need the large text columns. In
our case, however, we had a bunch of entities that were modeled with
CLOBs instead of VARCHARs unnecessarily. The users simply weren't
entering enough data to demand a full-on text column.
However, if it's possible, it may make sense to pull your CLOBs (or
'text' columns, in your case) out into separate entities related to
your main entity. If you've got CLOBs containing a large amount of
data, they will already have an adverse affect on fetching performance.
I'm working under the assumption--perhaps incorrect--that users
generally work with large amounts of text data only within some kind of
detail view, where they're working on an object-by-object basis rather
than with a whole batch of objects. If that is the case with your
application, you can take the expense of fetching the individual CLOB
objects only when you actually need the specific detail, and it
shouldn't affect the batch fetches you're trying add. I believe this
approach has been suggested in a number of the Advanced EOF sessions at
WWDC over the years.
An alternative if you can't refactor your model, is as you suggested,
to create your own fetches by hand without DISTINCT instead of batch
fetching the relationship. I don't think it's anything to be too scared
of. ;)
Colin
On Tuesday, February 17, 2004, at 09:08 PM, Jonathan Rochkind wrote:
One idea I have:
Construct a fetch spec myself, and do the fetch myself, WITHOUT using
DISTINCT, then asssemble the results into appropriate snapshots, and
record them directly to the EODatabase myself manually.
Doing such low level stuff always makes me nervous, but do the EOF
gurus think something like that will work? Is a good idea?
In investigating the feasibility of this: it appears that for a
flattened many-to-many, a snapshot is only recorded for the actual
flattened relationship---not for the intermediary (non-class-property)
relationship. Which I guess makes sense. Can someone confirm this is
true, and I don't need to worry about storing snapshots for the
intermediary (not class property) relationship?
--Jonathan
At 7:57 PM -0600 2/17/04, Jonathan Rochkind wrote:
Okay, more fun with batchFetchRelationship, trying to optimize some
code. [I have a little bit more insight on the last question after
experimenting; this is a new one].
I have a bunch of objects (call em Departments), with a to-many
relationship (call it toEmployees). (It's actually a flattened
many-to-many, but I don't think that effects the below description).
EOF, in batch fetching a to-many relationship, whether through a
numerical setting in EOModeler, or whether through
EODatabaseContext.batchFetchRelationship.... in either case, it
insists on using a "SELECT DISTINCT" query on the destination entity.
Alas, my database, SQL Server 7, won't allow a "SELECT DISTINCT" on a
table that includes columns of types 'text'. And the Employee table
does.
Prefetching relationship key paths ALSO uses a "SELECT DISTINCT", so
won't work with this relationship either. Whether batch
fetching/faulting, whether prefetching----I can't do it, I get an
exception from my db "Can't select distinct on a table with a 'text'
type column ".
Argh. So... what the heck can I do to efficiently fetch these to-many
arrays? I've got a list of potentially dozens (or even 100 or 200)
Departments, and I'm going to have to display them with their
employees. I don't want a seperate trip to the db to resolve each
Departments toEmployees relationship. Can anyone think of any
alternatives at all, anything I can do to solve this problem?
--Jonathan
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.
---
Colin Clark
Dynamic Web/Database Developer
Resource Centre for Academic Technology,
University of Toronto
(416) 946-7592 / email@hidden
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.