Multi dimensional indexing question

Question

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 13-Oct-16 14:59pm

Super Lloyd

Updated 13-Oct-16 21:55pm

Rajesh R Subramanian

v5

Add a Solution

1 solution

Add a Solution

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

Wendelius · Accepted Answer · 2016-10-13T21:55:00

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 :)

Multi dimensional indexing question

1 solution

Solution 1

Add your solution here

Preview 0

Multi dimensional indexing question

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us