SQL Server 性能和索引 View

Question

使用 SQL Server 2008。

(很抱歉，如果这只是一篇文章，但我正在尝试提供尽可能多的信息。)

我有多个位置，每个位置包含多个部门，每个部门包含多个项目，这些项目可以进行零到多次扫描。每次扫描都与特定操作相关，该操作可能有也可能没有截止时间。每个项目还属于一个特定的包，该包属于一个特定的工作，属于一个特定的项目，属于一个特定的客户。每个作业包含一个或多个包，其中包含一个或多个项目。

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

items 表中大约有 24,000,000 条记录，scans 表中大约有 48,000,000 条记录。新项目全天零星地批量插入数据库，通常一次插入数万个。每小时都会批量插入新的扫描，每次插入几百到几十万个。

这些表被大量查询、切片和切 block 。我正在编写非常具体的存储过程，但它变成了维护噩梦，因为我正处于站点中没有尽头的一百个存储过程的边缘(例如类似于 ScansGetDistinctCountByProjectIDByDepartmentIDGroupedByLocationID、ScansGetDistinctCountByPackageIDByDepartmentIDGroupedByLocationID 等)幸运的是，要求几乎每天都会改变(感觉如何)，每次我必须更改/添加/删除一列时，好吧......我最终会在酒吧。

因此，我创建了一个索引 View 和一些通用存储过程，其中包含用于确定过滤和分组的参数。不幸的是，性能下降了。 我想第一个问题是，由于选择性能至关重要，我是否应该坚持使用特定方法并通过对基础表的更改进行斗争？或者，可以采取一些措施来加速索引 View /通用查询方法吗？除了减轻维护噩梦之外，我实际上还希望索引 View 也能提高性能。

这是生成 View 的代码:

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]) 

创建索引将执行时间缩短到大约五秒，但这仍然是 Not Acceptable (查询的“特定”版本运行时间亚秒)。我尝试向索引添加不同的列，而不是仅仅包含它们而不包含它们。提高性能(并没有真正的帮助，因为我不知道此时我在做什么。)

这是查询计划:

以下是第一个索引查找的详细信息(它似乎返回 View 中 Cutoff IS NOT NULL 的所有行):

Answer 1

在这种情况下，通用过程可能不是一个坏主意，但您不必像当前所做的那样将所有这些情况放入最终查询中。我会尝试在通用过程中使用动态 SQL 构建“特定查询”，这与 Gail Shaw 在这里构建“包罗万象”查询的方式非常相似:

SQL in the Wild - Catch-all queries

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