Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chris_cs
Posting Yak Master
223 Posts |
 Posted - 2011-06-20 : 04:04:30
|
Hi,I have a query that someone has created and it caused some issues with our system this morning. I noticed it because when I ran sp_who2 the stats were not good!Here is the query:SELECTc.IRN as JOBNO, w.TRANSDATE, n2.NAMECODE as AC,n2.NAME as AC_NAME, w.TRANSTYPE, w.WIPCODE, w2.DESCRIPTION As WIPDESC,w2.WIPTYPEID As WIPTYPE,e.ABBREVIATEDNAME as FEE_EARNER, w.TOTALUNITS, DATEDIFF(MINUTE,'1899-01-01 00:00:00', w.TOTALTIME) as RECTIME, CAST(COALESCE(w.LOCALTRANSVALUE,0) as MONEY) as GROSS, COST = Case When w2.WIPTYPEID In ('OVERHD', 'CONV', 'ADV') Then Cast(0 As MONEY) Else Cast(Coalesce(w.LOCALCOST, 0) As MONEY) End,NET = Case When w2.WIPTYPEID In ('OVERHD', 'CONV', 'ADV') Then Cast(Coalesce(w.LOCALTRANSVALUE, 0) As MONEY) Else Cast(Coalesce(w.LOCALTRANSVALUE, 0) - Coalesce(w.LOCALCOST, 0) As MONEY) End,n.NAMECODE AS JOB_PARTNER, n3.NAMECODE AS JOB_FE, n4.NAMECODE as INSTRUCTOR,w.INVOICENUMBER,w.REASONCODE,REASON.DESCRIPTIONFROM WORKHISTORY As w Inner Join EMPLOYEE As e On w.EMPLOYEENO = e.EMPLOYEENO Left Outer Join CASES As c On c.CASEID = w.CASEID Left Outer Join CASENAME As cn On cn.CASEID = c.CASEID And cn.NAMETYPE = 'SIG' Left Outer Join NAME As n On n.NAMENO = cn.NAMENO Left Outer Join CASENAME As ac On ac.CASEID = c.CASEID And ac.NAMETYPE = 'D' Left Outer Join NAME As n2 On n2.NAMENO = ac.NAMENO Left Outer Join CASENAME As fe On fe.CASEID = c.CASEID And fe.NAMETYPE = 'EMP' Left Outer Join NAME As n3 On n3.NAMENO = fe.NAMENO Left Outer Join CASENAME As ins On ins.CASEID = c.CASEID And ins.NAMETYPE = 'I' Left Outer Join NAME As n4 On n4.NAMENO = ins.NAMENO Inner Join WIPTEMPLATE As w2 On w2.WIPCODE = w.WIPCODELeft Join REASON On REASON.REASONCODE = w.REASONCODEWHERE w.TRANSTYPE<>510 and W.commandid<>9When I look at the execution plan, there is a clustered index scan on the workhistory table which has a cost of 26%. This part of the query is returning over 600k rows so it looks like this is the issue.I just wondered if there is anything you can see in the syntax that could be improved?I have rebuilt the index on one of our test servers but the cost is still shown at 26%.Any advice would b much appreciated |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-06-20 : 05:06:07
|
| what indices do you have? can you post the execution plan?don't focus too much on the 26% part. it's only a split of the effort in the query which has to add up to 100%. resolving this may only move the 26% somewhere else. |
 |
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-06-20 : 05:16:45
|
I just thought the 26% was the best way to start. I wasn't sure the best way to post the query plan so here is the XML:<?xml version="1.0" encoding="utf-16"?><ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.4000.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="560152" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="149.732" StatementText="SELECT#xD;#xA;c.IRN as JOBNO, #xD;#xA;w.TRANSDATE, #xD;#xA;n2.NAMECODE as AC,#xD;#xA;n2.NAME as AC_NAME, #xD;#xA;w.TRANSTYPE, #xD;#xA;w.WIPCODE, #xD;#xA;w2.DESCRIPTION As WIPDESC,#xD;#xA;w2.WIPTYPEID As WIPTYPE,#xD;#xA;e.ABBREVIATEDNAME as FEE_EARNER, #xD;#xA;w.TOTALUNITS, #xD;#xA;--DATEDIFF(MINUTE,'1899-01-01 00:00:00', w.TOTALTIME) as RECTIME, #xD;#xA;CAST(COALESCE(w.LOCALTRANSVALUE,0) as MONEY) as GROSS, #xD;#xA;COST = Case#xD;#xA; When w2.WIPTYPEID In ('OVERHD', 'CONV', 'ADV') Then Cast(0 As MONEY)#xD;#xA; Else Cast(Coalesce(w.LOCALCOST, 0) As MONEY) End,#xD;#xA;NET = Case#xD;#xA; When w2.WIPTYPEID In ('OVERHD', 'CONV',#xD;#xA; 'ADV') Then Cast(Coalesce(w.LOCALTRANSVALUE, 0) As MONEY)#xD;#xA; Else Cast(Coalesce(w.LOCALTRANSVALUE, 0) - Coalesce(w.LOCALCOST,#xD;#xA; 0) As MONEY) End,#xD;#xA;n.NAMECODE AS JOB_PARTNER, #xD;#xA;n3.NAMECODE AS JOB_FE, #xD;#xA;n4.NAMECODE as INSTRUCTOR#xD;#xA;--w.INVOICENUMBER,#xD;#xA;--w.REASONCODE,#xD;#xA;--REASON.DESCRIPTION#xD;#xA;FROM #xD;#xA; WORKHISTORY As w Inner Join#xD;#xA; EMPLOYEE As e On w.EMPLOYEENO = e.EMPLOYEENO Left Outer Join#xD;#xA; CASES As c On c.CASEID = w.CASEID Left Outer Join#xD;#xA; CASENAME As cn On cn.CASEID = c.CASEID And cn.NAMETYPE = 'SIG' Left Outer Join#xD;#xA; NAME As n On n.NAMENO = cn.NAMENO Left Outer Join#xD;#xA; CASENAME As ac On ac.CASEID = c.CASEID And ac.NAMETYPE = 'D' Left Outer Join#xD;#xA; NAME As n2 On n2.NAMENO = ac.NAMENO Left Outer Join#xD;#xA; CASENAME As fe On fe.CASEID = c.CASEID And fe.NAMETYPE = 'EMP' Left Outer Join#xD;#xA; NAME As n3 On n3.NAMENO = fe.NAMENO Left Outer Join#xD;#xA; CASENAME As ins On ins.CASEID = c.CASEID And ins.NAMETYPE = 'I' Left Outer Join#xD;#xA; NAME As n4 On n4.NAMENO = ins.NAMENO Inner Join#xD;#xA; WIPTEMPLATE As w2 On w2.WIPCODE = w.WIPCODE#xD;#xA;Left Join#xD;#xA; REASON On REASON.REASONCODE = w.REASONCODE#xD;#xA;WHERE w.TRANSTYPE<>510 and W.commandid<>9#xD;#xA;" StatementType="SELECT" QueryHash="0x2EA5F632BAED5A2C" QueryPlanHash="0x0928F020296546C8"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan CachedPlanSize="416" CompileTime="8313" CompileCPU="7976" CompileMemory="65584"> <MissingIndexes> <MissingIndexGroup Impact="26.0859"> <MissingIndex Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]"> <ColumnGroup Usage="INEQUALITY"> <Column Name="[TRANSTYPE]" ColumnId="7" /> <Column Name="[COMMANDID]" ColumnId="40" /> </ColumnGroup> <ColumnGroup Usage="INCLUDE"> <Column Name="[TRANSDATE]" ColumnId="5" /> <Column Name="[WIPCODE]" ColumnId="9" /> <Column Name="[CASEID]" ColumnId="10" /> <Column Name="[EMPLOYEENO]" ColumnId="13" /> <Column Name="[TOTALUNITS]" ColumnId="15" /> <Column Name="[LOCALTRANSVALUE]" ColumnId="23" /> <Column Name="[LOCALCOST]" ColumnId="53" /> </ColumnGroup> </MissingIndex> </MissingIndexGroup> <MissingIndexGroup Impact="21.4186"> <MissingIndex Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]"> <ColumnGroup Usage="EQUALITY"> <Column Name="[EMPLOYEENO]" ColumnId="13" /> </ColumnGroup> <ColumnGroup Usage="INCLUDE"> <Column Name="[TRANSDATE]" ColumnId="5" /> <Column Name="[TRANSTYPE]" ColumnId="7" /> <Column Name="[WIPCODE]" ColumnId="9" /> <Column Name="[CASEID]" ColumnId="10" /> <Column Name="[TOTALUNITS]" ColumnId="15" /> <Column Name="[LOCALTRANSVALUE]" ColumnId="23" /> <Column Name="[COMMANDID]" ColumnId="40" /> <Column Name="[LOCALCOST]" ColumnId="53" /> </ColumnGroup> </MissingIndex> </MissingIndexGroup> </MissingIndexes> <RelOp AvgRowSize="432" EstimateCPU="0.0560152" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="560152" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="149.732"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n4]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> <ColumnReference Column="Expr1032" /> <ColumnReference Column="Expr1033" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1032" /> <ScalarOperator ScalarString="CASE WHEN [INPRO].[dbo].[WIPTEMPLATE].[WIPTYPEID] as [w2].[WIPTYPEID]=N'ADV' OR [INPRO].[dbo].[WIPTEMPLATE].[WIPTYPEID] as [w2].[WIPTYPEID]=N'CONV' OR [INPRO].[dbo].[WIPTEMPLATE].[WIPTYPEID] as [w2].[WIPTYPEID]=N'OVERHD' THEN ($0.0000) ELSE CONVERT(money,CASE WHEN [INPRO].[dbo].[WORKHISTORY].[LOCALCOST] as [w].[LOCALCOST] IS NOT NULL THEN [INPRO].[dbo].[WORKHISTORY].[LOCALCOST] as [w].[LOCALCOST] ELSE (0.00) END,0) END"> <IF> <Condition> <ScalarOperator> <Logical Operation="OR"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'ADV'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'CONV'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'OVERHD'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="($0.0000)" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Convert DataType="money" Style="0" Implicit="false"> <ScalarOperator> <IF> <Condition> <ScalarOperator> <Compare CompareOp="IS NOT"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> </Identifier> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(0.00)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </Convert> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1033" /> <ScalarOperator ScalarString="CASE WHEN [INPRO].[dbo].[WIPTEMPLATE].[WIPTYPEID] as [w2].[WIPTYPEID]=N'ADV' OR [INPRO].[dbo].[WIPTEMPLATE].[WIPTYPEID] as [w2].[WIPTYPEID]=N'CONV' OR [INPRO].[dbo].[WIPTEMPLATE].[WIPTYPEID] as [w2].[WIPTYPEID]=N'OVERHD' THEN CONVERT(money,CASE WHEN [INPRO].[dbo].[WORKHISTORY].[LOCALTRANSVALUE] as [w].[LOCALTRANSVALUE] IS NOT NULL THEN [INPRO].[dbo].[WORKHISTORY].[LOCALTRANSVALUE] as [w].[LOCALTRANSVALUE] ELSE (0.00) END,0) ELSE CONVERT(money,CASE WHEN [INPRO].[dbo].[WORKHISTORY].[LOCALTRANSVALUE] as [w].[LOCALTRANSVALUE] IS NOT NULL THEN [INPRO].[dbo].[WORKHISTORY].[LOCALTRANSVALUE] as [w].[LOCALTRANSVALUE] ELSE (0.00) END-CASE WHEN [INPRO].[dbo].[WORKHISTORY].[LOCALCOST] as [w].[LOCALCOST] IS NOT NULL THEN [INPRO].[dbo].[WORKHISTORY].[LOCALCOST] as [w].[LOCALCOST] ELSE (0.00) END,0) END"> <IF> <Condition> <ScalarOperator> <Logical Operation="OR"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'ADV'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'CONV'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'OVERHD'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Convert DataType="money" Style="0" Implicit="false"> <ScalarOperator> <IF> <Condition> <ScalarOperator> <Compare CompareOp="IS NOT"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> </Identifier> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(0.00)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </Convert> </ScalarOperator> </Then> <Else> <ScalarOperator> <Convert DataType="money" Style="0" Implicit="false"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <IF> <Condition> <ScalarOperator> <Compare CompareOp="IS NOT"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> </Identifier> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(0.00)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> <ScalarOperator> <IF> <Condition> <ScalarOperator> <Compare CompareOp="IS NOT"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> </Identifier> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(0.00)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </Arithmetic> </ScalarOperator> </Convert> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="434" EstimateCPU="5.69917" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="560152" LogicalOp="Right Outer Join" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="149.676"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n4]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.0785689" Output="0.0785689" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n4]" Column="NAMENO" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="NAMENO" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[NAME].[NAMENO] as [n4].[NAMENO]=[INPRO].[dbo].[CASENAME].[NAMENO] as [ins].[NAMENO]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n4]" Column="NAMENO" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="NAMENO" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="25" EstimateCPU="0.0705757" EstimateIO="0.080162" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="64017" LogicalOp="Index Scan" NodeId="2" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.150738" TableCardinality="64017"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n4]" Column="NAMENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n4]" Column="NAMECODE" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n4]" Column="NAMENO" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n4]" Column="NAMECODE" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Index="[XIE1NAME]" Alias="[n4]" IndexKind="NonClustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="426" EstimateCPU="7.7684" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="560152" LogicalOp="Right Outer Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="143.827"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="NAMENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.173551" Output="0.173551" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="CASEID" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[CASENAME].[CASEID] as [ins].[CASEID]=[INPRO].[dbo].[CASES].[CASEID] as [c].[CASEID]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="CASEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="22" EstimateCPU="1.6878" EstimateIO="3.59127" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="209259" LogicalOp="Index Scan" NodeId="4" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="5.27907" TableCardinality="1534220"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="NAMENO" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="CASEID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="NAMENO" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Index="[XPKCASENAME]" Alias="[ins]" IndexKind="NonClustered" /> <Predicate> <ScalarOperator ScalarString="[INPRO].[dbo].[CASENAME].[NAMETYPE] as [ins].[NAMETYPE]=N'I'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ins]" Column="NAMETYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'I'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="426" EstimateCPU="5.69917" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="560152" LogicalOp="Right Outer Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="130.043"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.0785689" Output="0.0785689" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMENO" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="NAMENO" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[NAME].[NAMENO] as [n3].[NAMENO]=[INPRO].[dbo].[CASENAME].[NAMENO] as [fe].[NAMENO]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMENO" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="NAMENO" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="25" EstimateCPU="0.0705757" EstimateIO="0.080162" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="64017" LogicalOp="Index Scan" NodeId="6" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.150738" TableCardinality="64017"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMECODE" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMENO" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n3]" Column="NAMECODE" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Index="[XIE1NAME]" Alias="[n3]" IndexKind="NonClustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="418" EstimateCPU="7.47144" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="560152" LogicalOp="Right Outer Join" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="124.193"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="NAMENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.165371" Output="0.165371" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="CASEID" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[CASENAME].[CASEID] as [fe].[CASEID]=[INPRO].[dbo].[CASES].[CASEID] as [c].[CASEID]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="CASEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="22" EstimateCPU="1.6878" EstimateIO="3.59127" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="209241" LogicalOp="Index Scan" NodeId="8" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="5.27907" TableCardinality="1534220"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="NAMENO" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="CASEID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="NAMENO" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Index="[XPKCASENAME]" Alias="[fe]" IndexKind="NonClustered" /> <Predicate> <ScalarOperator ScalarString="[INPRO].[dbo].[CASENAME].[NAMETYPE] as [fe].[NAMETYPE]=N'EMP'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[fe]" Column="NAMETYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'EMP'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="414" EstimateCPU="16.3427" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="512955" LogicalOp="Right Outer Join" NodeId="9" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="110.706"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.121272" Output="0.121272" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMENO" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="NAMENO" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[NAME].[NAMENO] as [n2].[NAMENO]=[INPRO].[dbo].[CASENAME].[NAMENO] as [ac].[NAMENO]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMENO" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="NAMENO" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="281" EstimateCPU="0.0705757" EstimateIO="1.50313" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="64017" LogicalOp="Clustered Index Scan" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.5737" TableCardinality="64017"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAME" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMENO" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAMECODE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n2]" Column="NAME" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Index="[XPKNAME]" Alias="[n2]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="150" EstimateCPU="7.15706" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="512955" LogicalOp="Right Outer Join" NodeId="11" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="92.7894"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="NAMENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.123905" Output="0.123905" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="CASEID" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[CASENAME].[CASEID] as [ac].[CASEID]=[INPRO].[dbo].[CASES].[CASEID] as [c].[CASEID]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="CASEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="22" EstimateCPU="1.6878" EstimateIO="3.59127" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="209064" LogicalOp="Index Scan" NodeId="12" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="5.27907" TableCardinality="1534220"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="NAMENO" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="CASEID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="NAMENO" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Index="[XPKCASENAME]" Alias="[ac]" IndexKind="NonClustered" /> <Predicate> <ScalarOperator ScalarString="[INPRO].[dbo].[CASENAME].[NAMETYPE] as [ac].[NAMETYPE]=N'D'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[ac]" Column="NAMETYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'D'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="146" EstimateCPU="5.03804" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="473503" LogicalOp="Right Outer Join" NodeId="13" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="79.6169"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.0549293" Output="0.0549293" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMENO" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="NAMENO" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[NAME].[NAMENO] as [n].[NAMENO]=[INPRO].[dbo].[CASENAME].[NAMENO] as [cn].[NAMENO]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMENO" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="NAMENO" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="25" EstimateCPU="0.0705757" EstimateIO="0.080162" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="64017" LogicalOp="Index Scan" NodeId="14" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.150738" TableCardinality="64017"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMENO" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Alias="[n]" Column="NAMECODE" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[NAME]" Index="[XIE1NAME]" Alias="[n]" IndexKind="NonClustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="138" EstimateCPU="6.89771" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="473503" LogicalOp="Right Outer Join" NodeId="15" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="74.4281"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="NAMENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.094417" Output="0.094417" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="CASEID" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[CASENAME].[CASEID] as [cn].[CASEID]=[INPRO].[dbo].[CASES].[CASEID] as [c].[CASEID]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="CASEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="22" EstimateCPU="1.6878" EstimateIO="3.59127" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208851" LogicalOp="Index Scan" NodeId="16" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="5.27907" TableCardinality="1534220"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="NAMENO" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="CASEID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="NAMENO" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Index="[XPKCASENAME]" Alias="[cn]" IndexKind="NonClustered" /> <Predicate> <ScalarOperator ScalarString="[INPRO].[dbo].[CASENAME].[NAMETYPE] as [cn].[NAMETYPE]=N'SIG'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASENAME]" Alias="[cn]" Column="NAMETYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'SIG'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="134" EstimateCPU="10.7713" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="441243" LogicalOp="Right Outer Join" NodeId="17" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="61.5149"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.105459" Output="0.105459" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="CASEID" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[CASES].[CASEID] as [c].[CASEID]=[INPRO].[dbo].[WORKHISTORY].[CASEID] as [w].[CASEID]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="CASEID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="32" EstimateCPU="0.229601" EstimateIO="0.556458" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208585" LogicalOp="Index Scan" NodeId="18" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.786059" TableCardinality="208585"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="CASEID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Alias="[c]" Column="IRN" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[CASES]" Index="[XIE7CASES]" Alias="[c]" IndexKind="NonClustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="115" EstimateCPU="3.84745" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="441243" LogicalOp="Inner Join" NodeId="19" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="49.9575"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.00378092" Output="0.00378092" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPCODE" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[WIPTEMPLATE].[WIPCODE] as [w2].[WIPCODE]=[INPRO].[dbo].[WORKHISTORY].[WIPCODE] as [w].[WIPCODE]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPCODE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="60" EstimateCPU="0.0012559" EstimateIO="0.0186806" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="999" LogicalOp="Clustered Index Scan" NodeId="20" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0199365" TableCardinality="999"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPCODE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="WIPTYPEID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Alias="[w2]" Column="DESCRIPTION" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[WIPTEMPLATE]" Index="[XPKWIPTEMPLATE]" Alias="[w2]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="75" EstimateCPU="4.64888" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="505852" LogicalOp="Inner Join" NodeId="21" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="46.0901"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> <ColumnReference Column="Expr1031" /> </OutputList> <MemoryFractions Input="0.000176678" Output="0.000176678" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="EMPLOYEENO" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="EMPLOYEENO" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[INPRO].[dbo].[WORKHISTORY].[EMPLOYEENO] as [w].[EMPLOYEENO]=[INPRO].[dbo].[EMPLOYEE].[EMPLOYEENO] as [e].[EMPLOYEENO]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="EMPLOYEENO" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="EMPLOYEENO" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="25" EstimateCPU="0.0004012" EstimateIO="0.00534722" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="222" LogicalOp="Clustered Index Scan" NodeId="22" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00574842" TableCardinality="222"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="EMPLOYEENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="EMPLOYEENO" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Alias="[e]" Column="ABBREVIATEDNAME" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[EMPLOYEE]" Index="[XPKEMPLOYEE]" Alias="[e]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="69" EstimateCPU="0.107567" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="623145" LogicalOp="Compute Scalar" NodeId="23" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="40.4889"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="EMPLOYEENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> <ColumnReference Column="Expr1031" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1031" /> <ScalarOperator ScalarString="CONVERT(money,CASE WHEN [INPRO].[dbo].[WORKHISTORY].[LOCALTRANSVALUE] as [w].[LOCALTRANSVALUE] IS NOT NULL THEN [INPRO].[dbo].[WORKHISTORY].[LOCALTRANSVALUE] as [w].[LOCALTRANSVALUE] ELSE (0.00) END,0)"> <Convert DataType="money" Style="0" Implicit="false"> <ScalarOperator> <IF> <Condition> <ScalarOperator> <Compare CompareOp="IS NOT"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> </Identifier> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(0.00)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="61" EstimateCPU="1.1834" EstimateIO="39.1979" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="623145" LogicalOp="Clustered Index Scan" NodeId="24" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="40.3813" TableCardinality="1075670"> <OutputList> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="CASEID" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="EMPLOYEENO" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSDATE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="WIPCODE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="CASEID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="EMPLOYEENO" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TOTALUNITS" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALTRANSVALUE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="LOCALCOST" /> </DefinedValue> </DefinedValues> <Object Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Index="[XPKWIPHISTORY]" Alias="[w]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[INPRO].[dbo].[WORKHISTORY].[TRANSTYPE] as [w].[TRANSTYPE]<>(510) AND [INPRO].[dbo].[WORKHISTORY].[COMMANDID] as [w].[COMMANDID]<>(9)"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="TRANSTYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(510)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[INPRO]" Schema="[dbo]" Table="[WORKHISTORY]" Alias="[w]" Column="COMMANDID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(9)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </ComputeScalar> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence></ShowPlanXML>Sorry it's a bit on the large side! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-06-20 : 09:01:16
|
| You've several index scans going on in there.Are the fields in the ON clauses of the same datatypes on both sides of the "=" signs? i.e. any implicit data conversions going on in order to evaluate them?Can you post the DDL of the tables involved? |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-06-20 : 09:14:39
|
| The data types are all the same on the joins. I could post the DDL but the issue is the DB is part of a 3rd party product, and we're very limited as to what we can modify.If it was a case of missing indexes then I'm not sure I can add them. I guess I just wondered if there was anything in the syntax used which stood out, as I couldn't see anything obvious.Would you want the whole DML statement or just the field list? I ask because the DDL for these tables is quite large.Thanks for looking at this. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2011-06-20 : 10:13:22
|
You could try a pivot to reduce the number of joins:;WITH CaseNamePivotAS( SELECT C1.CASEID ,MAX(CASE WHEN C1.NAMETYPE = 'D' THEN N1.NAMECODE ELSE '' END) AS AC ,MAX(CASE WHEN C1.NAMETYPE = 'D' THEN N1.NAME ELSE '' END) AS AC_NAME ,MAX(CASE WHEN C1.NAMETYPE = 'SIG' THEN N1.NAMECODE ELSE '' END) AS JOB_PARTNER ,MAX(CASE WHEN C1.NAMETYPE = 'EMP' THEN N1.NAMECODE ELSE '' END) AS JOB_FE ,MAX(CASE WHEN C1.NAMETYPE = 'I' THEN N1.NAMECODE ELSE '' END) AS INSTRUCTOR FROM CASENAME C1 JOIN NAME N1 ON C1.NAMENO = N1.NAMENO GROUP BY C1.CASEID)SELECT c.IRN as JOBNO ,w.TRANSDATE ,P.AC ,P.AC_NAME ,w.TRANSTYPE ,w.WIPCODE ,w2.[DESCRIPTION] As WIPDESC ,w2.WIPTYPEID As WIPTYPE ,e.ABBREVIATEDNAME as FEE_EARNER ,w.TOTALUNITS ,DATEDIFF(minute,'1899-01-01 00:00:00', w.TOTALTIME) as RECTIME ,CAST(COALESCE(w.LOCALTRANSVALUE,0) as MONEY) as GROSS ,CASE WHEN w2.WIPTYPEID In ('OVERHD', 'CONV', 'ADV') THEN CAST(0 AS money) ELSE CAST(COALESCE(w.LOCALCOST, 0) AS money) END AS COST ,CASE WHEN w2.WIPTYPEID In ('OVERHD', 'CONV', 'ADV') THEN CAST(COALESCE(w.LOCALTRANSVALUE, 0) AS money) ELSE CAST(COALESCE(w.LOCALTRANSVALUE, 0) - COALESCE(w.LOCALCOST, 0) AS MONEY) END AS NET ,P.JOB_PARTNER ,P.JOB_FE ,P.INSTRUCTOR ,w.INVOICENUMBER ,w.REASONCODE ,R.[DESCRIPTION]FROM WORKHISTORY AS w JOIN EMPLOYEE AS e ON w.EMPLOYEENO = e.EMPLOYEENO JOIN WIPTEMPLATE AS w2 ON w2.WIPCODE = w.WIPCODE LEFT JOIN CASES AS c ON c.CASEID = w.CASEID LEFT JOIN REASON R ON R.REASONCODE = w.REASONCODE LEFT JOIN CaseNamePivot P ON w.CASEID = P.CASEIDWHERE w.TRANSTYPE<>510 and W.commandid<>9 |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-06-21 : 03:46:07
|
| Thanks for the suggestion.I think I may have to try doing something like that seeing as I'm limited with what I can do with indexes etc. |
|
|
|
|
|
|
|
|
Poor performing query