Re: "UPDATE STATISTICS" command
John C. Sinues (sinues@prc.prc.mrms.navy.mil) wrote: : Is UPDATE STATISTICS a useful command to run? If so, when is a good : time? each time after a table is updated? Weekly? : : Well lets see.... Yes and No. It is a good thing to do so that your indexes are rebuilt and it will speed up your searches and selects but......... If you are running 5.03.UC1 on an HP platform (don't know about others so I will not slam them) it could result in verrrry slooooow response times. To give the specs: There is a known bug with the release of software that we have from informix: ================================================================================ Tech Info Article #4495 Title: workaround for bug 14829 negative npused in systables Submittee: jmiller Sub. Date: 11/17/1993 Bug Number: 14829 Schedule Fix: 5.02.UC1 SUCCESSIVE INSERTS AND DELETES CAN LEAD TO NEGATIVE PN_NPDATA ================================================================================ What this technical garble means is that whenever we did a mass update on the database - like creating a new recipe - it scrambled the indexes on the database which makes it very slow to find data. Informix provided us with a workaround for this bug. It is a procedure, update_stats(), that we can run to reset the database system tables so that the indexes can be restructured correctly. To give you an idea of the difference this can make, here are some run times that we collected for a sample query: Initial Query Next or Previous row Notes ================================================================================ 75 59 After update statistics ran. Buffers = 2000 41 39 After indexes were dropped and recreated. 41 41 Using LCAC config parameters 2 !! 0 REALLY!! After running the update_stats procedure. *NOTE: times are in seconds. *-------------------------------------------------------------* | Saint Humble Servant of the Idiots | *---------------------*---------------------------------------* | Butch Stokes | E-MAIL: stokes@hpspk.hp.com | | Hewlett Packard | HPDESK: Butch Stokes/HP1000 | | P.O. Box 2500 | TELNET: 1-921-3439 | | Spokane, Wa. | ATT: (509) 921-3439 | | 99220-2500 | FAX: (509) 921-3991 | *---------------------*---------------------------------------* | A poorly designed system is what the Germans would call | | "Die Eier-legende Wollmilchsau" an egg-laying woolly milk | | pig. It's what happens when you try to put together many | | different animals. You don't know what you'll end up with. | | I believe this quote belongs to Wim Rolands of HP ... | | | | Das Computenmaschine ist nicht fuer gefinger poken and | | mittengrabben... (Stop *HACK*ing and fall in love....) | | Found on Notes | *-------------------------------------------------------------*