SQL CTE和临时表的区别

Question

我在这里遇到了一些难题。我的主要目标是能够在C#和Entity Framework中使用它，而我们从上面得到的指令是要远离存储过程。

我有两个表：一个交叉引用表和一个（Celko）树状表。

/**
 ** Table [dbo].[EntityXref]
 **/
IF EXISTS(SELECT * FROM sys.tables WHERE name = N'EntityXref' AND type = N'U')
    DROP TABLE [dbo].[EntityXref]
GO
CREATE TABLE dbo.[EntityXref]
( Id                BIGINT IDENTITY(1,1) NOT NULL
, EntityId          INT NOT NULL
, EntityTypeId      INT NOT NULL
, ChildEntityId     INT NOT NULL
, ChildEntityTypeId INT NOT NULL
, CONSTRAINT [PK_EntityXref] PRIMARY KEY NONCLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
, CONSTRAINT [UQ_EntityXref] UNIQUE CLUSTERED (EntityId, EntityTypeId, ChildEntityId, ChildEntityTypeId)
)


/**
 ** Table [dbo].[EntityTree]
 **/
IF EXISTS(SELECT * FROM sys.tables WHERE name = N'EntityTree' AND type = N'U')
    DROP TABLE dbo.EntityTree
GO
CREATE TABLE dbo.EntityTree
( Id            BIGINT IDENTITY(1,1) NOT NULL
, SystemId      INT NOT NULL DEFAULT 1 
, EntityId      INT NOT NULL -- could be an AgencyId, UserId, ClientId, VendorId, etc
, EntityTypeId  INT NOT NULL -- Defines the entity type
, isActive      BIT NOT NULL
, lft           BIGINT NOT NULL 
, rgt           BIGINT NOT NULL 
, CONSTRAINT [PK_EntityTree] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
, CONSTRAINT [UQ_Entity]     UNIQUE NONCLUSTERED (EntityId, EntityTypeId)
, CONSTRAINT [UQ_Left]       UNIQUE NONCLUSTERED ([lft])
, CONSTRAINT [UQ_LeftRight]  UNIQUE NONCLUSTERED ([lft], [rgt])
)
GO

基本的树形数据如下：

客户 -> 机构 -> 用户 -> 客户端

我们还有管理多个机构的用户，因此有交叉引用（不好的名称）表。我正在测试一个单一用户，该用户概述了98％的机构，并且我需要所有客户端。所以，我的难题是：

注意：

EntityTypeId = 7 -> 用于报告生成的用户帐户 EntityTypeId = 4 -> 客户帐户

这需要运行4秒钟，但不能表示为视图：

DECLARE @t TABLE
( childentityid INT
, childentitytypeid INT
)

INSERT INTO @t
SELECT et.RootEntityId, et.RootEntityTypeId
  FROM dbo.EntityXref et
 WHERE et.EntityId = 17088 AND et.EntityTypeId = 7    

SELECT * 
  FROM @t a
 INNER JOIN dbo.GetMyCaseLoad b ON a.RootEntityId = b.ParentEntityId AND a.RootEntityTypeId = b.ParentEntityTypeId
GO

这个操作需要运行36-40秒（对这个连接操作进行了多种不同的排列组合！）

WITH xrefParent (parentEntityId, parentEntityTypeId)    --, rootEntityId, rootEntityTypeId)
  AS (SELECT ChildEntityId, ChildEntityTypeId /*, EntityId, EntityTypeId */ FROM dbo.EntityXref WHERE EntityId = 17088 AND EntityTypeId = 7) 
SELECT *
  FROM GetMyCaseLoad cl
 INNER JOIN xrefParent p ON cl.ParentEntityId = p.parentEntityId AND cl.ParentEntityTypeId = p.parentEntityTypeId
-- WHERE p.rootEntityId = 17088 AND p.rootEntityTypeId = 7
GO

有什么办法可以将临时表的好处转化为视图，以供Entity Framework使用？

---

新增定义：

CREATE VIEW GetMyCaseLoad AS
SELECT Parent.Id            [ParentRecordId]
     , Parent.EntityId      [ParentEntityId]
     , Parent.EntityTypeId  [ParentEntityTypeId]
     , Child.SystemId       [ChildSystemId]
     , Child.Id             [ChildRecordId]
     , Child.EntityId       [ChildEntityId]
     , Child.EntityTypeId   [ChildEntityTypeId]
     , Child.isActive       [ChildIsActive]
     , Child.lft            [ChildLeft]
     , Child.rgt            [ChildRight]
  FROM dbo.EntityTree Parent
     , dbo.EntityTree Child
 WHERE Child.lft > Parent.lft
   AND Child.rgt < Parent.rgt
   AND Child.EntityTypeId = 4
GO


CREATE VIEW GetMyFullCaseLoad AS
SELECT x.Id             [XrefRecordId]
     , x.EntityId       [XrefParentEntityId]
     , x.EntityTypeId   [XrefParentEntityTypeId]
     , c.ParentRecordId
     , c.ParentEntityId
     , c.ParentEntityTypeId
     , c.ChildRecordId
     , c.ChildEntityId
     , c.ChildEntityTypeId
     , c.ChildIsActive
     , c.ChildLeft
     , c.ChildRight
     , x.CanRead
     , x.CanWrite 
  FROM EntityXref x
 INNER JOIN dbo.GetMyCaseLoad c ON x.ChildEntityId = c.ParentEntityId AND x.ChildEntityTypeId = c.ParentEntityTypeId
GO

第二个视图是我们试图加速的内容。

顺带一提，当前系统需要大约2-3分钟才能返回记录。基于新的数据结构（邻接树 vs 集合树），第二个视图或公共表表达式可以在40秒内完成此操作。使用临时表则只需要4秒钟。

- Keith Barrows

---

我想知道您是否能在sqlFiddle中创建一个示例演示，以便我们有些东西可以操作。您可能希望创建一个更简单的示例来展示关键问题。此外，性能问题应包括“EXPLAIN PLAN”。 - Juan Carlos Oropeza

我并不太清楚CTE对你有什么帮助，除了让查询变得更难读懂。我怀疑它不会让SQL Server感到困惑，但在未来的某个时候，它可能会让你或你的同事感到困惑。 - John Bollinger

为什么存储过程被禁止了？这似乎是一个奇怪而武断的决定，由一个不太了解SQL Server的人做出的。 - Sean Lange

如果临时表的好处仅仅是提高性能，那么我建议您先调整性能（例如索引等），除非索引也不被看好 :p - Nick.McDermaid

查询的执行计划在哪里？如果没有它，我们怎么可能诊断问题呢？ - Pieter Geerkens

Answer 1

CTE的问题在于它们没有被实现，它们没有专门的统计数据（它们依赖底层对象的统计数据），它们没有索引（虽然在某些情况下它们可以使用所引用表上的索引）。

临时表的好处在于它们本质上是实现的（在tempdb中），它们可以有索引（如果您定义了它们），并且绝对有专门的统计数据。

在很多情况下，使用临时表而不是CTE可以产生更好的执行计划。使用CTE几乎永远不会加快速度，而在很多情况下使用临时表会加快速度。

我将把你留给比我更高的权威，并留下他的话：

“CTE永远不应该用于性能。你几乎永远不会通过使用CTE来加速查询，因为它只是一个一次性视图。你可以用它们做一些有趣的事情，但加速查询确实不是其中之一。”

这个引用来自于问题“CTE和Temp Table之间的区别”的接受的答案。

PS：我看到你在第一个查询中使用了一个TABLE变量。这不同于临时表。从性能的角度来看，临时表几乎总是优于TABLE变量。关于TABLE变量和临时表之间的区别的好文章，请阅读这个答案被采纳的问题——"SQL Server中临时表和表变量有什么区别？"。

- TT.

Answer 2

在dbo.EntityXref（EntityId，EntityTypeId）上添加索引？

我猜测这是因为临时表的唯一区别就是如果没有索引，则只会在插入临时表时扫描一次表。 在其他排列中，它可能会变成循环表扫描。

唯一比表扫描更糟糕的是循环表扫描。

- Bruce Dunwiddie