CODEWITHSUNDEEP
sqlpostgresql
edbras
edbras

Reputation: 4404

Sql subselect improvement through joins?

How can I improve the following query as I think I am missing something and it can be done better :( I have table A and a one-to-many from A to B I want some info from A and the row from B that is linked to A with the highest sequence number. I came up with this:

SELECT A.*, 
 (
  SELECT B.value
  FROM   B 
  WHERE A.idB = B.id
  ORDER BY B.seqNr DESC LIMIT 1
 )
FROM A

Performance is important for me, so is this my best bet ?

Upvotes: 2

Views: 172

Answers (2)

Samuel Neff
Samuel Neff

Reputation: 74909

While bigger, it's possible this will perform better.

SELECT
    A.*,
    B3.value
FROM
    (
        SELECT  B.id, MAX(B.seqNr) MaxSeqNr
        FROM    B
        GROUP BY    B.id
    ) B2
            INNER JOIN
    B B3
            ON
        B2.id = B3.id AND B2.MaxSeqNr = B3.seqNr
            INNER JOIN
    A
            ON
        A.id = B3.id

Also possible it will perform worse. Need some real data to test and see. :-)

Also creating a compound index on B.id, B.seqNr DESC could improve performance of both your original and this alternate query.

If you can hold-off any theoretical purists in your organization you can significantly increase performance by adding a LatestPaymentForA field and using a trigger to keep this field in sync. Whenever a new record is inserted into B, update existing record B where LastPaymentFromA is true and has same id, then insert the new record with LastPaymentFromA to true.

Then you can do something really fast like this:

SELECT
    A.*,
    B.value
FROM
    A
            INNER JOIN
    B
            On
        A.id = B.id
WHERE
    B.LastPaymentFromA = 1

Upvotes: 0

araqnid
araqnid

Reputation: 133482

That's probably your best bet, especially if you will only be visiting a small number of the rows from A and B.

If you are going to be covering all the rows anyway, you can try to address the problem with window aggregations assigning row numbers to rows from B:

SELECT * FROM (
  SELECT A.*, B.*, ROW_NUMBER() OVER(PARTITION BY B.id ORDER BY B.seqNr DESC) AS seqidx
  FROM A JOIN B ON A.idB = B.id
) WHERE seqidx = 1

This will use a lot of temp space though... consider just getting the A and B primary keys out of the aggregate and joining back onto them later (it's not clear from your query what your pkey columns are, since B.id apparently isn't)

Upvotes: 1

Related Questions