Click here to Skip to main content
15,921,351 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I am creating a table now which contains truck information overtime,
my question, what I want is two fold:

1. write the query that get the latest record for each truck (I am creating the table right now, I have no date, difficult to test)

2. make sure I create the right index that will make that query fast (I have some doubt that multidimensional index (date and truckID) can use comparison operator successfully)

3. (optionally) how to write that query with EF

What I have tried:

My table looks like that
SQL
create table TruckLocation (
  ID bigint identity (1, 1),
  TruckID int not null,
  timestamp datetime2 not null,
  -- blablabla other data, ex:
  latitude real not null,
  longitude real not null
)

And yes I am also using geometry type, this is not my question.


For the current location, I came up with that query
SQL
select * 
from dbo.TruckLocations LOC
where [timestamp] = (select max([timestamp]) from dbo.TruckLocations where TruckID = LOC.TruckID)

seems to work...

But I want to make sure it works fast!
what about that index? (that is the real question)

SQL
CREATE NONCLUSTERED INDEX [IDX_TRUCKLOCATIONS_TIMESTAMP] ON [dbo].[TruckLocations]
(
	[TruckID] ASC,
	[timestamp] desc
)
GO


Finally, how do I write that query in EF (Entity Framework)?
would be nice to avoid stored procedure....

I came up with that, query analyser seems alright with it....
C#
var query =
    from p in db.TruckLocations
    where p.timestamp == db.TruckLocations.Where(x => x.TruckID == p.TruckID).Max(x => x.timestamp)
    select p
    ;

wondring about doing OrderByDescending(timestamp).Take(1), but query analyser "looks" worst... (not really good at reading query analyser)
Posted
Updated 13-Oct-16 21:55pm
v5

1 solution

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
SQL
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 :)
 
Share this answer
 
Comments
Super Lloyd 14-Oct-16 19:44pm    
the problem is instead of having 2 table (historical locations of all trucks and current location of all trucks) I got only 1 table (historical) and try to get the current location of all trucks by fetching the latest record for each truck.

Yeah! I did make the index unique!
I can't cluster it since I have a primary key (ID) but.. that is an interesting idea!

A view (for current locations) hey? Excellent idea! :)
I wanted to avoid SP and dislike complicated view.. but this one is so meaningful and simple! Yeah! :)
Wendelius 15-Oct-16 2:30am    
Glad if this was helpful. With the driving table I actually didn't mean that you would separate current and historical locations. Only that you would have a lookup table containing one row for each truck and it's primary key (truckid).

What I suspect is that if you would have such table and use it in the query, the database could use it as a lookup table and possibly do an index join to fetch the locations more quickly.

Perhaps worth a try...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900