Re: prefetching run amok
Re: prefetching run amok
- Subject: Re: prefetching run amok
- From: Chuck Hill <email@hidden>
- Date: Fri, 18 Feb 2005 18:30:03 -0800
Ah, I see. Prefetching is not going to help you there. In fact, its quite
painful. :-) The story fetch is not qualified so the prefetch can't be
either (hence 1=1). Prefetching is done by using the qualifier from the
base fetch on the prefetched table joined to the original table. In the
case of a limited fetch, there is no way to know which rows were fetched
and so this is not used to limit the prefetched rows. Yes, it could use an
IN predicate, but it does not. That is not the implementation.
I suggest that you look at batch faulting (set in the EOModel on a
relationship or entity) or batchFetchRelationship (see www.wodev.com or
Practical WebObjects p. 36).
Chuck
At 09:03 PM 18/02/2005 -0500, Zak Burke wrote:
>On closer inspection, I discovered the problem is only happening
>where I'm using fetchSpec.setFetchLimit(n). Perhaps JDBC (or the SQL
>standard) doesn't have a "limit" function to restrict the number of
>rows returned, and EOF implements this by canceling the query after
>retrieving the rows it wants without passing on that fact to the
>other prefetch queries.
>
>The SQL log is instructive here:
>
>[2005-02-18 17:48:40 EST] <WorkerThread11> evaluateExpression:
><com.webobjects.jdbcadaptor.MySQLPlugIn$MySQLExpression: "SELECT
>t0.authors, t0.channel_id, t0.contact_id, t0.created, t0.created_by,
>t0.date_live, t0.date_published, t0.did, t0.is_featured, t0.name,
>t0.original_source_id, t0.source_id, t0.status_id, t0.summary,
>t0.updated, t0.updated_by, t0.url_id FROM news_document t0 ORDER BY
>t0.date_live DESC">
>[2005-02-18 17:48:45 EST] <WorkerThread11> fetch canceled
>[2005-02-18 17:48:45 EST] <WorkerThread11> 3 row(s) processed
>
>...
>
>[2005-02-18 17:48:47 EST] <WorkerThread11> evaluateExpression:
><com.webobjects.jdbcadaptor.MySQLPlugIn$MySQLExpression:
>"SELECT DISTINCT t0.contact_id, t0.created, t0.created_by,
>t0.url_description, t0.updated, t0.updated_by, t0.url, t0.url_id
>FROM url t0 WHERE (1=1)">
>[2005-02-18 17:48:53 EST] <WorkerThread11> 121805 row(s) processed
>
>I guess I need to find a way to explicitly retrieve the rows I want,
>rather than letting EOF help me.
>
>zak.
>
>
>Zak Burke wrote on 2/18/05 4:48 PM:
>> I am trying to use prefetching but it's not behaving in the way I
>> expect, or the way I want. I have a two tables, STORY and URL, with a
>> to-one relationship defined from STORY to URL. Other tables have to-one
>> relationships with URL as well, so it has a lot (100k+) of rows.
>>
>> When I retrieve a set of stories, I always want the stories' related URL
>> items as well so I use
>> EOFetchSpecification.setPrefetchingRelationshipKeyPaths to add an array
>> containing this field to my fetch.
>>
>> This works, kind of. Instead of individual queries for each URL item,
>> there's only one query (good), but it selects *every* row in URL rather
>> than just those which are related to the STORY rows in the current query
>> (bad). When I run a simple search, I'm retrieving 15 STORY rows and
>> 112,000 URL rows. Yikes.
>>
>> The query EOF generates is this:
>>
>> SELECT DISTINCT t0.contact_id, t0.created,
>> t0.created_by, t0.url_description, t0.updated,
>> t0.updated_by, t0.url, t0.url_id
>> FROM url t0
>> WHERE (1=1)
>>
>> I expected a where clause that ORed together the selected STORY.URL_ID
>> fields.
>>
>> Is there way to restrict prefetching to only grab the rows related to
>> the current query?
>>
>> zak.
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (email@hidden)
>> Help/Unsubscribe/Update your Subscription:
>>
>> This email sent to email@hidden
>>
> _______________________________________________
>Do not post admin requests to the list. They will be ignored.
>Webobjects-dev mailing list (email@hidden)
>Help/Unsubscribe/Update your Subscription:
e.net
>
>This email sent to email@hidden
>
--
Chuck Hill email@hidden
Global Village Consulting Inc. http://www.global-village.net
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden