索引视图与表上的索引 [英] Indexed View vs Indexes on Table

问题描述

我有下表

EVENT_LOG:

EVENT_ID: pk, int, not null
TYPEID: fk, int, not null
CATEGORYID: fk, int, null
SOURCE: varchar(255), null
DESCRIPTION: varchar(4000), null
CREATED: datetime, null

我们一直在创建报告，发现性能很差.除了聚集索引之外没有任何索引.我们可以创建它们，但是因为该表写入的内容多于读取的内容 - 存在权衡性能问题.对于报告，我倾向于将索引放在每一列上，因为来源 &需要在描述列中搜索子字符串.

We've been creating a report, and found that performance sucks. There aren't any indexes aside from the clustered one. We could create them, but because this table is written to more than it is read from - there's a counter weighing performance concern. For the reporting, I'm inclined to put indexes on every column because the source & description columns need to be searched for substrings.

我们想知道 索引视图(又名实体化视图)将是一个选项，其中索引视图将包含 EVENT_LOG 表中的所有列，但具有适当的在视图上创建的索引.这会让我们获得报告的性能，同时不会影响对 EVENT_LOG 表的写入吗?

We wondered if an indexed view (AKA materialized view) would be an option, where the indexed view would contain all the columns from the EVENT_LOG table but have the appropriate indexes created on the view. Would this get us the performance for reporting, while not impacting writes to the EVENT_LOG table?

推荐答案

索引视图将导致与列索引相同的问题，因为索引视图需要 with schemabinding，这将其绑定到表，不允许您以任何方式、形状或形式更改/更改该表的架构.这包括调整列的大小(例如，从 varchar(50) 到 varchar(255))，更改列的数据类型(例如，从 double> 到 decimal(18,5)) 等.我已经看到它们由于这个事实引起了很多意想不到的麻烦.

An indexed view will cause the same issues as an index on the column, because indexed views require with schemabinding, which tie it to the table directly, disallowing you from changing/altering the schema of that table in any way, shape, or form. This includes resizing a column (e.g.-from varchar(50) to varchar(255)), changing a column's data type (e.g.-from double to decimal(18,5)), etc. I've seen them cause a lot of unexpected headaches due to this fact.

我的建议是设置一个存储过程或 SSIS 包，它们将为您创建一个每小时运行一次的报告表.通过这种方式，您可以索引它所钟爱的地狱，并享受它产生的所有性能优势.我不愿从实时的、正在进行的系统中进行报告.我实际上还没有看到有必要这样做的情况.出于报告目的，一小时前的信息通常绝对足以完成工作.

My suggestion is to set up a stored procedure or SSIS package that will create a reporting table for you that's run every hour or so. This way, you can index the ever-loving hell out of it and enjoy all the performance benefits that it produces. I shy against reporting from a live, in-progress system. I've actually yet to see the case where this is necessary. For reporting purposes, hour-old information is usually absolutely sufficient to get the job done.

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