Thanks for taking the time to do this.
If I understand your test correctly, in each of the two cases, you've looped through the 10,000 EOs updating a single one of your 12 attributes. You then did a saveChanges to update the database, resulting in 10,000 individual SQL updates hitting the database, correct?
So, the result was 10,000 round trips to the DB, one for each row updated?
If all of that is correct, and if it's a trivial change to test, would it be possible to test the difference between a SINGLE locked column vs all columns locked?
You've taken the time to do something helpful here and I don't want to reward you by suggesting you need to do a lot more work, but the test I'm suggesting would put the results more in line with the original contention, that locking a single attribute saves a significant amount over locking all the attributes (in your case, 12).
If you would rather not take the tests any further, would you mind sending me a tarball of your current test project? I would be pleased to do it.
If all of the above is correct, your results say that the difference for a single update is approximately:
27.753 sec / 10,000 = 2.7 ms / update
vs.
7.111 / 10,000 = .7 ms / update
So we've achieved a 2 ms savings per update between no checking and checking 12 columns.
If that's the case, my untested (ignorant?) opinion that the difference is insignificant (and is probably less than 10 ms) stands corrected. Much as I hate to be wrong, my yearning for facts over both my ignorant opinions and other WO "old wive's tales" leads me to offer you unequivocal thanks for your efforts.
Regards,
Jerry
On Jan 20, 2006, at 12:54 PM, Mike Schrag wrote:
I haven't done any timings, but I find it hard to believe that this technique will effect any significant savings for most databases. As Art indicates, the only effect of reducing the number of lock columns is to correspondingly reduce the number of columns in the WHERE clause for updates and deletes.
I've always wondered this, too, so I threw together a quick test. I created two identical entities which have 3 ints, 3 booleans, 3 strings, and 3 dates as properties, but one locks all attrs and one locks none. I then inserted 10,000 of each into the db, followed by an update of the same attribute for each with the same change in value.
This was run on the latest version of FrontBase on an Intel iMac with WO 5.3:
Main.main: 10000 with all columns locked = 25753ms
Main.main: 10000 with no columns locked = 7111ms
So in this test, it is 3.6x faster to not lock any columns in this example, with this database, one this hardware. Obviously db benchmarks are never this simple, so take this with a grain of salt.
ms
--
__ Jerry W. Walker,
WebObjects Developer/Instructor for High Performance Industrial Strength Internet Enabled Systems
jerrywwalker@gee-em-aye-eye-ell.com
203 278-4085 office