The most common single cause of slow applications is inefficient Queries. While most of the queries in an application are probably efficient, if just one query in a hundred doesn't use an index properly, it can significantly affect performance. As a result, the Server can become overloaded by excessive reads that waste system resources and flood the database buffer pool as a result. When an Application appears slow at some points, or if it slows significantly as more users are added, the efficiency of queries should be examined.
What constitutes an excessive read rate?Simply reading two records where one would do would not in itself cause a read performance issue, since individual record reads are processed very quickly by Progress. However, when 100 or 1000 or 100,000 reads are being made when only a few records are being returned by the result set, then this excessive read rate needs closer inspection at the application code level. By the same token, if the same or almost the same number of reads are consistently being read from a small table; it could be that the Application is doing a full table scan each time. Another warning sign is when more records from a table are occurring than actually exist in the table.
A useful technique to zero in on an expensive piece of code is to monitor the running of a large functional piece of the Application. For example, an entire Order, or update a Customer record. At the same time, run the program (below) while the application module is running to gather metrics on the associated read levels.
Example _Tablestat code:
- The purpose of this procedure is to display the number of records accessed in each table from the _tablestat VST.
- Press the space bar to re-display each time segment.
- The number of records read from the most heavily used tables since its last iteration will be displayed.
- When more records are read than expected, the Application module needs to be re-run in smaller and smaller time segments.
- In the monitor program, press space bar after each segment to refresh the record read counts.
- Eventually the read intensive code section will be isolated.
- Once the query that is doing a larger number of reads than is needed is isolated, verify that it is using indexes properly.
- Before running, review the -tablebase/-tablerangesize database startup parameters. Refer to Article Calculating values for -tablerangesize and -indexrangesize
DEFINE VARIABLE totreads AS INTEGER NO-UNDO LABEL "Total reads".
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE lastsampletime AS DECIMAL NO-UNDO FORMAT ">>>,>>9.99" LABEL "Duration(secs)".
DEFINE TEMP-TABLE reads
FIELD table-name AS CHARACTER FORMAT "x(20)" COLUMN-LABEL "Table"
FIELD table-id AS INTEGER
FIELD reads AS INTEGER
FIELD recentreads AS INTEGER FORMAT ">>>,>>>,>>9" COLUMN-LABEL "Reads"
INDEX i1 IS UNIQUE PRIMARY table-id
INDEX i2 recentreads DESCENDING.
FOR EACH _file WHERE _file-num > 0 AND _file-num < 32767 NO-LOCK:
CREATE reads.
ASSIGN reads.table-name = _file-name
reads.table-id = _file-num
reads.reads = 0
reads.recentreads = 0.
END.
PAUSE 0 BEFORE-HIDE.
lastsampletime = ETIME(YES).
DO WHILE TRUE:
FOR EACH _tablestat WHERE _tablestat-id > 0 AND _tablestat-id < 32767 NO-LOCK:
FIND reads WHERE reads.table-id = _tablestat._tablestat-id NO-ERROR.
IF NOT AVAILABLE reads THEN
NEXT.
IF reads.reads <> _tablestat._tablestat-read THEN
ASSIGN reads.recentreads = _tablestat-read - reads.reads
reads.reads = _tablestat-read.
END.
ASSIGN i = 0
totreads = 0
lastsampletime = ETIME(YES) / 1000.
DISPLAY lastsampletime WITH 1 COL FRAME tot.
FOR EACH reads BY recentreads DESCENDING WITH FRAME x:
IF (i = 0 OR i < FRAME x:DOWN) AND reads.recentreads > 0 THEN
DISPLAY reads.table-name reads.recentreads WITH FRAME x.
ASSIGN i = i + 1
totreads = totreads + reads.recentreads. END.
DISPLAY totreads WITH FRAME tot.
IF totreads = 0 THEN
DO:
HIDE FRAME x.
MESSAGE "No reads!".
END.
PAUSE MESSAGE "Ready...".
END.
.> END.
DISPLAY totreads WITH FRAME tot.
IF totreads = 0 THEN
DO:
HIDE FRAME x.
MESSAGE "No reads!".
END.
PAUSE MESSAGE "Ready...".
