对于“仅插入”，适当的SQL Server索引应为“仅插入”。图式 [英] Appropriate SQL server index for "insert only" schema

问题描述

我们正在使用SQLServer 2008，并且对许多表都具有仅插入模式。

我们拥有的表的示例是（和这只是一个示例）：

 创建表Spotquotes 
（
 Id numeric（19,0） identity（1,1）不为null主键集群，
 feeditem_id数字（19,0）不为null，
 value_ask浮点数不为空，
 value_bid浮点数不为空，
 EffectiveDateUTC日期时间不是null默认值getutcdate（）
）
  

我们然后使用此查询查询表

 从Spotquotes中选择* q 
内部联接
（从spotquotes组中选择最新的feeditem_id，max（id）通过feeditem_id）q.id = q2.latest和q.feeditem_id = q2.feeditem_id 
上的q2 
  

实际上，创建上述查询的视图很有意义：

 创建视图的最新报价为
从引号中选择* q 
内部联接
 （从Spotquotes组中按feeditem_id选择最新的feeditem_id，max（id））q.id = q2.latest和q.feeditem_id = q2.feeditem_id 
 上的q2 
 pre> 
 
 
即我们希望将每个feeditem_id的最新插入表中-但我们也能够查询表的状态，就像过去任何时候一样（出于审计考虑，这非常好）。
 
 
 
一种更简单的放置方法。我希望优化以下查询：
 从feedquotes组中选择feeditem_id，max（id）为最新，由feeditem_id 
  
该表通常具有数亿行-但少数feeditem_id实例很可能位于
 
 
 
使用现有的主键和该表中的约1亿行，
 SQLServer 2008需要6秒的时间来执行此查询- 
 
 
 
所以我想知道-如果我们要为此表创建索引以加快查询速度，我们应该创建哪个索引？
 
 
 
可悲的是，管理工作室没有为我们建议一个索引。
 
 
 
编辑：仍然存在问题，但我将单独提出问题。
 
 
 
 更新 
 
 
 
查询速度更快（<10毫秒）可以通过将交叉应用与ID desc的select top * ...顺序结合使用，将其从SQL Server哄骗出来。请参见>说服SQL Server搜索 
 
解决方案
对于此查询：
 从feedquotes组中选择feeditem_id，max（id）为最新by feeditem_id 
  
创建以下非聚集索引
 在Spotquotes上创建索引IX_Spotquotes_feeditem_id（feeditem，id ）
  
 
We are using SQLServer 2008, and have an "insert-only" schema for many tables.


An example of the sort of table that we have is (and this is just one example):
create table spotquotes
(
   Id numeric(19,0) identity(1,1) not null primary key clustered,
   feeditem_id numeric(19,0) not null,
   value_ask float not null,
   value_bid float not null,
   effectiveDateUTC datetime not null default getutcdate()
)
We then query the table with this query
select * from spotquotes q
inner join
    (select feeditem_id, max(id) as latest from spotquotes group by feeditem_id) q2
    on q.id = q2.latest and q.feeditem_id = q2.feeditem_id
In fact, it makes sense to create a view of the above query:
create view latestspotquotes as
select * from spotquotes q
inner join
    (select feeditem_id, max(id) as latest from spotquotes group by feeditem_id) q2
    on q.id = q2.latest and q.feeditem_id = q2.feeditem_id
i.e. we want the "latest" insert into the table for each feeditem_id -but we also have the ability to query the state of the table as it was at any time in the past (which is very nice for audit considerations).

An even simpler way to put it. I wish to optimize the following query:
select feeditem_id, max(id) as latest from spotquotes group by feeditem_id
This table typically has hundreds of millions of rows - but a small number of feeditem_id instances which are very likely to be at the end of the table.

With the existing primary key and about 100 million rows in this table,
SQLServer 2008 takes 6 seconds to execute this query - it's very slow.

So I'm wondering - if we were to create an index for this table to speed up this query, what index should we create?

Sadly, management studio does not suggest an index for us.

EDIT: There are still issues but I will raise as a separate question.

UPDATE

A much faster query (< 10 ms) can be coaxed out of SQL server by using "cross apply" in conjunction with a select top * ... order by Id desc. See Convincing SQL server to search backwards on clustered index for an insert only schema for details.
解决方案
For this query:
select feeditem_id, max(id) as latest from spotquotes group by feeditem_id
create the following nonclustered index
CREATE INDEX IX_Spotquotes_feeditem_id on spotquotes(feeditem, id)


                        
这篇关于对于“仅插入”，适当的SQL Server索引应为“仅插入”。图式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！