%UpdateStats MetaSQL

Have you ever wondered why a sql step with %UpdateStats is frequently used in the application engines? I was always wondering why it is used until I got a chance to use the same. Let me explain why it is required.

Before explaining the metasql, I need to explain a bit on the database statistics. We know that if we create an index on the table, sql queries will fetch result much faster if we query based on the indexed fields. Now suppose a table is having multiple indexes with fields overlapping between the indexes. In that case how will the system pick the correct index so that the result is fetched faster? For that, database store meta information on the tables known as statistics. This statistics can be updated manually by the db administrator or can be scheduled to run on a fixed interval (widely used).

Let me go back to the original question, why it is required in application engines if this can be scheduled in the db? If you notice the usage of metasql, it will be mostly used for temporary or intermediate tables. Here comes the answer, the temporary or intermediate tables hold data only during the app engine time frame. So at the time when the scheduled db update happens, these tables do not contain any data and hence statistics will not be updated properly.

When should I consider using this? The answer is, in your process, if you have an intermediate table where you store large volume of data for further processing then you should call this metasql in the very next step. The condition to use this is, the step just before the %updatestsats should issue a commit. Otherwise the statistics updated will be wrong again. As I stated above it make sense to use this only for temp tables which hold large volume of data. If your tables hold only few rows of data, there is no point in index selection and hence statistics update. Further it may dampen your performance with an additional commit and database action.

Hope this gives a brief understanding on %updatestats.