Re: Optimizing SE databases.

On 5 Mar 1997 16:53:22 -0500, ngall@stratis-caribbean.com (Edmund Nigel Gall) wrote: >Hi fellow Informixers! > >Does any one have any tips on optimizing SE 5.x databases? From my >reading so far I have: > >- Update statistics (after every major change or weekly) >- Use SCSI disks (done that) >- Drop and rebuild indexes (how is this different from UPDATE STATISTICS > and when is it ideally useful?) This rebuild the index in the correct order and will have less levels (fewer reads) if there have been a lot of deleted from the table >- Unload data, drop table, reload data to solve fragmentation > (How is this different from backing off to tape using cpio/tar and > dropping the entire database and then restoring from tape? Can I > use cpio/tar to solve fragmentation problems? Would I have to re- > build indexes after doing this? Any other methods to solve > fragmentation in UNIX?) >- Tune OS kernel (SCO 3.2.4.2; sar shows that memory may not be the > problem, since at height of poor performance, during an update to > A/R of a minor amount of data, swap size remains small; hence this > may not have much impact on performance) > >I'm trying to optimize a client's system for an A/R batch update they >run at night when no other users are on the system. It currently takes >5 hrs to update a few hundred records (less than 500 in number). This >is unacceptable on a single CPU 166MHz server, with more than enough >RAM! > >Until I determine an upgrade path to OnLine 7.10, could anyone guide me >in my SE optimization efforts? Thanks a lot for your time. > >Best regards, >Nigel >-- >+--------------------------------------------------------------------+ >| Name : Edmund Nigel Gall Address: 9A Stanmore Avenue | >| Title : Account Executive Port of Spain | >| Company : Stratis Caribbean Ltd. Trinidad & Tobago, W.I. | >| | >| Contact Information: | >| Office Personal | >| Voice : (809) 623 6530/1 (809) 662 6611 | >| Fax : (809) 623 8370 (809) 662 6611 | >| Email : ngall@stratis-caribbean.com nigelg@trinidad.net | >+----------------- http://www.stratis-caribbean.com -----------------+ From what you have listed, you might also want to check set explain info. It may not be the DB, but the code (seq scans?). Hope this starts to help. Chris Burton cburton@gte.net