Re: prefetching run amok
Re: prefetching run amok
- Subject: Re: prefetching run amok
- From: Zak Burke <email@hidden>
- Date: Sat, 19 Feb 2005 21:47:48 -0500
Chuck Hill wrote on 2/18/05 9:30 PM:
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.
Yup. I uncovered this subtlety in prefetching shortly after solving
the limited-query fetch problem when I went to implement a search
feature against this same set of tables.
The first query used "like '%pattern%'" matching, and since the
prefetch queries use the same qualifier, I was replicating this
expensive like-clause for each group of prefetched EOs. Ouch.
I solved the problem with batchFetchRelationship, as you suggest below.
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 think "where field in (list)" is the one piece of SQL I miss most
in EOF. I wrote a simple method that joins array elements into an
EOOrQualifier which can then be ANDed onto the rest of a query for
the same effect, but I wonder if that's as optimal for the DB.
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).
Thanks for the tips.
The wodev example at
http://wodev.spearway.com/cgi-bin/WebObjects/WODev.woa/1/wa/Main?wosid=false
&wikiPage=EODatabaseContext.batchFetchRelationship
is simple, clear and informative. I tried to figure this out last
night with the WO javadocs, but absent a clear example I didn't get
anywhere.
Thanks to all, especially Chuck and Art, for helping me to
understand what I was doing, and what I wanted to do instead.
On a related note, all these issues came up when I was trying to
optimize search performance. I could run raw queries against my DB
and get results in .5-5 seconds; with EOF helping (I use that term
loosely) results took > 1 minute.
I eventually decided on two fixes: prefetching as discussed above
and limiting the number of rows that a search could return. In the
latter case, I wanted to alert users that their search results were
truncated. It turns out that, like so many WO tasks, this was very
easy but not very obvious.
There are three pieces:
1. call setPromptsAfterFetchLimit(true) on your fetch spec so that
it will broadcast a message when too many results are returned.
2. call setMessageHandler(Object handler) on your editing context to
set handler to listen for the fetch's broadcasts.
3. implement editingContextShouldContinueFetching(...) on the
handler. This method takes an EC, among other params, so you if you
put an NSMutableArray in your EC, you can log the fetch messages to
the list and pull them out later to display on a page.
I have no doubt that there are other faster/better/more efficient
ways of accomplishing these things, but this is what I've been able
to get working so far. If you have other suggestions, please let me
know. I'm always looking to improve.
zak.
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