Kbase 21461: How Can I Check on the Efficiency of My 4GL Queries?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
09/12/2005 |
|
Status: Unverified
GOAL:
How Can I Check on the efficiency of My 4GL Queries?
GOAL:
Is my query efficient?
GOAL:
Is a query using efficiently indexes?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
CAUSE:
The most common single cause of slow applications is inefficient queries. While most of the queries in your 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. If your application appears slow at some points, or if it slows significantly when more users are added, you should examine the efficiency of your queries.
FIX:
How do you decide what constitutes an excessive read rate? We are not talking about reading two records where one would do, since individual record reads are processed very quickly by Progress. If you see 100 or 1000 or 100,000 reads, however, where you know you should only be reading a few records, then you have found a problem. By the same token, if you consistently see the same or almost the same number of reads from a small table, check to see how many records that table has; it could be that the application is doing a full table scan each time. Yet another warning sign comes when you find you are reading more records from a table 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, you might enter an entire order, or update a customer record. At the same time, run the program (below) while you run your application module to see if there are any excessive read levels in your module. The program recview.p below will display the number of records read from the most heavily used tables since its last iteration. If when you run it, you see more records read than you expected, then re-run your module in smaller and smaller segments. In the monitor program, press space bar after each segment to refresh the record read counts. Eventually you will isolate the offending code. Once you have identified a query that is doing a larger number of reads than is needed, check to see if it is using indexes properly.
The purpose of this procedure is to display the number of records accessed in each table. Press the space bar to redisplay each time segment.
NOTE: Before running, enable the database with VSTs and use the -tablebase/-tablerangesize startup parameters. Those Startup parameters will enable the VSTs _tablestat and _idxstat which are disabled by default in Version 9.
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.
.