SQL Server 2012懒加入 [英] Does SQL Server 2012 Lazy Join

问题描述

我正在开发一个应用程序，允许用户动态选择他们看到什么。它们属于的列和表的列表是巨大的。我正在开发一个动态查询，但我不想存储所有的连接逻辑，如果我也没有。
例如，我有一个查询，有10个表的LEFT JOINS，但我只选择和筛选一个表。

我知道Oracle的DBMS是这样工作的，但是我的公司使用SQL Server 2012，所以我'

示例查询最后4个LEFT JOINS不需要执行的位置：

  SELECT [IncidentIncidentNumberModValue]。[IncidentNumber] 
，IncidentNumberModifierNotValue。[Values] AS IncidentNumberNotValue 
，[Incident]。[IncidentDate] 
， [ResponseNumber] 
，[Incident]。[PatientCareReportNumber] 
，[IncidentType]。[Value] AS IncidentType 
，[SceneStreetAddressModValue]。[StreetAddress2] AS IncidentAddress 
，IncidentStreetAddressNotValue 。[Values] AS IncidentAddressNotValue 
，COUNT（*）OVER（）AS TotalRecordCount 
 FROM [EmsEvent]。事件
 LEFT JOIN [Resource]。[IncidentType] ON [EmsEvent]。 [Incident] [IncidentType] = [Resource]。[IncidentType]。[IncidentTypeID] 
 LEFT JOIN [EmsEvent]。[IncidentIncidentNumberModValue] ON [EmsEvent]。[Incident]。[IncidentID] = [EmsEvent] IncidentIncidentNumberModValue] [IncidentID] 
 LEFT JOIN [GlobalResource]。[IncidentNumberModifier] ON [EmsEvent]。[IncidentIncidentNumberModValue]。[NotValue] = [GlobalResource]。[IncidentNumberModifier]。[DataElementID] 
 LEFT JOIN [ [Scene] [Scene]。[EventSource]。[EventSource]。[EventSource]。[EventSource] [EventSource] [EventValue] = [EmsEvent]。[Incident]。[IncidentID] 
 LEFT JOIN [EmsEvent]。[SceneStreetAddressModValue] ON [EmsEvent]。[Scene]。[SceneID] = [EmsEvent]。[SceneStreetAddressModValue]。 b $ b LEFT JOIN [GlobalResource]。[IncidentStreetAddressModifier] ON [EmsEvent]。[SceneStreetAddressModValue]。[NotValue] = [GlobalResource]。[IncidentStreetAddressModifier]。[DataElementID] 
 LEFT JOIN [GlobalResource]。[NotValue] IncidentStreetAddressNotValue [EmsEvent]。[IncidentContext]。[IncidentStreetAddressModifier]。[NotValueID] = IncidentStreetAddressNotValue。[NotValueID] 
 LEFT JOIN [EmsEvent]。[CustomConfig] on [EmsEvent]。[IncidentID] = [EmsEvent]。[CustomConfig ] [IncidentID] 
 LEFT JOIN [EmsEvent]。[IncidentDisasterType] on [EmsEvent]。[Incident]。[IncidentID] = [EmsEvent]。[IncidentDisasterType]。[IncidentID] 
 LEFT JOIN [EmsEvent ] [EmsEvent]。[Scene]。[SceneID] = [EmsEvent]。[Response]。[SceneID] 
 LEFT JOIN [EmsEvent]。[CrewMember] on [EmsEvent] 。[ResponseID] = [EmsEvent]。[CrewMember]。[ResponseInformationID] 
 
 WHERE [EmsEvent]。[Incident]。[DeletedStatus] = 0 
 AND [EmsEvent] .AgencyID ='607CEE05-276D-49A1-B6BF-FD606EFC2377'
 ORDER BY [EmsEvent]。[IncidentIncidentNumberModValue]。[IncidentNumber] ASC 
 OFFSET 0 ROWS 
 FETCH NEXT 25 ROWS ONLY 
 OPTION（recompile）
  

解决方案

如果列是主键或具有唯一约束，则表将从查询计划中排除if

它看起来不像你的最后四个表在它们各自的主键上加入。

使用以下表格：

  create table父
（
 ParentID int primary键
）
 
创建表child 
（
 ChildID int主键，
 ParentID int引用Parent（ParentID）
）
  

此查询不会使用表 Parent p>

选择C. *
来自Child as C
left outer join Parent as P
on P .ParentID = C.ParentID


但是此查询必须使用左外部联接的Child表，因为它可能会向结果中添加行如果外键有多个匹配。

 选择P. * 
来自Parent as P 
 left outer join孩子作为C 
在P.ParentID = C.ParentID 
  

img src =https://i.stack.imgur.com/i3thJ.pngalt =输入图片说明here>>

I am working on an application that allows users to dynamically choose what they see. The list of columns and tables those belong to is huge. I'm working on building a dynamic query but I don't want to have to store all the join logic if I don't have too. An example is I have a query that has LEFT JOINS for 10 tables but I only select and filter by one table. Is SQL Server 2012 Smart enough to not join on the other tables that are not needed?

I know Oracle's DBMS works like this but my company uses SQL Server 2012 so I'm stuck with that.

Example Query where the last 4 LEFT JOINS don't need to be performed:

SELECT [IncidentIncidentNumberModValue].[IncidentNumber]
    ,IncidentNumberModifierNotValue.[Value] AS IncidentNumberNotValue
    ,[Incident].[IncidentDate]
    ,[Incident].[ResponseNumber]
    ,[Incident].[PatientCareReportNumber]
    ,[IncidentType].[Value] AS IncidentType
    ,[SceneStreetAddressModValue].[StreetAddress2] AS IncidentAddress
    ,IncidentStreetAddressNotValue.[Value] AS IncidentAddressNotValue
    ,COUNT(*) OVER() AS TotalRecordCount
FROM [EmsEvent].[Incident]
    LEFT JOIN [Resource].[IncidentType] ON [EmsEvent].[Incident].[IncidentType] = [Resource].[IncidentType].[IncidentTypeID]
    LEFT JOIN [EmsEvent].[IncidentIncidentNumberModValue] ON [EmsEvent].[Incident].[IncidentID] = [EmsEvent].[IncidentIncidentNumberModValue].[IncidentID]
    LEFT JOIN [GlobalResource].[IncidentNumberModifier] ON [EmsEvent].[IncidentIncidentNumberModValue].[NotValue] = [GlobalResource].[IncidentNumberModifier].[DataElementID]
    LEFT JOIN [GlobalResource].[NotValue] IncidentNumberModifierNotValue ON [GlobalResource].[IncidentNumberModifier].[NotValueID] = IncidentNumberModifierNotValue.[NotValueID]
    LEFT JOIN [EmsEvent].[Scene] ON [EmsEvent].[Scene].[IncidentID] = [EmsEvent].[Incident].[IncidentID]
    LEFT JOIN [EmsEvent].[SceneStreetAddressModValue] ON [EmsEvent].[Scene].[SceneID] = [EmsEvent].[SceneStreetAddressModValue].[SceneID]
    LEFT JOIN [GlobalResource].[IncidentStreetAddressModifier] ON [EmsEvent].[SceneStreetAddressModValue].[NotValue] = [GlobalResource].[IncidentStreetAddressModifier].[DataElementID]
    LEFT JOIN [GlobalResource].[NotValue] IncidentStreetAddressNotValue ON [GlobalResource].[IncidentStreetAddressModifier].[NotValueID] = IncidentStreetAddressNotValue.[NotValueID]
    LEFT JOIN [EmsEvent].[CustomConfig] on [EmsEvent].[Incident].[IncidentID] = [EmsEvent].[CustomConfig].[IncidentID]
    LEFT JOIN [EmsEvent].[IncidentDisasterType] on [EmsEvent].[Incident].[IncidentID] = [EmsEvent].[IncidentDisasterType].[IncidentID]
    LEFT JOIN [EmsEvent].[Response] on [EmsEvent].[Scene].[SceneID] = [EmsEvent].[Response].[SceneID]
    LEFT JOIN [EmsEvent].[CrewMember] on [EmsEvent].[Response].[ResponseID] = [EmsEvent].[CrewMember].[ResponseInformationID]

WHERE [EmsEvent].[Incident].[DeletedStatus] = 0
    AND [EmsEvent].[Incident].AgencyID = '607CEE05-276D-49A1-B6BF-FD606EFC2377'
ORDER BY [EmsEvent].[IncidentIncidentNumberModValue].[IncidentNumber] ASC
OFFSET 0 ROWS
FETCH NEXT 25 ROWS ONLY
OPTION(recompile)

解决方案

It depends on what column in the left outer joined table you are using in the join.

If the column is a primary key or has a unique constraint the table will be excluded from the query plan if it is not referenced elsewhere in the query.

It does not look like your last four tables are joined on their respective primary key.

With tables like this:

create table Parent
(
  ParentID int primary key
)

create table Child
(
  ChildID int primary key,
  ParentID int references Parent(ParentID)
)

This query will not use the table Parent.

select C.*
from Child as C
  left outer join Parent as P
    on P.ParentID = C.ParentID

But this query have to use the left outer joined Child table because it might add rows to the result if there are more than one hit on the foreign key.

select P.*
from Parent as P
  left outer join Child as C
    on P.ParentID = C.ParentID

这篇关于SQL Server 2012懒加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！