为什么使用表假脱机慢？

问题描述：

有在SQL Server中运行两个similiar sql语句，该表TBSFA_DAT_CUST有millons行和无约束（无索引和主键），其他两个有只有几行和正常的主键：为什么使用表假脱机慢？

小号较慢的一个：

SELECT A.CUST_ID, C.CUST_NAME, A.xxx --and several specific columns 
FROM TBSFA_DAT_ORD_LIST A JOIN VWSFA_ORG_EMPLOYEE B ON A.EMP_ID = B.EMP_ID 
    LEFT JOIN TBSFA_DAT_CUST C ON A.CUST_ID = B.CUST_ID 
    JOIN VWSFA_ORG_EMPLOYEE D ON A.REVIEW_ID = D.EMP_ID 
WHERE ISNULL(A.BATCH_ID, '') != ''

execution plan of slower one

f表示更快的一个：

SELECT * 
FROM TBSFA_DAT_ORD_LIST A JOIN VWSFA_ORG_EMPLOYEE B ON A.EMP_ID = B.EMP_ID 
    LEFT JOIN TBSFA_DAT_CUST C ON A.CUST_ID = B.CUST_ID 
    JOIN VWSFA_ORG_EMPLOYEE D ON A.REVIEW_ID = D.EMP_ID 
WHERE ISNULL(A.BATCH_ID, '') != ''

execution plan of faster one

f（0.6s以上）比s（4.6s以上）快得多。

否则，我发现了两个办法，使s快速为f：

1.增加constaint和表TBSFA_DAT_CUST.CUST_ID主键;

2.特定的61个以上的表TBSFA_DAT_CUST（共80列）列。

我的问题是为什么sql优化器使用表假脱机当我在SELECT子句而不是'*'的特定列，以及为什么使用表假脱机一个执行较慢？

我的问题是关于sql-server table-spool

确保你运行它至少两次，所以你消除时间创造这个计划是一个原因。除此之外，我的猜测是'select *'，SQL知道它必须执行表扫描，所以它甚至不会尝试执行某些“优化”。然而，通过选择单独的列，它会尝试，而在这种情况下，不会做得更好。表假脱机意味着SQL基本上将所有数据都放入临时表中，以便稍后在查询中重新使用它。虽然这并非天生不好，但需要一些时间，这可能只是优化器丢弃球的情况。 – Xedni

谢谢你！我已经运行这些sql几十次以获得平均成本。并且执行计划显示两个sqls都进行了表扫描。我想知道的是为什么sql优化器选择使用表假脱机，而我指定的列和发生的事情，使其缓慢？据我所知，通常选择特定的列应该比不快。 –

最有可能是因为它是如此宽阔的桌子。当你选择*时，它意识到这是一大堆数据，并且认为桌面假脱机会浪费时间和精力。当你限制列数时，它认为它现在处于阈值之内，假脱机数据可能是一个很好的调用。如果没有索引，那么就没有多少SQL可以知道数据的统计数据，因此估计会变得更糟。这也可能是为什么（除其他原因之外）为表格添加索引会提高性能。 – Xedni

答

在你限制你的结果设定为特定的列较慢的查询。由于这是一个未索引的非约束表，因此优化器正在从原始表扫描中创建一个临时表，只需要特定的列。然后它通过临时表上的嵌套循环运算符运行。当它知道它需要表上的每一列（Select *）时，它可以直接在表扫描之外运行嵌套循环操作符，因为扫描的结果集将全部连接到顶部表。

外，你的查询有几个其他可能出现的问题：

LEFT JOIN TBSFA_DAT_CUST C ON A.CUST_ID = B.CUST_ID

你不加入任何东西在这里，你将加入整个表的每一个记录。意思是a.cust_id = c.cust_id或b.cust_id = c.cust_id或a.cust_id = c.cust_id and b.cust_id = c.cust_id？

此外，该功能在where子句是没有意义的，而且会降低性能：

WHERE ISNULL(A.BATCH_ID, '') != ''

将其更改为：

WHERE A.BATCH_ID is not null and A.Batch_ID <> ''

为什么使用表假脱机慢？

相关推荐