SQL Server：设计：嵌入式 Select 语句还是 INNER JOIN？
我有以下表结构 -
站点：站点主表
组织：组织主表
用户：用户主表（每个用户通过 用户.OrgId)
OrgSite：存储一些“组织特定”网站详细信息（OrgId、SiteId、SiteName、 站点代码）。并非所有网站，但仅 组织可以访问的内容。
UserSite：将用户链接到其可访问的站点（UserId、SiteId）。 当用户链接到组织用户站点时 将是 OrgSite 表的子集。
ItemSite：存储一些项目和内容的表。站点特定详细信息（ItemID、 站点 ID、组织 ID、...)
现在，我必须过滤\显示“ItemSite”中的记录，并且我还需要显示站点代码。所以，我看到以下两个选项 -
1。创建一个视图： vw_ItemSite_UserSite_OrgSite（内部连接 SiteId 上的所有表） - 这将使我能够访问“OrgSite”表中可用的所有组织特定详细信息（即 SiteCode 等）
如果你能注意到我必须包括 视图中的“OrgSite”只是因为我 想要组织特定的站点代码和站点名称。 因为 UserSite 已经 过滤网站 - 这样我就可以 “排除”OrgSite 表和 消除不必要的 INNER JOIN。
2.基于上面的注释 - 第二个选项是创建一个视图：vw_ItemSite_UserSite 并在视图的“SELECT”语句中我可以嵌入以下 SELECT 就像 -
CREATE VIEW vw_ItemSite_UserSite AS
SELECT ItemSite.SiteID,
(SELECT TOP 1 [SiteCode] FROM OrgSite WHERE OrgId = ItemSite.OrgId) AS SiteCode,
...
FROM ItemSite INNER JOIN UserSite ON ItemSite.SiteId = UserSite.SiteId
我唯一的意图是 - 我相信 INNER JOIN 和WHERE 将在嵌入式 select 语句的求值之前求值。那么，这会节省一些性能吗？或者使用 vw_ItemSite_UserSite_OrgSite 的想法更好。
选项#1 还是选项#2？
谢谢。
I've the following table structure -
Site: Master tablefor site
Org: Master table for Org
User: Master table for User (each user links to a unique Org via
User.OrgId)OrgSite: Store some 'Org specific' Site details (OrgId, SiteId, SiteName,
SiteCode). Not ALL sites but only
those which are accessible to Org.UserSite: Link User to his accessible Site(s) (UserId, SiteId).
As a user is linked to an Org UserSite
will be a subset of the OrgSite table.ItemSite: Table which stores some Item & Site specific details (ItemID,
SiteId, OrgId, ...)
Now, I've to filter\display records from the 'ItemSite' and in that I also need to display the Sitecode. So, I see the following two options -
1. Create a VIEW: vw_ItemSite_UserSite_OrgSite (INNER JOIN all the tables on SiteId) - this will give me access to ALL the Org specific details available in the 'OrgSite' table (i.e. SiteCode, etc..)
If you can notice I've to include the
'OrgSite' in the view only because I
want Org specific SiteCode & SiteName.
Because the UserSite is already
filtering the Sites - so I can
'exclude' the OrgSite table and
eliminate an unnecessary INNER JOIN.
2. Based on the above note - the second option is to create a VIEW: vw_ItemSite_UserSite and in the 'SELECT' statement of the VIEW I can embed the following SELECT like -
CREATE VIEW vw_ItemSite_UserSite AS
SELECT ItemSite.SiteID,
(SELECT TOP 1 [SiteCode] FROM OrgSite WHERE OrgId = ItemSite.OrgId) AS SiteCode,
...
FROM ItemSite INNER JOIN UserSite ON ItemSite.SiteId = UserSite.SiteId
My only intention is that - I believe the INNER JOIN and WHERE will be evaluted before the evalution of the embedded select statement. So, does this save me some performance? Or is the idea of having the vw_ItemSite_UserSite_OrgSite is better.
Option#1 or option#2?
Thank you.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
谨防过早优化。如果两个查询返回相同的结果，请使用更易于理解和维护的查询。 SQL Server 的任务是确保查询操作（连接、选择等）按照优化性能的顺序执行。通常，SQL Server 在这方面做得很好。
也就是说，在某些情况下，SQL Server 查询分析器找不到最佳查询计划，您需要自行微调。然而，这些都是罕见的情况。除非您的查询已经存在性能问题（并且无法通过引入缺失索引来修复这些问题），否则您现在不必担心。
Beware of Premature optimization. If both queries return the same result, use the one that is easier for you to understand and maintain. It's SQL Server's task to make sure that the query operations (join, select, ...) are performed in the order which optimizes performance. And, usually, SQL Server does quite a good job on that.
That said, there are some occasions where the SQL Server query analyzer does not find the optimal query plan and you need to fine-tune yourself. However, these are rare cases. Unless you already have performance problems with your query (and they cannot be fixed by introducing missing indexes), this is something you should not worry about right now.
我将采用简单的答案方法。创建一些测试并检查它们的性能，看看哪一个真正最适合您的给定环境。
I'll take the easy answer approach.Create some tests and check them for performance and see which one really performs best for your given environment.
选项 1 几乎肯定会更快，嵌入式 SELECT 对于性能来说通常不是一个好主意。
但是 - 不要相信我们的话。对两者进行编码并尝试它们，检查查询计划。在这种情况下，这可能是不成熟的优化，但它也是一个很好的简单测试用例，可以用来学习，这样您就可以正确地知道如何做到这一点，以及当您遇到真正需要正确方法来解决问题时会产生什么影响。有时，编写相同查询的不同方式之间存在巨大性能差异，优化器对此无能为力，因此预先了解一般规则，您的生活会更快乐。
Option 1 will almost certainly be faster, the embedded SELECT is usually a bad idea for performance.
BUT - don't take our word for it. Code up both and try them, checking the query plans. It's probably premature optimisation in this case, but it's also a good simple test case on which to learn so you know properly how to do it and what the implications are for when you have a problem that really needs the right way to do it. There are sometimes huge performance differences between different ways of writing the same query that the optimiser can do nothing about so learn the general rules up front and your life will be happier.