slow informix queries in a large database? try this

If you have been experiencing slow sql queries after loading large amounts of data into your informix database(over 100 thousand rows per table) try this sql statement to once again speed up the query as if there are 10 rows in the table: UPDATE statistics; My development effort has put together a program which frequently(every few weeks) deletes all the data in a large database and then reloads it with different data. We noticed that if we have several thousand rows loaded each time that the queries start to become very slow. Executing this statement at the end of our load program fixes this problem and the queries become fast again. Yours, Ted K -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

kolovos_ted@prc.com wrote: > > If you have been experiencing slow sql queries after loading large > amounts of data into your informix database(over 100 thousand rows per table) > try this sql statement to once again speed up the query as if > there are 10 rows in the table: > > UPDATE statistics;[SNIP] That is just the beginning. If that is all you are doing, assuming you are running IDS 7.xx you can be getting even better performance by updating stats HIGH and or MEDIUM or a combination of both. Search the archives of this list at the IIUG WEB site (www.iiug.org) to see other postings and questions with the keywords "UPDATE STATISTICS" in the body r subject. The topic is discussed frequently. There is a lot you can learn about this subject. You may also want to check out the Informix FAQ and the Software Repository while you are there. Art S. Kagel

And, if you have a multiple CPU machine, read up on the environment variable PSORT_NPROCS (documented in the SQL Reference Manual). This variable will speed up the processing of Update Statistics as well as your garden variety Index Creation, Query Order Bys and Group Bys. Take care. Clifton Art S. Kagel wrote in message <368A9DAC.55C1@bloomberg.net>... >kolovos_ted@prc.com wrote: >> >> If you have been experiencing slow sql queries after loading large >> amounts of data into your informix database(over 100 thousand rows per table) >> try this sql statement to once again speed up the query as if >> there are 10 rows in the table: >> >> UPDATE statistics;>[SNIP] > >That is just the beginning. If that is all you are doing, assuming you >are running IDS 7.xx you can be getting even better performance by >updating stats HIGH and or MEDIUM or a combination of both. Search the >archives of this list at the IIUG WEB site (www.iiug.org) to see other >postings and questions with the keywords "UPDATE STATISTICS" in the >body r subject. The topic is discussed frequently. There is a lot you >can learn about this subject. You may also want to check out the >Informix FAQ and the Software Repository while you are there. > >Art S. Kagel