SQL Server - 使用聚集索引时如何存储数据页

Question

Kermit

Asked: 2013-07-17 15:56:40 +0800 CST2013-07-17 15:56:40 +0800 CST 2013-07-17 15:56:40 +0800 CST

函数因空大小写操作而挂起

772

我创建了一个接受开始和结束日期的函数，结束日期是可选的。然后我在过滤器中写了一个CASE如果没有传递结束日期则使用开始日期。

CASE WHEN @dateEnd IS NULL
    THEN @dateStart
    ELSE @dateEnd
END

当我为最近一个月的数据调用该函数时：

SELECT * FROM theFunction ('2013-06-01', NULL)

...查询挂起。如果我指定结束日期：

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

...结果正常返回。我从函数中取出代码并在查询窗口中运行它。我也不能用小提琴复制这个问题。查询如：

SELECT * FROM theFunction ('2013-04-01', '2013-06-01')

...也可以正常工作。

NULL在结束日期传递a 时，查询（下方）中是否有任何内容可能导致函数挂起？

SQL小提琴

执行计划SELECT * FROM theFunction ('2013-06-01', '2013-06-01')
预计计划SELECT * FROM theFunction ('2013-06-01', NULL)

2 个回答

Voted

Martin Smith · Answer 1 · 2013-07-18T13:03:31+08:00

您的初始查询的一部分如下。

  FROM   [dbo].[calendar] a
          LEFT JOIN [dbo].[colleagueList] b
            ON b.[Date] = a.d
   WHERE  DAY(a.[d]) = 1
          AND a.[d] BETWEEN @dateStart AND COALESCE(@dateEnd,@dateStart)

该计划的该部分如下所示

在此处输入图像描述

您修改后的查询 BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart)具有相同的连接

在此处输入图像描述

不同之处似乎在于ISNULL进一步简化，因此您可以获得更准确的基数统计信息进入下一个连接。这是一个内联表值函数，您使用文字值调用它，因此它可以执行类似的操作。

 a.[d] BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart) 
 a.[d] BETWEEN '2013-06-01' AND ISNULL(NULL,'2013-06-01') 
 a.[d] BETWEEN '2013-06-01' AND '2013-06-01'
 a.[d] = '2013-06-01'

由于有一个 equi join predicate b.[Date] = a.d，该计划还显示了一个 equal predicate b.[Date] = '2013-06-01'。因此，28,393行的基数估计可能非常准确。

对于CASE/COALESCE版本 when@dateStart和@dateEnd是相同的值然后它将 OK 简化为相同的相等表达式并给出相同的计划但是 when @dateStart = '2013-06-01'and@dateEnd IS NULL它只达到

a.[d]>='2013-06-01' AND a.[Date]<=CASE WHEN (1) THEN '2013-06-01' ELSE NULL END

它也适用于上的隐含谓词ColleagueList。这次估计的行数是79.8rows。

下一个加入是

   LEFT JOIN colleagueTime
     ON colleagueTime.TC_DATE = colleagueList.Date
        AND colleagueTime.ASSOC_ID = CAST(colleagueList.ID AS VARCHAR(10))

colleagueTime是一个3,249,590行表，它（再次）显然是一个没有有用索引的堆。

这种估计差异会影响所使用的连接选择。该ISNULL计划选择一个只扫描表一次的散列连接。该COALESCE计划选择嵌套循环连接并估计它仍然只需要扫描一次表并能够假脱机结果并重放 78 次。即它估计相关参数不会改变。

由于嵌套循环计划在两个小时后仍在运行，因此这种针对单次扫描的假设colleagueTime似乎非常不准确。

至于为什么两个连接之间的估计行数要低得多，我不确定不能看到表上的统计信息。在我的测试中，我设法使估计行数偏斜的唯一方法是添加NULL行负载（这减少了估计行数，即使返回的实际行数保持不变）。

计划中的估计行数COALESCE与我的测试数据的顺序是

number of rows matching >= condition * 30% * (proportion of rows in the table not null)

或者在 SQL 中

SELECT 1E0 * COUNT([Date]) / COUNT(*) * ( COUNT(CASE
                                                  WHEN [Date] >= '2013-06-01' THEN 1
                                                END) * 0.30 )
FROM   [dbo].[colleagueList]

但这与您关于该列没有NULL值的评论不一致。

Kermit · Answer 2 · 2013-07-18T05:07:28+08:00

似乎数据类型存在问题。ISNULL解决了这个问题（感谢ypercube）。经过一些研究，COALESCE相当于我使用的CASE语句：

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END

保罗怀特解释说：

COALESCE( expression [ ,...n ] )返回具有最高数据类型优先级的表达式的数据类型。

ISNULL(check_expression, replacement_value)返回与 check_expression 相同的类型。

为避免任何数据类型问题，似乎ISNULL只使用处理两个表达式的合适函数。

XML 计划摘录

XML 计划使用CASE，表达式 2 是NULL：

SELECT * FROM theFunction ('2013-06-01', NULL)

<ScalarOperator ScalarString="CASE WHEN (1) THEN '2013-06-01' ELSE NULL END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Const ConstValue="(1)"/>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="'2013-06-01'"/>
      </ScalarOperator>
    </Then>
    <Else>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

XML plan using CASE，表达式 2 是一个日期：

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

<ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="Expr1035"/>
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Const ConstValue="(0)"/>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
      </Then>
    <Else>
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="Expr1036"/>
        </Identifier>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

XML 计划使用ISNULL，表达式 2 是NULL：

SELECT * FROM theFunction ('2013-06-01', NULL)

<ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="Expr1035"/>
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Const ConstValue="(0)"/>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
    </Then>
    <Else>
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="Expr1036"/>
        </Identifier>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

XML plan using ISNULL，表达式 2 是一个日期：

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

<ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="Expr1035"/>
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Const ConstValue="(0)"/>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
    </Then>
    <Else>
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="Expr1036"/>
        </Identifier>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

函数因空大小写操作而挂起

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

授予用户对所有表的访问权限

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

函数因空大小写操作而挂起

2 个回答

相关问题