logo标签列表

SQL Server性能和索引视图

sqlsql-serversql-server-2008indexed-view
4
使用SQL Server 2008。
我有多个位置，每个位置包含多个部门，每个部门又包含多个物品，这些物品可以有零到多个扫描记录。每个扫描与特定的操作相关联，该操作可能具有截止时间，也可能没有。每个物品还属于一个特定的包裹，该包裹属于一个特定的作业，该作业属于一个特定的项目，而该项目则属于一个特定的客户。每个作业包含一个或多个包裹，每个包裹包含一个或多个物品。
                                        +=============+     +=============+
                                        |   Projects  | --> |   Clients   |
                                        +=============+     +=============+
                                              ^
                                              |
+=============+                         +=============+
|  Locations  |                         |     Jobs    |
+=============+                         +=============+
      ^                                       ^
      |                                       |
+=============+     +=============+     +=============+
| Departments | <-- |    Items    | --> |   Packages  |
+=============+     +=============+     +=============+
                          ^
                          |
                    +=============+     +=============+
                    |    Scans    | --> | Operations  |
                    +=============+     +=============+

items表中大约有2400万条记录，scans表中大约有4800万条记录。每天都会不时批量插入新项目，通常一次插入数以万计。新扫描数据每小时批量插入，每个扫描项目的数量从几百到数十万不等。
这些表被频繁查询，并按各种方式进行切片和分组。我曾编写非常具体的存储过程，但随着时间推移，这变成了维护噩梦，因为我快要写出一百个存储过程，而且看不到尽头（例如类似于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])

创建索引将执行时间缩短到约5秒，但仍然不可接受（“特定”版本的查询运行在子秒级）。我尝试添加不同的列到索引中，而不是只是将它们包含其中，但没有性能上的提升（此时我真的不知道该怎么办了）。
以下是查询计划： queryplan 以下是第一个索引查找的详细信息（它似乎返回视图中所有Cutoff IS NOT NULL的行）： operation
1个回答
3
在这种情况下，一个通用的过程可能不是一个坏主意，但你不必像当前所做的那样将所有这些情况都放入最终查询中。我建议尝试在通用过程中使用动态SQL构建你的“特定查询”，就像Gail Shaw在这里构建“catch-all”查询一样：

SQL in the Wild - Catch-all queries

这样，你就能够缓存查询计划并利用索引，如博客文章所示，你应该能够获得相同的亚秒级性能。
感谢您的快速回复。我必须承认，我从来没有考虑过动态SQL。我知道在某些情况下它是有用的，但我的脑海中仍然回荡着“动态SQL总是邪恶”的声音。就是无法摆脱这个想法。 - Frank
1
使用带参数的 sp_executesql 可以提供与存储过程相当的性能，并且可以避免大部分 exec (@sql) 带来的问题。我认为“必须使用存储过程”的时代已经基本过去了，尽管我仍然在许多数据库中维护着数百个存储过程的集合，这是不幸的。 - Tim Lehner
2
你会喜欢这个的...如果你看我的存储过程，我在声明参数后面加了WITH RECOMPILE。如果我去掉它并在存储过程的末尾添加OPTION (RECOMPILE)，它就飞快了。我不够聪明，不知道区别，但我很高兴你发布了链接，让我去Erland Sommarskog的网站上发现了区别。这也是为什么我更喜欢别人指引我正确方向而不是告诉我如何做某事的另一个原因。再次感谢。 - Frank
可选参数，例如：WHERE(@ID2 IS NULL OR ID =@ID2)，会防止 SQL Server 使用任何 ID 索引，除非在 SELECT 查询的末尾使用 OPTION RECOMPILE。我不知道，但是我也注意到，在存储过程头中使用 WITH RECOMPILE 也无法正常工作。 - xav
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接