Very Computer

Dear SQL 6.5 Experts,

I have a simple SQL which take an hour to run. It is

update largetable
set sn = sn + 1

---

the largetable has about 100,000 records and 'sn' is an integer field.

Is there anyway to improve the performance of this command?
I also found that lot of records were written into syslogs. Can
I execute this command without writing to syslogs?

Thank you very much in advance.

Best Regards,

C.M. Hui

This statement is going to require writing "before" and "after" images to
the log - that's the only way to guarantee recoverability should the
transaction fail.  

There are some things you can check and maybe change which could improve
your performance.

1) Make sure your data devices and log devices are on separate physical
drives.
2) Use some form of drive striping - we use RAID5 to spread our I/O load
and provide fault tolerance.  For optimal performance using mirroring
(RAID1) for the log devices.  
3) If the 'sn' column is a key in any indexes - you might want to drop them
before running this statement, then recreate it afterward.  

Of course options 1 & 2 above will require some extra work and maybe some
capital (more disks, array controllers, etc.) - it just depends on how
important the performance is to you.  Do you have other statements for
which you have unacceptable response times?  

Nancy Hidy
Shell Services Company

Quote:> Dear SQL 6.5 Experts,

> I have a simple SQL which take an hour to run. It is

> update largetable
> set sn = sn + 1

> ---

> the largetable has about 100,000 records and 'sn' is an integer field.

> Is there anyway to improve the performance of this command?
> I also found that lot of records were written into syslogs. Can
> I execute this command without writing to syslogs?

> Thank you very much in advance.

> Best Regards,

> C.M. Hui