SQL subselect statement very slow on certain machines

Question

I've got an sql statement where I get a list of all Ids from a table (Machines).

Then need the latest instance of another row in (Events) where the the id's match so have been doing a subselect.

I need to latest instance of quite a few fields that match the id so have these subselects after one another within this single statement so end up with results similar to this...

This works and the results are spot on, it's just becoming very slow as the Events Table has millions of records. The Machine table would have on average 100 records.

Is there a better solution that subselects? Maybe doing inner joins or a stored procedure?

Help appreciated :)

Answer 1

You can use apply. You don't specify how "latest instance" is defined. Let me assume it is based on the time column:

Select a.id, b.*
from TableA a outer apply
     (select top(1) b.Name, b.time, b.weight
      from b
      where b.id = a.id
      order by b.time desc
     ) b;

Both APPLY and the correlated subquery need an ORDER BY to do what you intend.

APPLY is a lot like a correlated query in the FROM clause -- with two convenient enhances. A lateral join -- technically what APPLY does -- can return multiple rows and multiple columns.