Re: Some help with Optimization
Re: Some help with Optimization
- Subject: Re: Some help with Optimization
- From: Ken Anderson <email@hidden>
- Date: Fri, 10 Feb 2006 10:18:44 -0500
I wrote a lengthly post regarding this not too long ago...
The bottom line is, I have not met a SQL optimizer that understands
WO's idea of how to lock optimistically. As far as it's concerned,
why would you put 20 fields in a where clause if you already know
that one of them is a unique primary key? So, it will run the query
through many stages of optimization, and often determine that there's
no good index to help it (because it thinks it's looking for a result
set larger than 1) and it will resort to a table scan. Yes, the name
implies exactly what it does...
Your best bet is to define a single column that changes on every
update as your lock - it will increase your performance (sometimes
dramatically based on the DB). I also recommend that it NOT be a
datetime, since resolution issues (seconds, milliseconds,
picoseconds?) can cause problems. I always have a transaction table
with a unique integer key, and create a transaction record for each
save. Every entity in my model has a trans_id, and that becomes a
foreign key to the transaction table (as well as my only locked column).
Ken
On Feb 10, 2006, at 10:10 AM, Arturo Perez wrote:
Chuck Hill wrote:
Hi,
On Feb 9, 2006, at 8:04 PM, Kieran Kelleher wrote:
One thing about EOModeler is that every attribute has optimistic
locking turned on by default. This creates big WHERE clauses
which can slow you down on non-indexed fields. My feeling is that
optimistic locking should be turned on on essential fields only
and then index those since WO _will_ use then in sql WHERE clauses.
I've heard this a number of times, but doubt that it is true. The
WHERE clause also includes the primary key. It seems to me that
any query optimizer worth its name should be able to grab that one
row and very quickly evaluate the rest of the WHERE conditions on it.
Chuck
Mike Schrag recently tested this and it does make a difference.
How important that is is up to the individual. See Mike's email
'Managing EOF Caching'. I forget which database he was testing and
I meant to do something similar for Postgres to see if smarter/
dumber databases made any difference.
-arturo
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40anderhome.com
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:
This email sent to email@hidden