Re: Large set-based UPDATE

Hi Howard, thanks for taking the time to respond. Comments embedded below.

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<pan.2003.02.12.16.57.49.61549_at_yahoo.com.au>...
> On Wed, 12 Feb 2003 04:02:13 +0000, Paul wrote:
>
> In which case: how often does the bulk update happen?

Every night.

> Why can't it be
> shunted off into the midnight hours so it doesn't upset your web users?

It runs in the early hours and processing creeps into 9am-10am. Users are accessing the site at all times.

> What's the (logical) relationship between the bulky updates and the
> web-based ones? If I update a row via the web, will your bulky update do
> things to my row that I might regret?

No. The web-based client updates different columns to the bulk update.

> No, it's a horrible way to go. You commit when it makes logical sense to
> do so. Anything else, and you risk logically corrupting your data.

The nature of this update statement is that there would be no logical curruption.

> Yes, but in principle don't go there. Commit when it makes logical sense
> to do so, not because you think it might speed things up (which it
> probably won't).

I don't think it will speed things up, but I think it will reduce the amount of rows that are locked at any one point in time.  

> Hence my original question: what can *safely* (and logically) be committed
> in your bulk update? And what relationship is there between what gets
> updated one way compared with what users are doing on the web?

The nature of this update statement is that there would be no logical curruption by committing every 1000 rows. Web-based client updates different columns to bulk update.  

> Strikes me that the real issue here is batch processing and oltp stuff
> co-existing in the same database. The usual recommendation is to shunt the
> batch stuff off into quiet times (if you have any). Or to look at
> structural design issues to see if there's better ways of making them
> co-exist. But introducing commits which are not logically appropriate is
> not the way to go.

I have inherited the current application, and am looking for a short-term solution to the solve locks. Received on Thu Feb 13 2003 - 10:03:56 CST