HOWTO: Improve SQL 6.5 Performance for very simple SQL

HOWTO: Improve SQL 6.5 Performance for very simple SQL

Post by CS Lab. Stf C.M. H » Wed, 21 May 1997 04:00:00



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

 
 
 

HOWTO: Improve SQL 6.5 Performance for very simple SQL

Post by Nancy Hid » Thu, 22 May 1997 04:00:00


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


 
 
 

1. SQL 6.5 Performance vs SQL 7.0 Performance

hello,

Has anyone encountered performance problem when porting 6.5 stored procedure
to 7.0?

The performance degrades almost 100%. Something which took 1min to run in
6.5 runs 2min in 7.0.

My procedure has lots of tmp table creation and use of cursors. Are these
the reason for the performance loss? I am really puzzled coz 7.0 is supposed
to outperform 6.5 many times over. If those were the reasons, what is the
alternative to using tmp tables to manipulate data?

Anyone has any clues? This is a big stumbling block to my company upgrading
to SQL 7.0

regards,

2. Scroll Cursor

3. migrating sql databases sever 6.5 from sql server 6.5 to another sql server 6.5

4. What are all the files?

5. HowTo: Prevent people from importing data from SQL 6.5 into Access97

6. Access97,2002 problem

7. HowTo?: Print Table Definition, SQL 6.5 Dev

8. HELP: Connect to Local Database in Form Designer

9. Howto: Query database on another server (SQL 6.5)

10. SQL 6.5 replication Howto?

11. What kind of performance improvement does SQL 2000 has over SQL 6.5

12. performance of SQL 7.0 vs. SQL 6.5

13. SQL 6.5 & SQL 7.0 Replication Performance