Re: Wierd Table -- Aaargh!

In <330don$e1t@ns.oar.net>, pete@afc.org (Pete Stiglich) wrote: >: Hello, I am having some really strange problems with one table. JUST that >:table. Occasionaly, users will complain saying that the order entry program >: is bogging down. Invariably, it is because of this ONE table. A query that >: normally takes a second will take 10. > >[stuff deleted...] >Thanks to everyone who responded. The overall consensus was that I should >check how often I am updatiing statistics. How often should it be run? >Daily? Hourly? > >Thanks > >Pete That depends on the volume and nature of transactions. Update statistics works by keeping the set of numbers that describe a table in synch with the actual table. If these are out of synch then the engine uses an inefficient method of working with the table. (Imagine making a decision to read every line of a book in search of a quote. If there are only 50 lines in the book that's a good strategy. If there are 50 volumes then the search should probably be conducted another way.) If your application changes the number of rows in the table, say by adding and purging at different intervals, then your statistics will be wrong. You need to update them when they no longer reflect the state of the table. If the application adds to the table every hour, but the purge deletes from the table every month, then the table will grow and shrink according to a hourly/monthly cycle. These cycles will dictate how often to update statistics. You must determine these cycles based on your application. You may use an automated routine to read the status of the tables periodically and use these data to develop a model. OTOH, you could just do it once a week like the rest of us. Verbosely, __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | | Reynolds Metals Co. "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@jabba.rmc.com | |________________________________________________________________|