Re: Large set-based UPDATE

True ...
But don't you think the real solution would be to break up the tables into columns which are updated by web client and to table where columns
are not updated by the web client.

Kinda like:
Table A : columns (key, c1, c2, c3, c4, c5, c6)

If web client only updates columns c1, c2 and c3 ... then it would be better of to have Table A : columns (key, c1, c2, c3) and Table B : columns (key, c4, c5, c6)

Anurag

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:pan.2003.02.13.18.10.42.569004_at_yahoo.com.au...
> On Thu, 13 Feb 2003 08:03:56 +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.
> >
>
> Hang on. You're only updating 150,000 rows. Why on Earth is it taking so
> long to do?? I don't know what 'early hours' means, but say it's 4.00am.
> You're saying it's taking 5 or 6 hours to update a paltry 150,000 rows??
>
> That's your problem if so. That's about 10 seconds per row!
>
> If you can modify code to insert unnecessary commits, why not modify the
> code so it runs faster (ie, more efficient SQL)?
>
> I'm also now confused why your are doing select for updates at all. If
> no-one else can modify the columns that this batch update is doing; and if
> the batch update can be restarted at will with 'zero' chances of logical
> corruption, then there can't be any possibility of encountering the
> situation where what you thought you were going to update changes by the
> time you get to update it. Therefore, there seems to be no real reason why
> you'd bother to pre-lock the rows in the first place. Just get rid of the
> 'for update' bit, and lock each row as you come across it. That will
> eliminate a massive select before you start to update, and should reduce
> running time accordingly.
>
> Otherwise, the advice still stands. Spurious commits are not a good idea,
> they will probably slow things down even more, and although the locking
> issue might be relieved by such an approach, the load on your database
> will be up, and you increase the risk of 1555s.
>
> Regards
> HJR
>
>
>
Received on Thu Feb 13 2003 - 18:55:08 CST