RE: TimeSeries update statistics recommendations [

Good question. Update statistics on a table with a time series column will not play a role in how TimeSeries operations/functions like Apply or AggregateBy behave on the time series elements in the time series column. It may help the optimizer with queries on the base table as with any other table in your database. If you are using the TimeSeries Virtual Tables (ie you have created tables with TSCreateVirtualTab()), you can run update statistics on that table to help the optimizer with queries on the virtual table. The statistics gathered are the estimate of the number of rows and the estimate of the number of pages in the virtual table (if the entire table were materialized). Before TimeSeries.5.00 (which is available in 11.70) the Virtual Table interface would scan the time series data to determine these statistical values and this could be slow with large number of elements in your time series columns. This was addressed 11.70. The TimeSeries Virtual Table Interface will push predicate from the base table into the query so you may see benefits from updating the statistics on the base table. You can see these queries in the EXPLAIN output when querying the Virtual Table. Not matter what mode/resolution/confidence level you use, it calculates the same stats. Its good to hear that you are planning moving to a later release. In 11.70, Timeseries (as well as Spatial) are part of the server and you no longer need to install a separate package. In the latest fixpack of 11.70, we have many performance improvements to insert, delete and query operations. In 12.10, we continued to improve performance on all fronts and added significant new capabilities including ER, Rolling Window, reduced logging and the fast loader support to name a few. In the area of table statistics gathering has dramatically faster for a virtual table. Also, a feature went in to SQL and Timeseries to improve table joins between a TimeSeries Virtual Table and other tables.. In 11.70.xC7 you can take advantage of this by giving a query hint to improve join performance. In 12.10.xC1, the query hint is no longer necessary. In 12.10, SQL now pushes more predicates down to the TimeSeries Virtual Table interface which helps to make queries materialize fewer rows which make many queries execute faster. I hope this helps. Mark Ashworth IBM Informix Extensibility Architect mailto:ashworth@ca.ibm.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LUC VAN GASTEL Sent: April 17, 2013 9:38 AM To: ids@iiug.org Subject: [Bulk] TimeSeries update statistics recommendations [30086] Dear, My customer is running IDS 10.00.FC8 with TimeSeries.4.01.FC7X3. The instance contains a database that is mainly used for an application using the TimeSeries software. My question: does it make sense to run update statistics against the timeseries tables and if so, what would be the preferred mode/resolution/confidence? I'm aware this is an older IDS version, we are planning to upgrade to IDS 11.70 or perhaps even IDS 12.10 in the near future, so any input related to the subject for these new releases would also be welcome Kind regards, Luc van Gastel **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.