使用批量插入和查询优化表上的性能(Optimizing Performance on Table with Bulk Inserts and Queries)

 我们有一个包含以下模式的表： 
 
CREATE TABLE Measurement (
  HubId bigint NOT NULL
  ,DeviceId bigint NOT NULL
  ,Timestamp datetime NOT NULL
  ,Value bigint NOT NULL
)
 
 我们希望将记录导入此表中，每秒大约100,000条记录，这些记录将分成多个并发连接。 我可以通过将表视为堆（即没有索引）并使用SqlBulkCopy来实现此级别的性能。 每秒都有100,000个唯一的HubId和DeviceId组合， Timestamp递增。 Value是累积的。 
 
 我们还要求读取最近的两个（在这种情况下为最后一个和倒数第二个 - 插入始终按顺序）值GROUP BY HubId, DeviceId每秒一次，但仅用于行的子集（用户是感兴趣的是产生最后两个值之间的实时差异。 
 
 此外，出于历史目的，我们需要每15分钟将数据汇总到15分钟。 此数据基于前15分钟切片的最大值和当前15分钟切片的最大值进行聚合...这需要在整个数据集中进行，但可以跨多个连接分割，以实现唯一的HubId和DeviceId组合。 因为Value是累积的，所以这实际上是前一个和当前15分钟切片的最后一个值。 
 
 使用索引批量插入表会导致升级到独占表锁。 此外，我们似乎无法在不升级到独占表锁的情况下执行查询。 
 
 任何人都可以给我一些关于构建这个的最佳方法的指示吗？ 我正在撞墙，试图找出最好的方法...... 
 
 谢谢，迪恩 

We have a table with the following schema:
 
CREATE TABLE Measurement (
  HubId bigint NOT NULL
  ,DeviceId bigint NOT NULL
  ,Timestamp datetime NOT NULL
  ,Value bigint NOT NULL
)
 
We'd like to import records into this table at approximately 100,000 records a second which will be split across multiple concurrent connections. I can achieve this level of performance by treating the table as a heap (i.e. no indexes) and using SqlBulkCopy. Every second there are 100,000 unique HubId and DeviceId combinations with the Timestamp incrementing. Value is cumulative over time.
 
We also have the requirement to read the two most recent (last and second to last in this case - insert is always in order) values GROUP BY HubId, DeviceId once per second but only for a subset of the rows (ones that the user is interested in) to produce a realtime difference between the last two values.
 
In addition we need to aggregate the data once every 15 minutes into 15 minute slices for historical purposes. This data is aggregated based upon the maximum value from the previous 15 minute slice and the maximum from the current 15 minute slice... This needs to occur across the entire dataset but can be split across multiple connections for unique HubId and DeviceId combinations. Because Value is cumulative this is effectively the last value for the previous and current 15 minute slice.
 
Bulk inserting into the table with indexes results in escalation to an exclusive table lock. In addition we can't seem to get the queries to perform without escalating to exclusive table locks.
 
Can anybody give me some pointers as to the best way of structuring this? I'm banging my head against the wall trying to work out the best way to go...
 
Thanks, Dean

原文：https://stackoverflow.com/questions/12267619