Increase SQL Query Performance (MAX date)

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...