Apple

On Mar 31, 2009, at 8:16 AM, Jeff Schmitz wrote:

Thanks David,
Yes, from reading the Apple docs and wiki and prior discussions, I'm using pre-fetching pretty extensively, and have things pretty well tuned in that regard I think. Some of my pre-fetches are quite large (up to 6-7 seconds under good circumstances) but they do help bring down the overall processing time considerably. What's interesting is that under load, the prefetches themselves start to take longer and longer until they start taking over 10 times longer to complete. I would have thought during the pre-fetch the DB would be locked, so I was surprised to see this, but then I'm no DB expert by a long shot.

I believe that this is going to depend on how the plugin for your DB is written. I don't believe that normal reads are going to lock the table. I know the SQL Server plugin does for the optimistic locking check prior to updates and deletes.

Anyway, here's the prefetch code used by the background task:

    ERXFetchSpecification poolFetch = new ERXFetchSpecification(_Pool.ENTITY_NAME, poolQual, null);

   NSArray <String> keyPaths = new NSArray(new String[] {Pool.ENTRIES_KEY ,
Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY,
Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY + "." + ComboTeam.TEAM_POPUPS_KEY,
Pool.ENTRIES_KEY + "." + Entry.ENTRY_SCORE_KEY,
Pool.ENTRIES_KEY + "." + Entry.ENTRY_ADMIN_KEY,
Pool.ENTRIES_KEY + "." + Entry.PLACE_INFOS_KEY});

   poolFetch.setPrefetchingRelationshipKeyPaths(keyPaths);
poolFetch.setRefreshesRefetchedObjects(false);
   Pool pool = (Pool) ec.objectsWithFetchSpecification(poolFetch).lastObject();

A typical fetch might involve the following number of rows:
1 Pool---100>>Entry----63>>ComboTeams----2>>TeamPopups
   Entry----1>EntryScore
   Entry----1>EntryAdmin
   Entry----1>PlaceInfos

Note that this is run on a brand new EO and OBS stack, so I set refreshesRefetchedObjects to false. One other phenomenon I've noticed is that running two instances of my app also tends to kill performance. I do have -WOAllowsConcurrentRequestHandling YES set in the javamonitor config.

It really sounds like the bottleneck is the DB itself and not in Java / EOF. Which brings us to what Andrew Lindesay calls "scaling opportunities at the database end".

Did you turn on the SQL Logging?

Dave

References:
	>RE: How does concurrency control work in WO? (From: "Ren, Kevin" <email@hidden>)
	>Re: How does concurrency control work in WO? (From: Guido Neitzer <email@hidden>)
	>Re: How does concurrency control work in WO? (From: Jeff Schmitz <email@hidden>)
	>Re: How does concurrency control work in WO? (From: David Avendasora <email@hidden>)
	>Re: How does concurrency control work in WO? (From: Jeff Schmitz <email@hidden>)