使用 MIN 和 MAX 函数降低存储过程性能

Question

我在生产中有一个数据库表，用于存储给定项目的workflow;该表的每条记录基本上代表特定日期的项目状态。

过于简化的表结构如下所示：

工作流表

|-------------|------------|---------|----------------|
| Category    | ItemCode   | Status  | InsertDate     |
|-------------|------------|---------|----------------|
|    Cat1     |    Foo1    | 01      | 2012-01-01     |
|-------------|------------|---------|----------------|
|    Cat1     |    Foo1    | 02      | 2012-03-02     |
|-------------|------------|---------|----------------|
|    Cat1     |    Foo1    | 03      | 2012-04-01     | 
|-------------|------------|---------|----------------|
|    Cat1     |    Foo2    | 01      | 2012-04-06     |
|-------------|------------|---------|----------------|
|    Cat1     |    Foo2    | 02      | 2012-05-07     |
|-------------|------------|---------|----------------|
|    Cat1     |    Foo2    | 04      | 2012-05-09     | 
|-------------|------------|---------|----------------|
|    Cat2     |    Foo3    | 01      | 2011-02-03     |    
|-------------|------------|---------|----------------|
|    ...      |    ...     | ..      |....            |    
|-------------|------------|---------|----------------|

因此，在 2012-01-01 时，项目 Foo1 已达到状态 01;在 2012-04-01 已达到状态 03，依此类推。

存储过程PR_GetCategoryItemsInformation ，将给定的Category作为输入，读取工作流表并给出如下结果：

@Input：第1
类输出：

|------------------|---------------|------------------|---------------------|
|   Category       |    ItemCode   | DateOfFirstRecord| StatusOfLatestRecord|
|------------------|---------------|------------------|---------------------|
|     Cat1         |     Foo1      |    2012-01-01    |         03          |    
|     Cat1         |     Foo2      |    2012-04-06    |         04          |

给定一个Category ItemCode的 SP 需要获取工作流的第一行以读取InsertDate，而工作流的最后一行以获取当前Status。

它归结为如下所示的 SP 实现：

CREATE PROCEDURE dbo.PR_GetFooItemInformation
    @Category CHAR(3)
AS
BEGIN
    CREATE TABLE #TabTemp (
            Category CHAR(3),
        ItemCode CHAR(3),       
        Status CHAR(2), 
        InsertDate DATETIME
    )
    CREATE CLUSTERED INDEX XIE1TabTemp 
        ON #TabTemp (...)
    CREATE NONCLUSTERED INDEX XIE2TabTemp 
        ON #TabTemp (...)
    INSERT INTO #TabTemp 
    SELECT
                 Category,
                 ItemCode,
                 Status,
                 InsertDate   
    FROM Workflow
    WHERE (Some rules to cut down the number of rows)
  SELECT 
      T1.Category,
      Item.ItemCode,
      T1.InsertDate,
      T2.Status
  FROM 
      Item
  INNER JOIN
      #TabTemp as T1 ON Item.ItemCode = Workflow.ItemCode
  INNER JOIN 
      #TabTemp as T2 ON Item.ItemCode = Workflow.ItemCode
  WHERE
      ...
  AND
      T1.InsertDate= SELECT 
                         MIN(InsertDate) 
                     FROM 
                         #TabTemp as T3 
                     WHERE ..
  AND
      T2.InsertDate = SELECT 
                         MAX(InsertDate) 
                      FROM 
                         #TabTemp as T4 
                      WHERE ..

SP多年来一直按预期工作(2005年(，但几个月前它开始给出一些随机超时;由于workflow表的记录数量正在增长(2.5M并且还在不断增加(，它的性能肯定会越来越差*。

这些表已正确编制索引，就其价值而言，SQL 管理工作室不建议在 SP 上进一步编制索引。
不使用临时表的同一 SP 大约慢 4 倍。
此时，临时表在每次调用中平均填充 1.5M 行。

根据我有限的 dba 知识，这个问题与需要计算的MIN和MAX函数有关，以达到给定类别的每个项目的第一行和最后一行。

我

省略了工作流表和SP实现上的几个细节，但我希望我所描述的内容足以了解问题。

最后的问题：
你知道任何SQL策略甚至SQL-Server专有解决方案来处理这种情况吗？

我有什么样的限制？
好吧，SP 用于 BackOffice 函数，应该返回所有实时记录，而不是预处理的子集。

^{* 我不是dba;其中一个dba目前正在他黑暗的实验室里研究这个小怪物。}

Answer 1

您建议的转换可以通过相对简单的查询来完成：

select category, ItemCode, min(InsertDate) as DateOfFirstRecord,
       max(case when seqnum = 1 then Status end) as LastStatus           
from (Select category, ItemCode, Status, InsertDate,
             row_number() over (partition by category, ItemCode order by InsertDate desc) as seqnum
      from workflow w
      where category = <category>  
     )  w
group by category, ItemCode;

我意识到，一旦你把你的条件放进去，这就会更加复杂。

通常，我更喜欢让 SQL 优化器选择执行查询的最佳方式，而不是使用临时表。 (话虽如此，有一些非常不愉快的经历，由于优化器选择了错误的计划，我不得不求助于多个查询。

我建议您尝试一下，看看它是否可以解决您的性能问题。

Answer 2

为什么你必须计算日期内的 MAX 和 MIN？

你可以为MAX做

 SELECT TOP 1 InsertDate FROM #TabTemp WHERE ... ORDER BY InsertDate DESC

和最小值

 SELECT TOP 1 InsertDate FROM #TabTemp WHERE ... ORDER BY InsertDate ASC

并将其保存到 2 个日期时间变量。

Answer 3

SELECT  *
FROM    item
CROSS APPLY
        (
        SELECT  MIN(insertDate) AS dateOfFirstRecord
        FROM    workflow wf
        WHERE   wf.itemCode = i.itemCode
        ) fr
OUTER APPLY
        (
        SELECT  TOP 1
                status AS statusOfLatestRecord
        FROM    workflow wf
        WHERE   wf.itemCode = i.itemCode
        ORDER BY
                wf.insertDate DESC
        ) lr

在workflow (itemCode, insertDate)上创建一个索引，以便快速工作。

Answer 4

插入日期是索引吗？您需要一个复合索引(类别、项代码、插入日期(

最有可能的是，您的瓶颈在于您不必要地创建的临时表中。您可以使用 where 条件筛选出行。

是否可以像这样重写查询？

select category, itemcode, a.InsertDate, b.Status from (
        select category, itemCode, min(InsertDate) minDate, max(insertDate) maxDate
        from table where .. group by categroy, item code) minmax
        join table a on a.category =minmax.category 
                  and a.itemcode=minmax.itemcode and a.insertDate = minmax.mindate
        join table b on b.category =minmax.category and b.itemcode=minmax.itemcode 
        and b.insertDate = minmax.max date) results

Answer 5

试试这个 -

SELECT Category,
       ItemCode,
       MIN(InsertDate),
       MAX(Status)
FROM workflow 
WHERE Category = @cat
GROUP BY ItemCode 

您可能不需要临时表。此查询将获得所需的输出。为类别和项代码编制索引。