Re: 4gl experts ( Mr. Leffler?) please help, how can I speed up this 4gl?
From: "David Murray" <dmurray2@nycap.rr.com> > >A few things that might help: > >I) Make sure you're using page level locking during this batch process. Can't argue about the rest, but why do you say this? I've never seen any empirical proof that page level locking offers any performance benefits... ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
Obnoxio The Clown wrote: > > From: "David Murray" <dmurray2@nycap.rr.com> > > > >A few things that might help: > > > >I) Make sure you're using page level locking during this batch process. > > Can't argue about the rest, but why do you say this? I've never seen any > empirical proof that page level locking offers any performance benefits... I'm with you Obnoxio. There is no demonstrably significant performance benefit from page level locking versus row level locking it just reduces the number of locks needed. If it takes 5 cycles to obtain a lock without conflict, for argument sake, then a million row locks at 500MHZ is 0.001 sec versus say 10,000 page locks at 0.00001 secs. Is that a REAL difference? Even if it really takes 5000 cycles to get each lock due to conflicts and spins the difference is STILL < 1 sec for 1 million rows processed (3secs on my P166)! Of course the down side is increased lock clash conflicts and reduced throughput for multiple updaters due to additional lock waits. Art S. Kagel
In article <37B96EAE.41EA7DA0@bloomberg.net>, kagel@bloomberg.net wrote: > Obnoxio The Clown wrote: -- SNIP -- > > Can't argue about the rest, but why do you say this? I've never > > seen any empirical proof that page level locking offers any > > performance benefits... > > I'm with you Obnoxio. There is no demonstrably significant > performance benefit from page level locking versus row level > locking it just reduces the number of locks needed. If it takes 5 > cycles to obtain a lock without conflict, for argument sake, then > a million row locks at 500MHZ is 0.001 sec versus say 10,000 page > locks at 0.00001 secs. Is that a REAL difference? Even if it > really takes 5000 cycles to get each lock due to conflicts and > spins the difference is STILL < 1 sec for 1 million rows processed > (3secs on my P166)! > > Of course the down side is increased lock clash conflicts and reduced > throughput for multiple updaters due to additional lock waits. > > Art S. Kagel Art & company, Thoughtful analysis, if you actually benchmarked this stuff. Certainly a valid object to measure if someone were to benchmark this stuff. (Hey, I'm talking to Art! Of course we should expect hard measured numbers soon!) I usually recommend that my users try page level locking first. This is not for performance gain - a lock on the page has to be checked for in any case - but for resources. At my site we have many jobs that run huge transactions with easily 50,000 or more locks held before the transaction get committed. Run a few of these at the same time and you run low on lock resources. If you use page level locks, the transaction can get away with far fewer locks and the jobs complete with no "cannot obtain lock" errors. Of course, the trade-off is that two jobs may try to access the same page concurrently and the second one gets the thumbs-down. Well, when that starts to happen we change the strategy - fuggedabout lock resources; just get that ^%$#@! job to finish with no lock contention! We go to row level locking ASAP! Needless to say, most of our tables use row-level locking. (So why did I bother saying it? ;-) -- +-------- Jacob Salomon DBA JSalomon@bn.com -------------------------+ | (In perpetual pursuit of undomesticated semi-aquatic avians) | |The expedient performance of a task with excessive concern to its | | duration-to-completion engenders a virtual certainty of diminished | | benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +---------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.