Sql subselect improvement through joins?

How can I improve the follow开发者_JS百科ing 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 ?

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)

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