sql-server – 索引不会使执行更快,并且在某些情况下会降低查询

With the default index (on primary key only) why does it take less time,and with the non clustered index present,for each row in the joining table,the joined table row should be found quicker,because join is on Name column on which the index has been created. This is reflected in the query execution plan and Index Seek cost is less when IndexA is active,but why still slower? Also what is in the Nested Loop left outer join that is causing the slowdown?

现在应该清楚的是,非聚集索引计划可能更有效,正如您所期望的那样;在执行时跨线程的工作分配很差,这会导致性能问题.

为了完成示例并说明我提到的一些事情,获得??更好的工作分配的一种方法是使用临时表来驱动并行执行：

SELECT
    val1,val2
INTO #Temp
FROM dbo.IndexTestTable AS ITT
WHERE Name = N'Name1';

SELECT 
    N'Name1',SUM(T.val1),SUM(T.val2),SUM(I2.val2)
FROM   #Temp AS T
CROSS JOIN IndexTestTable I2
WHERE
    I2.Name = 'Name1'
OPTION (FORCE ORDER,QUERYTRACEON 8690);

DROP TABLE #Temp;

这导致一个计划使用更有效的索引查找,没有表假脱机,并且很好地跨线程分配工作：

在我的系统上,此计划的执行速度明显快于聚集索引扫描版本.

如果您有兴趣了解有关并行查询执行内部的更多信息,您可能会喜欢watch my PASS Summit 2013 session recording.

（编辑：拼字网 - 核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!