Question

我有一个表,有数亿人的数据。有一个称为<代码>的领域。活动Id , 即个惯性领域。

我有一些不同的看法,只能用具体活动来恢复数据。 Id

如果我提出质询,我会问:

SELECT TOP 1000 * FROM vw_MyView

退行需要5名未成年人。我需要增加哪些指数? 目前,主轴总表上有索引(载于<条码><0><0>>>>,未在<代码>上分类)。活动,在每种观点的条款中都使用。

我知道,可以将看法指数化——我认为,什么领域是指数化? 难道我就应该管理行“Tuning Wizard”的发动机,看它所说的话吗?

<><>在<>收到反馈后插入>

我的总表格中的所有数据都与以下图谋一致。

LogId (int) PK
EventId (int)
Param1 varchar(255)
Param2 varchar(255)
..
..
..
Param24 varchar(255)

每个活动类型都有不同的参数,因此,主表中通用外地名称。

我对每种类型的活动都持看法,即主议席上的ParamX油田通过该观点获得适当的外地名称。

因此,对一次事件的看法类似。

SELECT LogId, Param1 AS Name, Param2 AS Address1, Param3 AS Address2
WHERE EventId = 10

I tried the query

SELECT TOP 1000 LogId from vw_MyView

and that worked fast. It s the other fields which are slowing it down, which I assume is down to poor indexing?

www.un.org/spanish/ecosoc 2 - 更多信息

Previously the data for each event was stored in a table for each event. This meant that adding new events would require a new table for each one.

我将大量数据输入一个温床,然后将数据移至我的主桌上。批量进口使其迅速进口,但令我关切的是,拥有如此庞大的主食表可能意味着质疑会变得如此缓慢,以致无法使用。

The millions of rows of data is over 10 years or so and so I could probably move the first 8 years of data over to another database for archive purposes so that just the latest 2 years are kept.

因此,我是否继续采用主议制方法,这种办法不需要维修,但可能有许多索引,或者回到每个事件的表格的原始方法?

感谢反馈,真的赞赏

Answer 1

通常的看法包含更多的栏目,不足以回答许多不同的问题,在这些栏目中,直接瞄准相关表格和栏目可能更有利。 (

我可能提出一个指数,表明这应大大帮助你对事件10的看法:

CREATE NONCLUSTERED INDEX Index_ForViewOnEvent10 
ON dbo.MasterTable(LogID)
INCLUDE(Param1, Param2, Param3)
WHERE EventID = 10;

由于该指数涵盖询问,而且由于过滤器大大减少了必须扫描的浏览量,因此应当明显改善。

You may experiment with this with / without the INCLUDE portion. It may be efficient enough to pull those from the clustered index. But I think the above would be the best option.

现在,在这里,不要拖拉——正如我下面解释的那样,你总是不想在一张桌上扔100个指数,以完全回答100个不同的问题——在回答提问和不要把你的书写变成分子之间是平衡的。

我在数据库引擎通勤顾问上讲几句话。

这种担心不是魔鬼。这样做不会受到损害,看不到它所说的话,但你不应仅仅采纳所有建议并加以执行。我建议使用这一工具时有两点:

Make sure you present it with a full workload. If you feed it one query and tell it to create indexes based on what would make that one query perform better, it does so without considering what other queries might not benefit from such indexes, or what other queries might benefit from that index if only it had this or that other column.
Be very aware of duplicates that DTA will offer. It might offer two or three indexes with the same four leading keys and only one difference in a trailing key or INCLUDE column. It also likes to encourage lots of INCLUDE column usage, and you need to be careful about this too.
Adding indexes increases the work performed by DML (insert/update/delete) so you should also keep those portions of your workload in mind.

DBSophic 的工具比指数调整的推力要好得多,它们考虑到整个工作量,不提供多余的指数。 Qure Analyzer是自由的,提供了在Qure Bestizer的一组特征。

Answer 2

我认为你正在混淆http://msdn.microsoft.com/en-us/library/d171921%28v=sql.100%29.aspx” rel=“nofollow”>。意见。指数化的意见可以(必须)有课程索引,但这并不意味着你必须在KQ服务器使用指数时使用索引。如果认为使用指数会更快,则将定期使用基础表格上的任何指数。

因此,从审议文件开始,以征求你的意见,并确定哪些指数可帮助查询工作更快进行。如果你把“四方”的观点摆在桌面上,我们就能够提出一些想法。

另外,您的选定发言并不包含按条款分列的书面材料,因此这是一个非决定性的问题。换言之,即使数据没有变化,你在随后的运行方面可能取得不同结果。

关于 页: 1 页: 1 这取决于该栏数据的核心。

Answer 3

You shouldn t create indexed views on tables that have "several hundred million rows of data". A regular view is just a SQL statement stored on your database , so if you run select * from your_view, sql server just change your_view for its select.

If you want to index a view, you have to persist the data on the database, which means to make a physical copy of the indexed columns on the DB. As you can imagine, it will drastically increase storage size if you have that amount of rows, specially if you are joining that big table with other tables

友情链接