这取决于具体情况,但很少有显著影响。
假设我们有一个视图,如下所示:
CREATE VIEW TestView
AS
Select A.x, B.y, B.z
FROM A JOIN B on A.id = B.id
B.id
已经绑定,因此它是非空的,并且与 A.id
存在外键关系 - 也就是说,每当有一个 B
行时,总是至少有一个对应的 A
。from t in context.TestView where t.x == 3
这样的事情,而不是 from a in context.A join b in context.B on a.id equals b.id where a.x == 3 select new {a.x, b.y, b.z}
。(from t in context.TestView select t.z).Distinct()
. vs (from b in context.B select b.z).Distinct()
。SELECT DISTINCT z FROM ...
。B
的表扫描或索引扫描。A.id
上的连接是无关的。但是,数据库服务器擅长这种事情;它是一组计算机科学和已经进行了数十年研究的问题。CREATE VIEW Complicated
AS
Select A.x, B.x as y, C.z, COALESCE(D.f, D.g, E.h) as foo
FROM
A JOIN B on A.r = B.f + 2
JOIN C on COALESCE(A.g, B.x) = C.x
JOIN D on D.flag | C.flagMask <> 0
WHERE EXISTS (SELECT null from G where G.x + G.y = A.bar AND G.deleted = 0)
AND A.deleted = 0 AND B.deleted = 0
CREATE VIEW AllAncestry
AS
WITH recurseAncetry (ancestorID, descendantID)
AS
(
SELECT parentID, childID
FROM Parentage
WHERE parentID IS NOT NULL
UNION ALL
SELECT ancestorID, childID
FROM recurseAncetry
INNER JOIN Parentage ON parentID = descendantID
)
SELECT DISTINCT (cast(ancestorID as bigint) * 0x100000000 + descendantID) as id, ancestorID, descendantID
FROM recurseAncetry