多维索引问题 [英] Multi dimensional indexing question

问题描述

我正在创建一个包含卡车信息加班的表格，

我的问题，我想要的是两折：



1写下获取每辆卡车最新记录的查询（我现在正在创建表格，我没有日期，很难测试）



2.确保我创建了正确的索引，可以快速查询（我有点怀疑多维索引（日期和卡车ID）可以成功使用比较运算符）



3.（可选） ）如何用EF写这个查询



我尝试过：



我的表看起来像

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

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

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)

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

最后，如何在EF（实体框架）中编写该查询？

可以避免存储过程。 ...



我提出来了，查询分析器似乎还好....

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....

var query =
    from p in db.TruckLocations
    where p.timestamp == db.TruckLocations.Where(x => x.TruckID == p.TruckID).Max(x => x.timestamp)
    select p
    ;

想要做 OrderByDescending（时间戳）.Take（1），但查询分析器看起来最糟糕的......（并不擅长阅读查询分析器）

wondring about doing OrderByDescending(timestamp).Take(1), but query analyser "looks" worst... (not really good at reading query analyser)

推荐答案

在您的查询中让我感到困惑的是您从TruckLocations中选择所有行。我相信驾驶台只包含卡车，而不是地点，因为你拿到了最新的位置...



所以为了让它表现得更好，如果查询类似于

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)

如果情况确实如此，那么您建议的指数就是k很好。通过执行以下操作可以实现更快的速度：

- 在索引中使用群集，以便对时间戳进行物理排序。但请注意，这会增加插入和更新记录的时间

- 将索引定义为唯一，如果在同一时刻无法为单个卡车创建两个记录

- 如果您从TruckLocations表中仅获取其他几列，请考虑将这些列添加到索引以实现覆盖索引，请参阅创建包含列的索引 [ ^ ]



创建查询的方法是什么，如果你想避免一个程序，为什么不为它创建一个视图，或者让它在纯SQL中。我对LINQ的恐惧是它实际创建SQL语句的方式可能会随着时间的推移而改变，导致执行计划的变化。



我希望我理解你的问题正确而不是完全越野：）

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

这篇关于多维索引问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！