[Stackoverflow] [Progress OpenEdge ABL] Increase SQL Query Performance (MAX date)

[ThRnk]

I was searching on how to get the latest occurences based on col1 and col2.

Let's suppose we have the following table (all rows needed are marked with *):

col1                   col2                    col3  
---------------------------------------------------------
002478                 ABC                 2019-08-23    *
002478                 ABC                 2019-05-14    
002588                 CVMG                2019-01-07    *
002588                 IP                  2019-01-31    *
002588                 MMG                 2019-09-04    *
002588                 MMG                 2019-08-28    
002588                 NUSA                2019-11-04    *
002588                 NUSA                2019-04-24    
002746                 IE                  2019-01-15    *
003467                 IE                  2020-01-10    
003467                 IE                  2020-03-13    *

I was able to get the latest occurences based on col1 and col2 with the following select.

SELECT t.col1, 
       t.col2, 
       t.col3
FROM 
       teste t
WHERE t.col3 IN (SELECT max(a.col3) 
                 FROM teste a 
                 WHERE a.col1 = t.col1 AND a.col2 = t.col2)

In this example, it only takes about 10 ~ 7 ms to complete, but on my real database, it takes about 1 hour.

I removed all JOINS that I could and the minimum time I've reached was about 55 minutes.

As I'm using Progress, there's no window function (that I'm aware of) like partition by.


There's another way to solve this problem? The only query I could think was on that "style".

Here's an SQL Fiddle with that example database.

Continue reading...