Guest User

geometry type execution plan

a guest
Sep 19th, 2013
66
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?xml version="1.0" encoding="utf-16"?>
  2. <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1617.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  3. <BatchSequence>
  4. <Batch>
  5. <Statements>
  6. <StmtSimple StatementCompId="1" StatementEstRows="25409.8" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="143.023" StatementText="select o.code as Code, ro.ReportTaxonCode as TaxonCode, t1.TaxonName as TaxonName,&#xD;&#xA; case&#xD;&#xA; when o.NumberObserved IS NOT NULL THEN o.NumberObserved&#xD;&#xA; when o.NumberObserved IS NULL then su.Mean&#xD;&#xA; end as Scale,&#xD;&#xA; ro.OriginalTaxonCode as RecordedCode, t2.TaxonName as RecordedName, s.code as SetCode, CAST(s.StartDate AS nvarchar), CAST(s.EndDate AS nvarchar), l.code as Location, l.geom&#xD;&#xA; from obsveg.obs_observations o&#xD;&#xA; inner join obsveg.obs_sets s&#xD;&#xA; on o.obs_setsguid = s.guid&#xD;&#xA; inner join obsveg.obs_locations l&#xD;&#xA; on o.obs_locationsguid = l.guid&#xD;&#xA; inner join obsveg.obs_rpt_reportobservations ro&#xD;&#xA; on o.guid = ro.obs_observationsguid&#xD;&#xA; inner join obsveg.obs_taxon t1&#xD;&#xA; on ro.ReportTaxonCode = t1.code&#xD;&#xA; inner join obsveg.obs_taxon t2&#xD;&#xA; on ro.OriginalTaxonCode = t2.code&#xD;&#xA; left join obsveg.OBS_MeasurementMethodScaleUnits mmsu&#xD;&#xA; on o.OBS_MeasurementMethodScaleUnitsGUID = mmsu.GUID&#xD;&#xA; left join obsveg.obs_ScaleUnits su&#xD;&#xA; on mmsu.OBS_ScaleUnitsGUID = su.GUID&#xD;&#xA; where ro.reportguid = '64c0a4af-ee4d-4e83-a194-2a14e8a6ab0e'&#xD;&#xA; and l.geom.STGeometryType() = 'Point'&#xD;&#xA;" StatementType="SELECT" QueryHash="0x4BD4C225A6BB804B" QueryPlanHash="0xDC004F73B1922F07">
  7. <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
  8. <QueryPlan DegreeOfParallelism="1" MemoryGrant="165832" CachedPlanSize="152" CompileTime="576" CompileCPU="576" CompileMemory="11512">
  9. <RelOp AvgRowSize="4462" EstimateCPU="0.00254098" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25409.8" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="143.023">
  10. <OutputList>
  11. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  12. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="Code" />
  13. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  14. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  15. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  16. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  17. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  18. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  19. <ColumnReference Column="Expr1016" />
  20. <ColumnReference Column="Expr1017" />
  21. <ColumnReference Column="Expr1018" />
  22. </OutputList>
  23. <ComputeScalar>
  24. <DefinedValues>
  25. <DefinedValue>
  26. <ColumnReference Column="Expr1016" />
  27. <ScalarOperator ScalarString="CASE WHEN [CMSI_NM].[OBSVEG].[OBS_Observations].[NumberObserved] as [o].[NumberObserved] IS NOT NULL THEN CONVERT_IMPLICIT(float(53),[CMSI_NM].[OBSVEG].[OBS_Observations].[NumberObserved] as [o].[NumberObserved],0) ELSE CASE WHEN [CMSI_NM].[OBSVEG].[OBS_Observations].[NumberObserved] as [o].[NumberObserved] IS NULL THEN [CMSI_NM].[OBSVEG].[OBS_ScaleUnits].[Mean] as [su].[Mean] ELSE NULL END END">
  28. <IF>
  29. <Condition>
  30. <ScalarOperator>
  31. <Compare CompareOp="IS NOT">
  32. <ScalarOperator>
  33. <Identifier>
  34. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  35. </Identifier>
  36. </ScalarOperator>
  37. <ScalarOperator>
  38. <Const ConstValue="NULL" />
  39. </ScalarOperator>
  40. </Compare>
  41. </ScalarOperator>
  42. </Condition>
  43. <Then>
  44. <ScalarOperator>
  45. <Convert DataType="float" Scale="0" Style="0" Implicit="true">
  46. <ScalarOperator>
  47. <Identifier>
  48. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  49. </Identifier>
  50. </ScalarOperator>
  51. </Convert>
  52. </ScalarOperator>
  53. </Then>
  54. <Else>
  55. <ScalarOperator>
  56. <IF>
  57. <Condition>
  58. <ScalarOperator>
  59. <Compare CompareOp="IS">
  60. <ScalarOperator>
  61. <Identifier>
  62. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  63. </Identifier>
  64. </ScalarOperator>
  65. <ScalarOperator>
  66. <Const ConstValue="NULL" />
  67. </ScalarOperator>
  68. </Compare>
  69. </ScalarOperator>
  70. </Condition>
  71. <Then>
  72. <ScalarOperator>
  73. <Identifier>
  74. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_ScaleUnits]" Alias="[su]" Column="Mean" />
  75. </Identifier>
  76. </ScalarOperator>
  77. </Then>
  78. <Else>
  79. <ScalarOperator>
  80. <Const ConstValue="NULL" />
  81. </ScalarOperator>
  82. </Else>
  83. </IF>
  84. </ScalarOperator>
  85. </Else>
  86. </IF>
  87. </ScalarOperator>
  88. </DefinedValue>
  89. </DefinedValues>
  90. <RelOp AvgRowSize="4466" EstimateCPU="0.217098" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25409.8" LogicalOp="Right Outer Join" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="143.021">
  91. <OutputList>
  92. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  93. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  94. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="Code" />
  95. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  96. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  97. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  98. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  99. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  100. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  101. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_ScaleUnits]" Alias="[su]" Column="Mean" />
  102. <ColumnReference Column="Expr1017" />
  103. <ColumnReference Column="Expr1018" />
  104. </OutputList>
  105. <MemoryFractions Input="0.000692487" Output="0.000692487" />
  106. <RunTimeInformation>
  107. <RunTimeCountersPerThread Thread="0" ActualRows="8870" ActualEndOfScans="1" ActualExecutions="1" />
  108. </RunTimeInformation>
  109. <Hash>
  110. <DefinedValues />
  111. <HashKeysBuild>
  112. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_ScaleUnits]" Alias="[su]" Column="GUID" />
  113. </HashKeysBuild>
  114. <HashKeysProbe>
  115. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" Column="OBS_ScaleUnitsGUID" />
  116. </HashKeysProbe>
  117. <ProbeResidual>
  118. <ScalarOperator ScalarString="[CMSI_NM].[OBSVEG].[OBS_MeasurementMethodScaleUnits].[OBS_ScaleUnitsGUID] as [mmsu].[OBS_ScaleUnitsGUID]=[CMSI_NM].[OBSVEG].[OBS_ScaleUnits].[GUID] as [su].[GUID]">
  119. <Compare CompareOp="EQ">
  120. <ScalarOperator>
  121. <Identifier>
  122. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" Column="OBS_ScaleUnitsGUID" />
  123. </Identifier>
  124. </ScalarOperator>
  125. <ScalarOperator>
  126. <Identifier>
  127. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_ScaleUnits]" Alias="[su]" Column="GUID" />
  128. </Identifier>
  129. </ScalarOperator>
  130. </Compare>
  131. </ScalarOperator>
  132. </ProbeResidual>
  133. <RelOp AvgRowSize="31" EstimateCPU="0.000377" EstimateIO="0.0068287" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="200" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0072057" TableCardinality="200">
  134. <OutputList>
  135. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_ScaleUnits]" Alias="[su]" Column="GUID" />
  136. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_ScaleUnits]" Alias="[su]" Column="Mean" />
  137. </OutputList>
  138. <RunTimeInformation>
  139. <RunTimeCountersPerThread Thread="0" ActualRows="200" ActualEndOfScans="1" ActualExecutions="1" />
  140. </RunTimeInformation>
  141. <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
  142. <DefinedValues>
  143. <DefinedValue>
  144. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_ScaleUnits]" Alias="[su]" Column="GUID" />
  145. </DefinedValue>
  146. <DefinedValue>
  147. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_ScaleUnits]" Alias="[su]" Column="Mean" />
  148. </DefinedValue>
  149. </DefinedValues>
  150. <Object Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_ScaleUnits]" Index="[PK_OBS_ScaleUnits]" Alias="[su]" IndexKind="Clustered" />
  151. </IndexScan>
  152. </RelOp>
  153. <RelOp AvgRowSize="4474" EstimateCPU="0.218663" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25409.8" LogicalOp="Right Outer Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="142.797">
  154. <OutputList>
  155. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  156. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  157. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="Code" />
  158. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  159. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  160. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  161. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  162. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  163. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  164. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" Column="OBS_ScaleUnitsGUID" />
  165. <ColumnReference Column="Expr1017" />
  166. <ColumnReference Column="Expr1018" />
  167. </OutputList>
  168. <MemoryFractions Input="0.000692487" Output="0.000692487" />
  169. <RunTimeInformation>
  170. <RunTimeCountersPerThread Thread="0" ActualRows="8870" ActualEndOfScans="1" ActualExecutions="1" />
  171. </RunTimeInformation>
  172. <Hash>
  173. <DefinedValues />
  174. <HashKeysBuild>
  175. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" Column="GUID" />
  176. </HashKeysBuild>
  177. <HashKeysProbe>
  178. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  179. </HashKeysProbe>
  180. <ProbeResidual>
  181. <ScalarOperator ScalarString="[CMSI_NM].[OBSVEG].[OBS_Observations].[OBS_MeasurementMethodScaleUnitsGUID] as [o].[OBS_MeasurementMethodScaleUnitsGUID]=[CMSI_NM].[OBSVEG].[OBS_MeasurementMethodScaleUnits].[GUID] as [mmsu].[GUID]">
  182. <Compare CompareOp="EQ">
  183. <ScalarOperator>
  184. <Identifier>
  185. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  186. </Identifier>
  187. </ScalarOperator>
  188. <ScalarOperator>
  189. <Identifier>
  190. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" Column="GUID" />
  191. </Identifier>
  192. </ScalarOperator>
  193. </Compare>
  194. </ScalarOperator>
  195. </ProbeResidual>
  196. <RelOp AvgRowSize="39" EstimateCPU="0.0003935" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="215" LogicalOp="Index Scan" NodeId="4" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00425924" TableCardinality="215">
  197. <OutputList>
  198. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" Column="GUID" />
  199. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" Column="OBS_ScaleUnitsGUID" />
  200. </OutputList>
  201. <RunTimeInformation>
  202. <RunTimeCountersPerThread Thread="0" ActualRows="215" ActualEndOfScans="1" ActualExecutions="1" />
  203. </RunTimeInformation>
  204. <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
  205. <DefinedValues>
  206. <DefinedValue>
  207. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" Column="GUID" />
  208. </DefinedValue>
  209. <DefinedValue>
  210. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" Column="OBS_ScaleUnitsGUID" />
  211. </DefinedValue>
  212. </DefinedValues>
  213. <Object Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_MeasurementMethodScaleUnits]" Index="[UK_OBS_MeasurementMethodScaleUnits]" Alias="[mmsu]" IndexKind="NonClustered" />
  214. </IndexScan>
  215. </RelOp>
  216. <RelOp AvgRowSize="4474" EstimateCPU="0.00253455" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25409.8" LogicalOp="Compute Scalar" NodeId="5" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="142.574">
  217. <OutputList>
  218. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  219. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  220. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  221. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="Code" />
  222. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  223. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  224. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  225. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  226. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  227. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  228. <ColumnReference Column="Expr1017" />
  229. <ColumnReference Column="Expr1018" />
  230. </OutputList>
  231. <ComputeScalar>
  232. <DefinedValues>
  233. <DefinedValue>
  234. <ColumnReference Column="Expr1017" />
  235. <ScalarOperator ScalarString="CONVERT(nvarchar(30),[CMSI_NM].[OBSVEG].[OBS_Sets].[StartDate] as [s].[StartDate],121)">
  236. <Convert DataType="nvarchar" Length="60" Style="121" Implicit="false">
  237. <ScalarOperator>
  238. <Identifier>
  239. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="StartDate" />
  240. </Identifier>
  241. </ScalarOperator>
  242. </Convert>
  243. </ScalarOperator>
  244. </DefinedValue>
  245. <DefinedValue>
  246. <ColumnReference Column="Expr1018" />
  247. <ScalarOperator ScalarString="CONVERT(nvarchar(30),[CMSI_NM].[OBSVEG].[OBS_Sets].[EndDate] as [s].[EndDate],121)">
  248. <Convert DataType="nvarchar" Length="60" Style="121" Implicit="false">
  249. <ScalarOperator>
  250. <Identifier>
  251. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="EndDate" />
  252. </Identifier>
  253. </ScalarOperator>
  254. </Convert>
  255. </ScalarOperator>
  256. </DefinedValue>
  257. </DefinedValues>
  258. <RelOp AvgRowSize="4416" EstimateCPU="0.106265" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25345.5" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="142.571">
  259. <OutputList>
  260. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  261. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  262. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  263. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="Code" />
  264. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="StartDate" />
  265. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="EndDate" />
  266. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  267. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  268. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  269. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  270. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  271. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  272. </OutputList>
  273. <RunTimeInformation>
  274. <RunTimeCountersPerThread Thread="0" ActualRows="8870" ActualEndOfScans="1" ActualExecutions="1" />
  275. </RunTimeInformation>
  276. <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
  277. <OuterReferences>
  278. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_SetsGUID" />
  279. <ColumnReference Column="Expr1027" />
  280. </OuterReferences>
  281. <RelOp AvgRowSize="4374" EstimateCPU="0.177172" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25412" LogicalOp="Inner Join" NodeId="9" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="129.536">
  282. <OutputList>
  283. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  284. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_SetsGUID" />
  285. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  286. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  287. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  288. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  289. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  290. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  291. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  292. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  293. </OutputList>
  294. <RunTimeInformation>
  295. <RunTimeCountersPerThread Thread="0" ActualRows="8870" ActualEndOfScans="1" ActualExecutions="1" />
  296. </RunTimeInformation>
  297. <Merge ManyToMany="false">
  298. <InnerSideJoinColumns>
  299. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_LocationsGUID" />
  300. </InnerSideJoinColumns>
  301. <OuterSideJoinColumns>
  302. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="GUID" />
  303. </OuterSideJoinColumns>
  304. <Residual>
  305. <ScalarOperator ScalarString="[CMSI_NM].[OBSVEG].[OBS_Observations].[OBS_LocationsGUID] as [o].[OBS_LocationsGUID]=[CMSI_NM].[OBSVEG].[OBS_Locations].[GUID] as [l].[GUID]">
  306. <Compare CompareOp="EQ">
  307. <ScalarOperator>
  308. <Identifier>
  309. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_LocationsGUID" />
  310. </Identifier>
  311. </ScalarOperator>
  312. <ScalarOperator>
  313. <Identifier>
  314. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="GUID" />
  315. </Identifier>
  316. </ScalarOperator>
  317. </Compare>
  318. </ScalarOperator>
  319. </Residual>
  320. <RelOp AvgRowSize="4073" EstimateCPU="1.00165" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="54904.2" LogicalOp="Filter" NodeId="10" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="69.2853">
  321. <OutputList>
  322. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="GUID" />
  323. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  324. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  325. </OutputList>
  326. <RunTimeInformation>
  327. <RunTimeCountersPerThread Thread="0" ActualRows="2018554" ActualEndOfScans="0" ActualExecutions="1" />
  328. </RunTimeInformation>
  329. <Filter StartupExpression="false">
  330. <RelOp AvgRowSize="8075" EstimateCPU="0.208677" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2086770" LogicalOp="Compute Scalar" NodeId="11" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="68.2837">
  331. <OutputList>
  332. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="GUID" />
  333. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  334. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  335. <ColumnReference Column="Expr1019" />
  336. </OutputList>
  337. <RunTimeInformation>
  338. <RunTimeCountersPerThread Thread="0" ActualRows="2086263" ActualEndOfScans="0" ActualExecutions="1" />
  339. </RunTimeInformation>
  340. <ComputeScalar>
  341. <DefinedValues>
  342. <DefinedValue>
  343. <ColumnReference Column="Expr1019" />
  344. <ScalarOperator ScalarString="[CMSI_NM].[OBSVEG].[OBS_Locations].[geom] as [l].[geom].STGeometryType()">
  345. <UDTMethod>
  346. <CLRFunction Assembly="Microsoft.SqlServer.Types" Class="Microsoft.SqlServer.Types.SqlGeometry" Method="STGeometryType" />
  347. <ScalarOperator>
  348. <Identifier>
  349. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  350. </Identifier>
  351. </ScalarOperator>
  352. </UDTMethod>
  353. </ScalarOperator>
  354. </DefinedValue>
  355. </DefinedValues>
  356. <RelOp AvgRowSize="4073" EstimateCPU="2.2956" EstimateIO="65.7794" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2086770" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="68.075" TableCardinality="2086770">
  357. <OutputList>
  358. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="GUID" />
  359. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  360. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  361. </OutputList>
  362. <Warnings>
  363. <ColumnsWithNoStatistics>
  364. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  365. </ColumnsWithNoStatistics>
  366. </Warnings>
  367. <RunTimeInformation>
  368. <RunTimeCountersPerThread Thread="0" ActualRows="2086263" ActualEndOfScans="0" ActualExecutions="1" />
  369. </RunTimeInformation>
  370. <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
  371. <DefinedValues>
  372. <DefinedValue>
  373. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="GUID" />
  374. </DefinedValue>
  375. <DefinedValue>
  376. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="Code" />
  377. </DefinedValue>
  378. <DefinedValue>
  379. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Alias="[l]" Column="geom" />
  380. </DefinedValue>
  381. </DefinedValues>
  382. <Object Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Locations]" Index="[PK_OBS_Locations]" Alias="[l]" IndexKind="Clustered" />
  383. </IndexScan>
  384. </RelOp>
  385. </ComputeScalar>
  386. </RelOp>
  387. <Predicate>
  388. <ScalarOperator ScalarString="[Expr1019]=N'Point'">
  389. <Compare CompareOp="EQ">
  390. <ScalarOperator>
  391. <Identifier>
  392. <ColumnReference Column="Expr1019" />
  393. </Identifier>
  394. </ScalarOperator>
  395. <ScalarOperator>
  396. <Const ConstValue="N'Point'" />
  397. </ScalarOperator>
  398. </Compare>
  399. </ScalarOperator>
  400. </Predicate>
  401. </Filter>
  402. </RelOp>
  403. <RelOp AvgRowSize="342" EstimateCPU="1.71978" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25586" LogicalOp="Sort" NodeId="17" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="60.0733">
  404. <OutputList>
  405. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  406. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_SetsGUID" />
  407. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_LocationsGUID" />
  408. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  409. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  410. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  411. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  412. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  413. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  414. </OutputList>
  415. <MemoryFractions Input="0.0738487" Output="0.151951" />
  416. <RunTimeInformation>
  417. <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="25586" ActualEndOfScans="1" ActualExecutions="1" />
  418. </RunTimeInformation>
  419. <Sort Distinct="false">
  420. <OrderBy>
  421. <OrderByColumn Ascending="true">
  422. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_LocationsGUID" />
  423. </OrderByColumn>
  424. </OrderBy>
  425. <RelOp AvgRowSize="342" EstimateCPU="0.116531" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25586" LogicalOp="Inner Join" NodeId="18" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="58.3423">
  426. <OutputList>
  427. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  428. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_SetsGUID" />
  429. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_LocationsGUID" />
  430. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  431. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  432. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  433. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  434. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  435. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  436. </OutputList>
  437. <RunTimeInformation>
  438. <RunTimeCountersPerThread Thread="0" ActualRows="25586" ActualEndOfScans="1" ActualExecutions="1" />
  439. </RunTimeInformation>
  440. <Merge ManyToMany="false">
  441. <InnerSideJoinColumns>
  442. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  443. </InnerSideJoinColumns>
  444. <OuterSideJoinColumns>
  445. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="Code" />
  446. </OuterSideJoinColumns>
  447. <Residual>
  448. <ScalarOperator ScalarString="[CMSI_NM].[OBSVEG].[OBS_RPT_ReportObservations].[OriginalTaxonCode] as [ro].[OriginalTaxonCode]=[CMSI_NM].[OBSVEG].[OBS_Taxon].[Code] as [t2].[Code]">
  449. <Compare CompareOp="EQ">
  450. <ScalarOperator>
  451. <Identifier>
  452. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  453. </Identifier>
  454. </ScalarOperator>
  455. <ScalarOperator>
  456. <Identifier>
  457. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="Code" />
  458. </Identifier>
  459. </ScalarOperator>
  460. </Compare>
  461. </ScalarOperator>
  462. </Residual>
  463. <RelOp AvgRowSize="122" EstimateCPU="0.028779" EstimateIO="0.88831" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="26020" LogicalOp="Clustered Index Scan" NodeId="19" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.917089" TableCardinality="26020">
  464. <OutputList>
  465. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="Code" />
  466. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  467. </OutputList>
  468. <RunTimeInformation>
  469. <RunTimeCountersPerThread Thread="0" ActualRows="8243" ActualEndOfScans="0" ActualExecutions="1" />
  470. </RunTimeInformation>
  471. <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
  472. <DefinedValues>
  473. <DefinedValue>
  474. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="Code" />
  475. </DefinedValue>
  476. <DefinedValue>
  477. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t2]" Column="TaxonName" />
  478. </DefinedValue>
  479. </DefinedValues>
  480. <Object Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Index="[PK_OBS_Taxon]" Alias="[t2]" IndexKind="Clustered" />
  481. </IndexScan>
  482. </RelOp>
  483. <RelOp AvgRowSize="239" EstimateCPU="0.106949" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25586" LogicalOp="Inner Join" NodeId="20" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="57.3087">
  484. <OutputList>
  485. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  486. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_SetsGUID" />
  487. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_LocationsGUID" />
  488. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  489. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  490. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  491. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  492. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  493. </OutputList>
  494. <RunTimeInformation>
  495. <RunTimeCountersPerThread Thread="0" ActualRows="25586" ActualEndOfScans="1" ActualExecutions="1" />
  496. </RunTimeInformation>
  497. <NestedLoops Optimized="false" WithOrderedPrefetch="true">
  498. <OuterReferences>
  499. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OBS_ObservationsGUID" />
  500. <ColumnReference Column="Expr1026" />
  501. </OuterReferences>
  502. <RelOp AvgRowSize="151" EstimateCPU="1.71978" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25586" LogicalOp="Sort" NodeId="22" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="4.96391">
  503. <OutputList>
  504. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OBS_ObservationsGUID" />
  505. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  506. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  507. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  508. </OutputList>
  509. <MemoryFractions Input="0.0379878" Output="0.0781026" />
  510. <RunTimeInformation>
  511. <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="25586" ActualEndOfScans="1" ActualExecutions="1" />
  512. </RunTimeInformation>
  513. <Sort Distinct="false">
  514. <OrderBy>
  515. <OrderByColumn Ascending="true">
  516. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  517. </OrderByColumn>
  518. </OrderBy>
  519. <RelOp AvgRowSize="151" EstimateCPU="0.116531" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25586" LogicalOp="Inner Join" NodeId="23" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="3.23287">
  520. <OutputList>
  521. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OBS_ObservationsGUID" />
  522. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  523. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  524. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  525. </OutputList>
  526. <RunTimeInformation>
  527. <RunTimeCountersPerThread Thread="0" ActualRows="25586" ActualEndOfScans="1" ActualExecutions="1" />
  528. </RunTimeInformation>
  529. <Merge ManyToMany="false">
  530. <InnerSideJoinColumns>
  531. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  532. </InnerSideJoinColumns>
  533. <OuterSideJoinColumns>
  534. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="Code" />
  535. </OuterSideJoinColumns>
  536. <Residual>
  537. <ScalarOperator ScalarString="[CMSI_NM].[OBSVEG].[OBS_RPT_ReportObservations].[ReportTaxonCode] as [ro].[ReportTaxonCode]=[CMSI_NM].[OBSVEG].[OBS_Taxon].[Code] as [t1].[Code]">
  538. <Compare CompareOp="EQ">
  539. <ScalarOperator>
  540. <Identifier>
  541. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  542. </Identifier>
  543. </ScalarOperator>
  544. <ScalarOperator>
  545. <Identifier>
  546. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="Code" />
  547. </Identifier>
  548. </ScalarOperator>
  549. </Compare>
  550. </ScalarOperator>
  551. </Residual>
  552. <RelOp AvgRowSize="122" EstimateCPU="0.028779" EstimateIO="0.88831" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="26020" LogicalOp="Clustered Index Scan" NodeId="24" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.917089" TableCardinality="26020">
  553. <OutputList>
  554. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="Code" />
  555. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  556. </OutputList>
  557. <RunTimeInformation>
  558. <RunTimeCountersPerThread Thread="0" ActualRows="8243" ActualEndOfScans="0" ActualExecutions="1" />
  559. </RunTimeInformation>
  560. <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
  561. <DefinedValues>
  562. <DefinedValue>
  563. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="Code" />
  564. </DefinedValue>
  565. <DefinedValue>
  566. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Alias="[t1]" Column="TaxonName" />
  567. </DefinedValue>
  568. </DefinedValues>
  569. <Object Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Taxon]" Index="[PK_OBS_Taxon]" Alias="[t1]" IndexKind="Clustered" />
  570. </IndexScan>
  571. </RelOp>
  572. <RelOp AvgRowSize="49" EstimateCPU="1.71978" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25586" LogicalOp="Sort" NodeId="25" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2.19925">
  573. <OutputList>
  574. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OBS_ObservationsGUID" />
  575. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  576. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  577. </OutputList>
  578. <MemoryFractions Input="0.0401148" Output="0.0401148" />
  579. <RunTimeInformation>
  580. <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="25586" ActualEndOfScans="1" ActualExecutions="1" />
  581. </RunTimeInformation>
  582. <Sort Distinct="false">
  583. <OrderBy>
  584. <OrderByColumn Ascending="true">
  585. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  586. </OrderByColumn>
  587. </OrderBy>
  588. <RelOp AvgRowSize="65" EstimateCPU="0.055674" EstimateIO="0.38831" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25586" LogicalOp="Clustered Index Scan" NodeId="26" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.443984" TableCardinality="50470">
  589. <OutputList>
  590. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OBS_ObservationsGUID" />
  591. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  592. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  593. </OutputList>
  594. <RunTimeInformation>
  595. <RunTimeCountersPerThread Thread="0" ActualRows="25586" ActualEndOfScans="1" ActualExecutions="1" />
  596. </RunTimeInformation>
  597. <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
  598. <DefinedValues>
  599. <DefinedValue>
  600. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OBS_ObservationsGUID" />
  601. </DefinedValue>
  602. <DefinedValue>
  603. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OriginalTaxonCode" />
  604. </DefinedValue>
  605. <DefinedValue>
  606. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportTaxonCode" />
  607. </DefinedValue>
  608. </DefinedValues>
  609. <Object Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Index="[PK_OBS_RPT_ReportObservations]" Alias="[ro]" IndexKind="Clustered" />
  610. <Predicate>
  611. <ScalarOperator ScalarString="[CMSI_NM].[OBSVEG].[OBS_RPT_ReportObservations].[ReportGUID] as [ro].[ReportGUID]={guid'64C0A4AF-EE4D-4E83-A194-2A14E8A6AB0E'}">
  612. <Compare CompareOp="EQ">
  613. <ScalarOperator>
  614. <Identifier>
  615. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="ReportGUID" />
  616. </Identifier>
  617. </ScalarOperator>
  618. <ScalarOperator>
  619. <Const ConstValue="{guid'64C0A4AF-EE4D-4E83-A194-2A14E8A6AB0E'}" />
  620. </ScalarOperator>
  621. </Compare>
  622. </ScalarOperator>
  623. </Predicate>
  624. </IndexScan>
  625. </RelOp>
  626. </Sort>
  627. </RelOp>
  628. </Merge>
  629. </RelOp>
  630. </Sort>
  631. </RelOp>
  632. <RelOp AvgRowSize="113" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="25584.4" EstimateRewinds="0.579318" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="28" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="52.2378" TableCardinality="3242990">
  633. <OutputList>
  634. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  635. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_SetsGUID" />
  636. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_LocationsGUID" />
  637. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  638. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  639. </OutputList>
  640. <RunTimeInformation>
  641. <RunTimeCountersPerThread Thread="0" ActualRows="25586" ActualEndOfScans="0" ActualExecutions="25586" />
  642. </RunTimeInformation>
  643. <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
  644. <DefinedValues>
  645. <DefinedValue>
  646. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="Code" />
  647. </DefinedValue>
  648. <DefinedValue>
  649. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_SetsGUID" />
  650. </DefinedValue>
  651. <DefinedValue>
  652. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_LocationsGUID" />
  653. </DefinedValue>
  654. <DefinedValue>
  655. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_MeasurementMethodScaleUnitsGUID" />
  656. </DefinedValue>
  657. <DefinedValue>
  658. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="NumberObserved" />
  659. </DefinedValue>
  660. </DefinedValues>
  661. <Object Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Index="[PK_OBS_Observations]" Alias="[o]" IndexKind="Clustered" />
  662. <SeekPredicates>
  663. <SeekPredicateNew>
  664. <SeekKeys>
  665. <Prefix ScanType="EQ">
  666. <RangeColumns>
  667. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="GUID" />
  668. </RangeColumns>
  669. <RangeExpressions>
  670. <ScalarOperator ScalarString="[CMSI_NM].[OBSVEG].[OBS_RPT_ReportObservations].[OBS_ObservationsGUID] as [ro].[OBS_ObservationsGUID]">
  671. <Identifier>
  672. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_RPT_ReportObservations]" Alias="[ro]" Column="OBS_ObservationsGUID" />
  673. </Identifier>
  674. </ScalarOperator>
  675. </RangeExpressions>
  676. </Prefix>
  677. </SeekKeys>
  678. </SeekPredicateNew>
  679. </SeekPredicates>
  680. </IndexScan>
  681. </RelOp>
  682. </NestedLoops>
  683. </RelOp>
  684. </Merge>
  685. </RelOp>
  686. </Sort>
  687. </RelOp>
  688. </Merge>
  689. </RelOp>
  690. <RelOp AvgRowSize="67" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="25402.1" EstimateRewinds="8.93512" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="31" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="12.9231" TableCardinality="258446">
  691. <OutputList>
  692. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="Code" />
  693. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="StartDate" />
  694. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="EndDate" />
  695. </OutputList>
  696. <RunTimeInformation>
  697. <RunTimeCountersPerThread Thread="0" ActualRows="8870" ActualEndOfScans="0" ActualExecutions="8870" />
  698. </RunTimeInformation>
  699. <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
  700. <DefinedValues>
  701. <DefinedValue>
  702. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="Code" />
  703. </DefinedValue>
  704. <DefinedValue>
  705. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="StartDate" />
  706. </DefinedValue>
  707. <DefinedValue>
  708. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="EndDate" />
  709. </DefinedValue>
  710. </DefinedValues>
  711. <Object Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Index="[PK_OBS_Sets]" Alias="[s]" IndexKind="Clustered" />
  712. <SeekPredicates>
  713. <SeekPredicateNew>
  714. <SeekKeys>
  715. <Prefix ScanType="EQ">
  716. <RangeColumns>
  717. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Sets]" Alias="[s]" Column="GUID" />
  718. </RangeColumns>
  719. <RangeExpressions>
  720. <ScalarOperator ScalarString="[CMSI_NM].[OBSVEG].[OBS_Observations].[OBS_SetsGUID] as [o].[OBS_SetsGUID]">
  721. <Identifier>
  722. <ColumnReference Database="[CMSI_NM]" Schema="[OBSVEG]" Table="[OBS_Observations]" Alias="[o]" Column="OBS_SetsGUID" />
  723. </Identifier>
  724. </ScalarOperator>
  725. </RangeExpressions>
  726. </Prefix>
  727. </SeekKeys>
  728. </SeekPredicateNew>
  729. </SeekPredicates>
  730. </IndexScan>
  731. </RelOp>
  732. </NestedLoops>
  733. </RelOp>
  734. </ComputeScalar>
  735. </RelOp>
  736. </Hash>
  737. </RelOp>
  738. </Hash>
  739. </RelOp>
  740. </ComputeScalar>
  741. </RelOp>
  742. </QueryPlan>
  743. </StmtSimple>
  744. </Statements>
  745. </Batch>
  746. </BatchSequence>
  747. </ShowPlanXML>
RAW Paste Data