SQL Server 2005中UNION的性能很差（非完成）

Warning: this is the actual code generated from my system:

警告:这是我系统生成的实际代码:

;WITH RESULTS AS (
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'CountFocusRecords' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK)
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = m.APPL_CD
AND cat.ALBASE = m.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
GROUP BY m.APPL_CD, m.ALBASE
UNION
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'CountBiminiRecords' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_STATINV] AS c WITH(NOLOCK)
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = c.APPL_CD
AND cat.ALBASE = c.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
GROUP BY c.APPL_CD, c.ALBASE
UNION
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'RecordsInFocusMissingInBimini' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK)
LEFT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)
ON m.[YEAR] = c.[YEAR]
    AND m.[MONTH] = c.[MONTH]
    AND m.[BANK_NO] = c.[BANK_NO]
    AND m.[COST_CENTER] = c.[COST_CENTER]
    AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]
    AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]
    AND m.[APPL_CD] = c.[APPL_CD]
    AND m.[ALBASE] = c.[ALBASE]
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = m.APPL_CD
AND cat.ALBASE = m.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
WHERE c.[YEAR] IS NULL
GROUP BY m.APPL_CD, m.ALBASE
UNION
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'RecordsInBiminiMissingInFocus' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK)
RIGHT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)
ON m.[YEAR] = c.[YEAR]
    AND m.[MONTH] = c.[MONTH]
    AND m.[BANK_NO] = c.[BANK_NO]
    AND m.[COST_CENTER] = c.[COST_CENTER]
    AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]
    AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]
    AND m.[APPL_CD] = c.[APPL_CD]
    AND m.[ALBASE] = c.[ALBASE]
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = c.APPL_CD
AND cat.ALBASE = c.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
WHERE m.[YEAR] IS NULL
GROUP BY c.APPL_CD, c.ALBASE
) SELECT * FROM RESULTS ORDER BY Program, APPL_CD, ALBASE, Measure

The code just sits there, no locking or blocking.

代码就在那里,没有锁定或阻塞。

The individual components of the UNION return in a few seconds each. The code works in general for checking the output results of all the other programs in the STAT group, but just halts for this one.

UNION的各个组件每个都会在几秒钟内返回。该代码通常用于检查STAT组中所有其他程序的输出结果,但只是停止这个。

Remove the CTE, no effect, sits there for 30 minutes/an hour, however long you care to wait before cancelling.

取出CTE,没有效果,坐在那里30分钟/小时,无论你需要等多久才能取消。

Remove the UNION, and the 4 result sets return in 11 seconds, total of 19 records accross all 4 result sets.

删除UNION,4个结果集在11秒内返回,所有4个结果集共有19个记录。

Run just the first two together - works fine, run just the last 2 together, also fine. First 3 together, fine, too.

只运行前两个 - 工作正常,只运行最后两个,也很好。前三个一起,也很好。

I've already modified the code to output these to a #temp table, for other requirements, so I'm just going to change it to output each to the #temp table in sequence, but I have never seen SQL just stop like that with no evidence of blocking or anything.

我已经修改了代码以将这些输出到#temp表,以满足其他要求,所以我只是将它更改为按顺序将每个输出到#temp表,但我从未见过SQL就这样停止没有阻塞或任何东西的证据。

3 个解决方案

#1

Change to UNION ALL, since you'll never have dupes (the Measure column is hard coded to be different). UNION must first sort the rows, and then find dupes and eliminate.

更改为UNION ALL,因为您永远不会有欺骗(测量列被硬编码为不同)。 UNION必须首先对行进行排序,然后找到dupes并消除。

My real guess is it's a parallelization issue. Try adding OPTION (MAXDOP 1) at the end.

我的真正猜测是它是一个并行化问题。尝试在最后添加OPTION(MAXDOP 1)。

#2

If you can post the query execution plan in XML format, that'll help us determine what parts of the query are causing problems. In SSMS, click Query, Display Estimated Execution Plan, and when it comes up, right-click on it and save as XML.

如果您可以以XML格式发布查询执行计划,那将帮助我们确定查询的哪些部分导致问题。在SSMS中,单击“查询”,“显示估计的执行计划”,当它出现时,右键单击它并保存为XML。

#3

I've moved on to regression testing 200808, but the fundamental query is the same, with a different batchrunid and different known good table.

我已经转向回归测试200808,但基本查询是相同的,具有不同的batchrunid和不同的已知良好表。

<?xml version="1.0"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3239.00">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="&#13;&#10;;WITH RESULTS AS (&#13;&#10;SELECT 1251 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'CountFocusRecords' AS Measure, COUNT(*) AS Value&#13;&#10;FROM [MISWork].[SX_FOCUS_NATIVE_200808] AS m WITH(NOLOCK)&#13;&#10;INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)&#13;&#10;ON cat.APPL_CD = m.APPL_CD&#13;&#10;AND cat.ALBASE = m.ALBASE&#13;&#10;AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'&#13;&#10;GROUP BY m.APPL_CD, m.ALBASE&#13;&#10;UNION&#13;&#10;SELECT 1251 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'CountBiminiRecords' AS Measure, COUNT(*) AS Value&#13;&#10;FROM [MISWork].[SX_STATINV] AS c WITH(NOLOCK)&#13;&#10;INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)&#13;&#10;ON cat.APPL_CD = c.APPL_CD&#13;&#10;AND cat.ALBASE = c.ALBASE&#13;&#10;AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'&#13;&#10;GROUP BY c.APPL_CD, c.ALBASE&#13;&#10;UNION&#13;&#10;SELECT 1251 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'RecordsInFocusMissingInBimini' AS Measure, COUNT(*) AS Value&#13;&#10;FROM [MISWork].[SX_FOCUS_NATIVE_200808] AS m WITH(NOLOCK)&#13;&#10;LEFT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)&#13;&#10;ON m.[YEAR] = c.[YEAR]&#13;&#10;    AND m.[MONTH] = c.[MONTH]&#13;&#10;    AND m.[BANK_NO] = c.[BANK_NO]&#13;&#10;    AND m.[COST_CENTER] = c.[COST_CENTER]&#13;&#10;    AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]&#13;&#10;    AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]&#13;&#10;    AND m.[APPL_CD] = c.[APPL_CD]&#13;&#10;    AND m.[ALBASE] = c.[ALBASE]&#13;&#10;INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)&#13;&#10;ON cat.APPL_CD = m.APPL_CD&#13;&#10;AND cat.ALBASE = m.ALBASE&#13;&#10;AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'&#13;&#10;WHERE c.[YEAR] IS NULL&#13;&#10;GROUP BY m.APPL_CD, m.ALBASE&#13;&#10;UNION&#13;&#10;SELECT 1251 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'RecordsInBiminiMissingInFocus' AS Measure, COUNT(*) AS Value&#13;&#10;FROM [MISWork].[SX_FOCUS_NATIVE_200808] AS m WITH(NOLOCK)&#13;&#10;RIGHT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)&#13;&#10;ON m.[YEAR] = c.[YEAR]&#13;&#10;    AND m.[MONTH] = c.[MONTH]&#13;&#10;    AND m.[BANK_NO] = c.[BANK_NO]&#13;&#10;    AND m.[COST_CENTER] = c.[COST_CENTER]&#13;&#10;    AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]&#13;&#10;    AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]&#13;&#10;    AND m.[APPL_CD] = c.[APPL_CD]&#13;&#10;    AND m.[ALBASE] = c.[ALBASE]&#13;&#10;INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)&#13;&#10;ON cat.APPL_CD = c.APPL_CD&#13;&#10;AND cat.ALBASE = c.ALBASE&#13;&#10;AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'&#13;&#10;WHERE m.[YEAR] IS NULL&#13;&#10;GROUP BY c.APPL_CD, c.ALBASE&#13;&#10;) SELECT * FROM RESULTS ORDER BY Program, APPL_CD, ALBASE, Measure&#13;&#10;&#13;&#10;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="1209.5" StatementEstRows="13965.1" StatementOptmLevel="FULL">
          <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false"/>
          <QueryPlan CachedPlanSize="504" CompileTime="1244" CompileCPU="1099" CompileMemory="5016">
            <MissingIndexes>
              <MissingIndexGroup Impact="29.2539">
                <MissingIndex Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[APPL_CD]" ColumnId="7"/>
                    <Column Name="[ALBASE]" ColumnId="8"/>
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[YEAR]" ColumnId="1"/>
                    <Column Name="[MONTH]" ColumnId="2"/>
                    <Column Name="[BANK_NO]" ColumnId="3"/>
                    <Column Name="[COST_CENTER]" ColumnId="4"/>
                    <Column Name="[GLACCOUNT_NO]" ColumnId="5"/>
                    <Column Name="[CUSTACCOUNT]" ColumnId="6"/>
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
              <MissingIndexGroup Impact="29.6796">
                <MissingIndex Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[APPL_CD]" ColumnId="7"/>
                    <Column Name="[ALBASE]" ColumnId="8"/>
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <RelOp NodeId="0" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="13965.1" EstimateIO="0" EstimateCPU="0.121489" AvgRowSize="45" EstimatedTotalSubtreeCost="1209.5" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Column="Union1039"/>
                <ColumnReference Column="Union1040"/>
                <ColumnReference Column="Union1041"/>
                <ColumnReference Column="Union1042"/>
                <ColumnReference Column="Union1043"/>
                <ColumnReference Column="Union1044"/>
              </OutputList>
              <Parallelism>
                <OrderBy>
                  <OrderByColumn Ascending="1">
                    <ColumnReference Column="Union1041"/>
                  </OrderByColumn>
                  <OrderByColumn Ascending="1">
                    <ColumnReference Column="Union1042"/>
                  </OrderByColumn>
                  <OrderByColumn Ascending="1">
                    <ColumnReference Column="Union1043"/>
                  </OrderByColumn>
                </OrderBy>
                <RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="13965.1" EstimateIO="0.00281532" EstimateCPU="0.220682" AvgRowSize="45" EstimatedTotalSubtreeCost="1209.37" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Column="Union1039"/>
                    <ColumnReference Column="Union1040"/>
                    <ColumnReference Column="Union1041"/>
                    <ColumnReference Column="Union1042"/>
                    <ColumnReference Column="Union1043"/>
                    <ColumnReference Column="Union1044"/>
                  </OutputList>
                  <MemoryFractions Input="0.0191727" Output="1"/>
                  <Sort Distinct="0">
                    <OrderBy>
                      <OrderByColumn Ascending="1">
                        <ColumnReference Column="Union1041"/>
                      </OrderByColumn>
                      <OrderByColumn Ascending="1">
                        <ColumnReference Column="Union1042"/>
                      </OrderByColumn>
                      <OrderByColumn Ascending="1">
                        <ColumnReference Column="Union1043"/>
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp NodeId="2" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="13965.1" EstimateIO="0" EstimateCPU="0.000349132" AvgRowSize="45" EstimatedTotalSubtreeCost="1209.15" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Column="Union1039"/>
                        <ColumnReference Column="Union1040"/>
                        <ColumnReference Column="Union1041"/>
                        <ColumnReference Column="Union1042"/>
                        <ColumnReference Column="Union1043"/>
                        <ColumnReference Column="Union1044"/>
                      </OutputList>
                      <Concat>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Union1039"/>
                            <ColumnReference Column="Expr1006"/>
                            <ColumnReference Column="Expr1014"/>
                            <ColumnReference Column="Expr1025"/>
                            <ColumnReference Column="Expr1036"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1040"/>
                            <ColumnReference Column="Expr1007"/>
                            <ColumnReference Column="Expr1015"/>
                            <ColumnReference Column="Expr1026"/>
                            <ColumnReference Column="Expr1037"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1041"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_STATINV]" Alias="[c]" Column="APPL_CD"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_STATINV]" Alias="[c]" Column="APPL_CD"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1042"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_STATINV]" Alias="[c]" Column="ALBASE"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_STATINV]" Alias="[c]" Column="ALBASE"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1043"/>
                            <ColumnReference Column="Expr1008"/>
                            <ColumnReference Column="Expr1016"/>
                            <ColumnReference Column="Expr1027"/>
                            <ColumnReference Column="Expr1038"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1044"/>
                            <ColumnReference Column="Expr1005"/>
                            <ColumnReference Column="Expr1013"/>
                            <ColumnReference Column="Expr1024"/>
                            <ColumnReference Column="Expr1035"/>
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="7140" EstimateIO="0" EstimateCPU="0.0001785" AvgRowSize="42" EstimatedTotalSubtreeCost="362.728" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                            <ColumnReference Column="Expr1005"/>
                            <ColumnReference Column="Expr1006"/>
                            <ColumnReference Column="Expr1007"/>
                            <ColumnReference Column="Expr1008"/>
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1006"/>
                                <ScalarOperator ScalarString="(1251)">
                                  <Const ConstValue="(1251)"/>
                                </ScalarOperator>
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="Expr1007"/>
                                <ScalarOperator ScalarString="'STATINV'">
                                  <Const ConstValue="'STATINV'"/>
                                </ScalarOperator>
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="Expr1008"/>
                                <ScalarOperator ScalarString="'CountFocusRecords'">
                                  <Const ConstValue="'CountFocusRecords'"/>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="7140" EstimateIO="0" EstimateCPU="0.0001785" AvgRowSize="23" EstimatedTotalSubtreeCost="362.728" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                              <OutputList>
                                <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                <ColumnReference Column="Expr1005"/>
                              </OutputList>
                              <ComputeScalar>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1005"/>
                                    <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[globalagg1083],0)">
                                      <Convert DataType="int" Style="0" Implicit="1">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Column="globalagg1083"/>
                                          </Identifier>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <RelOp NodeId="7" PhysicalOp="Hash Match" LogicalOp="Aggregate" EstimateRows="7140" EstimateIO="0" EstimateCPU="0.114864" AvgRowSize="27" EstimatedTotalSubtreeCost="362.728" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                                  <OutputList>
                                    <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                    <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                    <ColumnReference Column="globalagg1083"/>
                                  </OutputList>
                                  <MemoryFractions Input="0.5" Output="0.980827"/>
                                  <Hash>
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Column="globalagg1083"/>
                                        <ScalarOperator ScalarString="SUM([partialagg1082])">
                                          <Aggregate Distinct="0" AggType="SUM">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Column="partialagg1082"/>
                                              </Identifier>
                                            </ScalarOperator>
                                          </Aggregate>
                                        </ScalarOperator>
                                      </DefinedValue>
                                    </DefinedValues>
                                    <HashKeysBuild>
                                      <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                      <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                    </HashKeysBuild>
                                    <BuildResidual>
                                      <ScalarOperator ScalarString="[DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[APPL_CD] as [m].[APPL_CD] = [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[APPL_CD] as [m].[APPL_CD] AND [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[ALBASE] as [m].[ALBASE] = [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[ALBASE] as [m].[ALBASE]">
                                        <Logical Operation="AND">
                                          <ScalarOperator>
                                            <Compare CompareOp="IS">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Compare CompareOp="IS">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </Logical>
                                      </ScalarOperator>
                                    </BuildResidual>
                                    <RelOp NodeId="8" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="28560" EstimateIO="0" EstimateCPU="0.0614707" AvgRowSize="27" EstimatedTotalSubtreeCost="362.613" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                                      <OutputList>
                                        <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                        <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                        <ColumnReference Column="partialagg1082"/>
                                      </OutputList>
                                      <Parallelism PartitioningType="Hash">
                                        <PartitionColumns>
                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                        </PartitionColumns>
                                        <RelOp NodeId="9" PhysicalOp="Hash Match" LogicalOp="Partial Aggregate" EstimateRows="28560" EstimateIO="0" EstimateCPU="1.7277" AvgRowSize="27" EstimatedTotalSubtreeCost="362.551" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                                          <OutputList>
                                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                            <ColumnReference Column="partialagg1082"/>
                                          </OutputList>
                                          <MemoryFractions Input="0" Output="0"/>
                                          <Hash>
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Column="partialagg1082"/>
                                                <ScalarOperator ScalarString="COUNT(*)">
                                                  <Aggregate Distinct="0" AggType="COUNT*"/>
                                                </ScalarOperator>
                                              </DefinedValue>
                                            </DefinedValues>
                                            <HashKeysBuild>
                                              <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                              <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                            </HashKeysBuild>
                                            <BuildResidual>
                                              <ScalarOperator ScalarString="[DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[APPL_CD] as [m].[APPL_CD] = [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[APPL_CD] as [m].[APPL_CD] AND [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[ALBASE] as [m].[ALBASE] = [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[ALBASE] as [m].[ALBASE]">
                                                <Logical Operation="AND">
                                                  <ScalarOperator>
                                                    <Compare CompareOp="IS">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </Compare>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Compare CompareOp="IS">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </Compare>
                                                  </ScalarOperator>
                                                </Logical>
                                              </ScalarOperator>
                                            </BuildResidual>
                                            <RelOp NodeId="10" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="879583" EstimateIO="0" EstimateCPU="62.0602" AvgRowSize="19" EstimatedTotalSubtreeCost="360.824" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                                              <OutputList>
                                                <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                              </OutputList>
                                              <MemoryFractions Input="1" Output="0.5"/>
                                              <Hash>
                                                <DefinedValues/>
                                                <HashKeysBuild>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISProcess]" Table="[SXProcessCatalog]" Alias="[cat]" Column="APPL_CD"/>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISProcess]" Table="[SXProcessCatalog]" Alias="[cat]" Column="ALBASE"/>
                                                </HashKeysBuild>
                                                <HashKeysProbe>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                </HashKeysProbe>
                                                <ProbeResidual>