Progress-4GL - What is the fastest way to summarize tables? (Aggregate Functions: Count, Sum etc.) - OpenEdge 10.2A

We are using OpenEdge 10.2A, and generating summary reports using progress procedures. We want to decrease the production time of the reports.

Since using Accumulate and Accum functions are not really faster than defining variables to get summarized values, and readibility of them is much worse, we don't really use them.

We have tested our data using SQL commands using ODBC connection and results are much faster than using procedures.

Let me give you an example. We run the below procedure:

DEFINE VARIABLE i AS INTEGER NO-UNDO.

ETIME(TRUE).
FOR EACH orderline FIELDS(ordernum) NO-LOCK:
    ASSIGN i = i + 1.
END.
MESSAGE "Count = " (i - 1) SKIP "Time = " ETIME VIEW-AS ALERT-BOX.

The result is:

Count= 330805
Time= 1891

When we run equivalent SQL query:

SELECT count(ordernum) from pub.orderline

The execution time is 141.

In short, when we compare two results; sql time is more than 13 times faster then procedure time.

This is just an example. We can do the same test with other aggregate functions and time ratio does not change much.

And my question has two parts;

1-) Is it possible to get aggregate values using procedures as fast as using sql queries?

2-) Is there any other method to get summarized values faster other than using real time SQL queries?

The 4gl and SQL engines use very different approaches to sending the data to the client. By default SQL is much faster. To get similar performance from the 4gl you need to adjust several parameters. I suggest:

-Mm 32600                  # messages size, default 1024, max 32600
-prefetchDelay             # don't send the first record immediately, instead bundle it
-prefetchFactor 100        # try to fill message 100%
-prefetchNumRecs 10000     # if possible pack up to 10,000 records per message, default 16

Prior to 11.6 changing -Mm requires BOTH the client and the server to be changed. Starting with 11.6 only the server needs to be changed.

You need at least OpenEdge 10.2b06 for the -prefetch* parameters.

Although there are caveats (among other things joins will not benefit) these parameters can potentially greatly improve the performance of "NO-LOCK queries". A simple:

FOR EACH table NO-LOCK:
  /* ... */
END.

can be greatly improved by the use of the parameters above.

Use of a FIELDS list can also help a lot because it reduces the amount of data and thus the number of messages that need to be sent. So if you only need some of the fields rather than the whole record you can code something like:

FOR EACH customer FIELDS ( name balance ) NO-LOCK:

or:

FOR EACH customer EXCEPT ( photo ) NO-LOCK:

You are already using FIELDS and your sample query is a simple NO-LOCK so it should benefit substantially from the suggested parameter settings.

We are using OpenEdge 10.2A, and generating summary reports using [​Stackoverflow] [Progress OpenEdge ABL] Progress-4GL - What is the fastest way to summarize tables? (Aggregate Functions: Count, Sum etc.)  2 Progress-4GL - What is the fastest way to summarize tables? (Aggregate Functions: Count, Sum etc.) - OpenEdge 10.2A Oct 9 '18 1 Reading XML file to Temp-table in Progress-4gl Oct 23 '17

Chinese characters are getting to junk characters - Progress4gL - Open edge · Custom Header with Progress-4GL - What is the fastest way to summarize tables? (Aggregate Functions: Count, Sum etc.) - OpenEdge 10.2A · How to run a​  This change in timestamp formatting allows OpenEdge to avoid calling certain UNIX system functions which can cause a process to hang or not properly terminate if called from within the Signal Handler code. g. Doc PSC00309122 : Documentation corrections for ABL API for Multi-tenant and Table Partition Management ===== In "OpenEdge Development

Some additional suggestions:

It is possible to write your example as

DEFINE VARIABLE i AS INTEGER NO-UNDO.

ETIME(TRUE).
select count(*) into i from orderline.
MESSAGE "Count = " (i - 1) SKIP "Time = " ETIME VIEW-AS ALERT-BOX.

which should yield a moderate performance increase. (This is not using an ODBC connection. You can use a subset of SQL in plain 4GL procedures. It is debatable if this can be considered good style.)

There should be a significant performance increase by accessing the database through shared memory instead of TCP/IP, if you are running the code on the server (which you do) and you are not already doing so (which you didn't specify).

These materials and all Progress® software products are copyrighted and all rights are 185 OpenEdge SQL Elements and Statements in Backus Naur Form . ABL table attributes used in OpenEdge SQL statements . The aggregate functions are: • AVG • COUNT • MAX • MIN • SUM ABS Scalar functions Scalar  Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Create a function to search by 'county_served' so you can use it for each This tutorial shows you, the fastest way to search in a multidimensional array. This is for a lesson scheduling app. There is a lessons table (id, start time, end time, etc) which will link via junction tables (via IDs) to a students table (id, first name, last name, etc) and a teachers table (id, first name, last name, etc). These are all many to many relationships. Lessons

vowel or a group of character; 3660 | Progress-4GL - What is the fastest way to summarize tables? (Aggregate Functions: Count, Sum etc.) - OpenEdge 10.2A. 11006 - Fast Track Report Writer "hangs" on 11849 - An alternative way to speed up data dump and 13294 - Summary of PTF needed by OS/400 level and

Progress-4GL - What is the fastest way to summarize tables? (Aggregate Functions: Count, Sum etc.) - OpenEdge 10.2A · how to use aggregate in drupal 8 for  Progress Database Administration Guide and Reference Part II Administration Chapter 4, “Creating and Deleting Databases” Describes how to create and delete Progress databases. Chapter 5, “Starting Up and Shutting Down” Describes the commands required to start up and shut down a Progress database. Chapter 6, “Backup and Recovery

Comments
  • Our progam runs on more than 400 different servers and we have more than 900 different reports. There are customers having millions of records whereas some of our customers have only thousands of records on the same table. Some have 100 users, some have only 1 user. Some have high capacity hardware some have less. But it is the same in all cases for all summary queries we tested; progress procedures are slow compare to equivalent sql queries running on the same server. I can give complex queries as example in here as well but it is same: sql queries runs 5 times or more faster.
  • Our progress startup parameters might not be right as you mentioned. But I guess the performance of sql queries also affected by it. Our startup parameters as follows: Maximum clients: 201 Maximum servers: 4 Maximum clients per server: 0 Lock table size: 1000000 entries Database buffers: 3000 (24000 kb) Excess shared memory: 60 kb APW queue check time:100 milliseconds APW scan time: 1 seconds APW buffers to scan: 5 APW max writes / scan: 25 Spinlock tries before timeout: 50000 Before-image buffers: 25 (200 kb) After-image buffers:25(200 kb) Max number of JTA transactions: 100
  • Often the reactions are the opposite: odbc is slow and ABL is fast so perhaps its an issue with database brokers. I doubt that anybody will be able to help without more information.
  • Thanks for your quick reply. One of two main problems we have with progress is that it is slow and I'd be happy to give any information needed. Here is how we start our database: _mprosrv.exe c:\db\myDb -N TCP -H SERVER -S 12345 -E -n 200 -Mi 200 -L 1000000 -Mxs 60 -spin 50000 -bibufs 25 -minport 22000 -maxport 22500 ------- if there is any other information that can help to solve the problem I'm ready to give.
  • This sort of open-ended discussion would be more suited on a forum like community.progress.com/?Redirected=true
  • When I start the db with parameters: -L 10000,-B 100000,-bibufs 25, -Mxs 60, -Ma 10, -spin 50000, -Mi 5, -Mn 20, -n 220; with TCP/IP connection; FOR EACH code runs in 1130 ms, and your code runs in 1207 ms. When I try to connect using shared memory it raises error message "Unable to attach shared memory, sharedmem.c.mydb.db.0, error 0 (1720)." If I decrease -B 100000 to -B 10000; Timings with TCP/IP connection are 1896 for FOR EACH and 2143 for your code. Timings with shared memory are 1356 for FOR EACH and 1269 with your code.
  • Sorry to hear that the timing gets worse with select count(*) for TCP/IP' connection. I tested it before posting and it reduced the run time by about a third, but that was using shared memory.
  • Decreasing -B is probably not worth it. You could try to decrease -B but not as much. -B 100000 should be achievable though, you might need to change other parameters. See knowledgebase.progress.com/articles/Article/P129034 for details. The more elegant solution would be to switch to 64-bit Progress, but that would probably require additional licenses.
  • I think switching to 64 bit and a newer version of openedge will handle much of my problems but it is not an option right now. Thanks for your help. I'll check the article.