How optimize select with max subquery on the same table?

Question

We have many old selects like this:

SELECT
    tm."ID",tm."R_PERSONES",tm."R_DATASOURCE", ,tm."MATCHCODE",
    d.NAME   AS DATASOURCE,
    p.PDID
FROM TABLE_MAPPINGS tm,
     PERSONES p,
     DATASOURCES d,
     (select ID
      from TABLE_MAPPINGS
      where (R_PERSONES, MATCHCODE) 
            in (select 
                  R_PERSONES, MATCHCODE
                from TABLE_MAPPINGS
                where 
                    id in (select max(id) 
                           from TABLE_MAPPINGS 
                           group by MATCHCODE)
               )
     ) tm2                    
WHERE tm.R_PERSONES = p.ID
  AND tm.R_DATASOURCE=d.ID
  and tm2.id = tm.id;

These are large tables, and queries take a long time. How to rebuild them?

Thank you

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (30 years!! ago) and its use is discouraged — marc_s
– marc_s, Commented Nov 17, 2022 at 10:50

MT0 · Accepted Answer · 2022-11-17 13:58:06Z

You can query the table only once using something like (untested as you have not provided a minimal example of your create table statements or sample data):

SELECT *
FROM   (
  SELECT m.*,
         COUNT(CASE WHEN rnk = 1 THEN 1 END)
           OVER (PARTITION BY r_persones, matchcode) AS has_max_id
  FROM   (
    SELECT tm.ID,
           tm.R_PERSONES,
           tm.R_DATASOURCE,
           tm.MATCHCODE,
           d.NAME AS DATASOURCE,
           p.PDID,
           RANK() OVER (PARTITION BY tm.matchcode ORDER BY tm.id DESC) As rnk
    FROM   TABLE_MAPPINGS tm
           INNER JOIN PERSONES p    ON tm.R_PERSONES = p.ID
           INNER JOIN DATASOURCES d ON tm.R_DATASOURCE = d.ID
  ) m
)
WHERE  has_max_id > 0;

First finding the maximum ID using the RANK analytic function and then finding all the relevant r_persones, matchcode pairs using conditional aggregation in a COUNT analytic function.

Note: you want to use the RANK or DENSE_RANK analytic functions to match the maximums as it can match multiple rows per partition; whereas ROW_NUMBER will only ever put a single row per partition first.

Littlefoot · Accepted Answer · 2022-11-17 10:52:05Z

1

You're querying table_mappings 3 times; how about doing it only once?

WITH
   tab_map
   AS
      (SELECT a.id,
              a.r_persones,
              a.matchcode,
              a.datasource,
              ROW_NUMBER ()
                 OVER (PARTITION BY a.matchcode ORDER BY a.id DESC) rn
         FROM table_mappings a)
SELECT tm.id,
       tm.r_persones,
       tm.matchcode,
       d.name AS datasource,
       p.pdid
  FROM tab_map tm
       JOIN persones p ON p.id = tm.r_persones
       JOIN datasources d ON d.id = tm.r_datasource
 WHERE tm.rn = 1

answered Nov 17, 2022 at 10:52

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

1 Comment

MT0 Over a year ago

This is not the same as the OP's query finds all the IDs that have the same R_PERSONES and MATCHCODE as the maximum IDs and that is not what this query does.

Collectives™ on Stack Overflow

How optimize select with max subquery on the same table?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related