What confuses me in your query is that you select all the rows from TruckLocations. I would believe that the driving table would contain only trucks, not the locations since you're fetching the latest position...
So in order to make it better performing, should the query be something like
select ...
from dbo.Trucs t INNER JOIN dbo.TruckLocations LOC on LOC.TruckID = t.TruckID
WHERE LOC.[timestamp] = (select max(LOC2.[timestamp])
from dbo.TruckLocations LOC2
where LOC2.TruckID = t.TruckID)
If that would be the case then the index you suggest would work just fine. A little more speed could be achieved by doing the following:
- using clustering in the index so that the timestamps are physically ordered. However, note that this increases time to insert and update records
- defining the index as unique, if it's impossible to have two records for a single truck at the same moment of them
- if you fetch only few other columns from TruckLocations table consider adding these columns to the index in order to achieve a covering index, see
Create Indexes with Included Columns[
^]
What comes to creating the query, if you want to avoid a procedure, why not create a view for it, or let it be in plain SQL. What I'm afraid of with LINQ is that the way it actually creates the SQL statement may change over time causing variations in execution plans.
I hope I understood your question correctly and not being completely off-road :)