UPDATE TABLE STATISTICS AND INDEX STATISTICS AND COLUMN STATISTICS

Jeevan

New Member
Hi,

what is UPDATE TABLE STATISTICS , INDEX STATISTICS AND COLUMN STATISTICS. ? what is the use of these statistics?

thanks,
Jeeva
 

Cringer

ProgressTalk.com Moderator
Staff member
Someone may be along in a while to explain further but it's essentially a way of improving the performance of SQL queries on your Progress database.
Have you looked in the documentation?
 

RealHeavyDude

Well-Known Member
The are only useful if the database is accessed via the SQL engine. They are not relevant if the database is only accessed by ABL clients. These statistics are used by the SLQ query optimizer. If you need decent SQL performance you should perform update statistics on regular basis.

Heavy Regards, RealHeavyDude.
 
I am pretty new to Progress, but from my Oracle perspective - database engines usually come with a so called SQL Optimizer. Many databases have them, Oracle, Sql-Server.
In order for querries to perform efficently the database engine/optimizer analyzes your query and looks up database statistics - a set of system views with information gathered
on all your tables. Those could be - number of records in your tables, indexed columns, type of colums, etc. etc. Then the RDBMS optimizer will chose the best
search path or most effiecient path to search for your data. Some databases like SQL-Server run statistics every time you run a query in the background.
So it's pretty much a built in automated task that you don't have to automate. Oracle uses 2 approaches. You either run statistics manually using SQL scripts and built in packages or
you purchase a license to use the Automatic Workload Repository and automate the task. However based on this comment:
They are not relevant if the database is only accessed by ABL clients.
it seems that statistics only apply to pure SQL type querries.
It seems here is the answer:
The ABL, on the other hand, uses a rule based optimizer.
Progress KB - Is there an equivalent of OpenEdge SQL Engine query optimizer for ABL?
A rule based optimizer anaylizes your query based on FROM/AND/WHERE clauses, so you need to understand how the optimizer works and how to write querries efficiently.
A cost based optimizer uses table and index statistics to decide the most efficient route to your data.
Progress KB - SQL Optimization for performance in SQL-89

Normally if you need to gather statistics you need to run a job once daily, best during off-peak times.

I have a new system in place done by a third party and they are experiencing some performance issues. Statistics is the first place to look at in some cases.
From my 20 year experience as an Oracle/SQL admin 90% of third party companies fail to configure jobs or treat gathering statistics as an unnecessary evil
and leave the job to any local DBA's that have to take over those tasks.

As I said, I am new to Progress, but the concepts behind statistics seems to be shared among RDBMS poducts, so I hope I gave some more
insight as to that this thing is.

Regards,
Ryszard
 
Last edited:
Top