SQL Server性能和索引视图

Question

使用SQL Server 2008的

（很抱歉，如果这原来是一篇文章，但我想给尽可能多的信息越好。）

我有多个位置其中每个部门都包含多个部门，每个部门都包含多个可以进行零到多次扫描的项目。每次扫描都涉及可能会或可能不会有中断时间的特定操作。每个项目也属于一个特定的包，属于特定的工作，属于特定的项目，属于特定的客户。每个作业都包含一个或多个包含一个或多个项目的包。

         +=============+  +=============+ 
             | Projects | --> | Clients | 
             +=============+  +=============+ 
              ^
               | 
+=============+       +=============+ 
| Locations |       |  Jobs | 
+=============+       +=============+ 
    ^         ^
     |          | 
+=============+  +=============+  +=============+ 
| Departments | <-- | Items | --> | Packages | 
+=============+  +=============+  +=============+ 
         ^
          | 
        +=============+  +=============+ 
        | Scans | --> | Operations | 
        +=============+  +=============+ 

项目表中大约有24,000,000条记录，扫描表中大约有48,000,000条记录。新项目偶尔会全天插入到数据库中，通常数以万计的流行时间。新扫描每小时插入一次，每个插入的数量从几百到几十万不等。

这些表格被严格查询，切片和切块每一个方式。我正在编写非常具体的存储过程，但它变成了维护的噩梦，因为我处在一个没有终点的网站中的一百个存储过程的边缘（例如类似于ScansGetDistinctCountByProjectIDByDepartmentIDGroupedByLocationID，ScansGetDistinctCountByPackageIDByDepartmentIDGroupedByLocationID等）。幸运的是，需求几乎每天都会改变（感觉），每次我必须更改/添加/删除一列，那么......我最终在酒吧。

所以我创建了一个索引视图和一些带有参数的通用存储过程，以确定过滤和分组。不幸的是，性能下降了。 我想第一个问题是，因为选择性能是最重要的，我应该坚持具体的方法，并通过改变基础表来对抗吗？或者，可以做些什么来加速索引视图/通用查询方法？除了缓解维护噩梦之外，我实际上希望索引视图能够提高性能。

下面是生成视图的代码：

CREATE VIEW [ItemScans] WITH SCHEMABINDING AS 

SELECT 
    p.ClientID   
    , p.ID  AS [ProjectID]    
    , j.ID  AS [JobID] 
    , pkg.ID  AS [PackageID] 
    , i.ID  AS [ItemID]  
    , s.ID  AS [ScanID] 
    , s.DateTime 
    , o.Code 
    , o.Cutoff 
    , d.ID  AS [DepartmentID] 
    , d.LocationID 
    -- other columns 
FROM 
    [Projects] AS p 
    INNER JOIN [Jobs] AS j 
     ON p.ID = j.ProjectID 
    INNER JOIN [Packages] AS pkg 
     ON j.ID = pkg.JobID 
    INNER JOIN [Items] AS i 
     ON pkg.ID = i.PackageID 
    INNER JOIN [Scans] AS s 
     ON i.ID = s.ItemID 
    INNER JOIN [Operations] AS o 
     ON s.OperationID = o.ID 
    INNER JOIN [Departments] AS d 
     ON i.DepartmentID = d.ID; 

和聚簇索引：

CREATE UNIQUE CLUSTERED INDEX [IDX_ItemScans] ON [ItemScans] 
(
    [PackageID] ASC, 
    [ItemID] ASC, 
    [ScanID] ASC 
) 

这里的通用存储的特效之一。它获取已扫描并有一个截止的项目数：

PROCEDURE [ItemsGetFinalizedCount] 
    @FilterBy  int = NULL 
    , @ID   int = NULL 
    , @FilterBy2 int = NULL 
    , @ID2   sql_variant = NULL 
    , @GroupBy  int = NULL   
WITH RECOMPILE 
AS 
BEGIN 

    SELECT 
     CASE @GroupBy   
      WHEN 1 THEN 
       CONVERT(sql_variant, LocationID) 
      WHEN 2 THEN 
       CONVERT(sql_variant, DepartmentID) 
      -- other cases 
     END AS [ID] 
     , COUNT(DISTINCT ItemID) AS [COUNT] 
    FROM 
     [ItemScans] WITH (NOEXPAND) 
    WHERE  
     (@ID IS NULL OR 
     @ID = CASE @FilterBy    
      WHEN 1 THEN   
       ClientID 
      WHEN 2 THEN 
       ProjectID 
      -- other cases 
     END) 
     AND (@ID2 IS NULL OR 
     @ID2 = CASE @FilterBy2   
      WHEN 1 THEN   
       CONVERT(sql_variant, ClientID) 
      WHEN 2 THEN 
       CONVERT(sql_variant, ProjectID) 
      -- other cases 
     END) 
     AND Cutoff IS NOT NULL 
    GROUP BY 
     CASE @GroupBy   
      WHEN 1 THEN 
       CONVERT(sql_variant, LocationID) 
      WHEN 2 THEN 
       CONVERT(sql_variant, DepartmentID) 
      -- other cases 
     END 
END 

我第一次运行查询，看了看实际的执行计划，我创建的缺失索引，它建议：

CREATE NONCLUSTERED INDEX [IX_ItemScans_Counts] ON [ItemScans] 
(
    [Cutoff] ASC 
) 
INCLUDE ([ClientID],[ProjectID],[JobID],[ItemID],[SegmentID],[DepartmentID],[LocationID]) 

创建索引会将执行时间缩短到大约五秒钟，但仍然不可接受（查询的“特定”版本在亚秒级运行。）我尝试向索引中添加不同的列，而不是仅将它们包括在内获得成绩（并没有真正的帮助，我不知道我现在在做什么。）

下面是查询计划：

这里是为第一指标的细节寻求（它似乎返回所有行的视图，其中截止IS NOT NULL）：

Answer 1

的通用PROC可能无法在这种情况下，一个坏主意，但你不必把所有这些案件到最终的查询，你正在做的事情。我会尝试建立你的“具体问题”使用动态SQL在通用PROC，几乎相同的方式盖尔肖建立一个“包罗万象”的查询这里：

SQL in the Wild - Catch-all queries

这样，你能缓存查询计划并利用博客文章中显示的索引，并且您应该能够获得与之前相同的亚秒级性能。