February 10, 2015 at 11:19 am
Can anyone tell me where to look when trying to figure out why the following query is running forever and appears to be deleting things in a RBAR way despite the code appearing set-based? It's code from a 3rd party vendor in their proprietary DB so that complicates things:
delete floats
where status_cd = 'c'
and closedatetime < @cutoff_dt
and float_no not in (select isnull(float_no,0) from transactionlog)
and float_no not in (select isnull(float_no,0) from transactionlog_tenders)
and float_no not in (select isnull(float_no,0) from transaction_hdr)
and float_no not in (select isnull(float_no,0) from transaction_tenders)
I confirmed there are no triggers on the floats table. Oh and all the trouble seems to be in the last line of the WHERE clause. If I switch this to a select count(1) isntead of delete and comment out that last line it runs sub-second. The execution plan suggests an index on the floats table with status_cd and closedatetime including float_no (there's an index already for float_no on all the tables referenced.).
The reason it appears RBAR is that if I check the number of rows in the floats table over and over it goes down by 1 every second or so. By my calculations that means the query will take about 36 hours to run.
Activity Monitor just shows that the query is running. A profiler trace shows the server doing virtually nothing.
Could it be referential integrity? That's an area I'm relatively weak on in SQL Server.
TIA for any help you guys can suggest.
Rick Todd
February 10, 2015 at 11:24 am
How many rows in floats and how many is it usually trying to delete?
February 10, 2015 at 11:30 am
Please attach the estimated query plan. My guess is index deletes are in play with the bad plan. There may be ways to assist with that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 10, 2015 at 11:35 am
At the start there were about 110k rows in floats, and it chose about 80k to delete.
Counts for the other tables in the query:
transactionlog2567
transactionlog_tenders571
transaction_hdr1690289
transaction_tenders2354679
Rick Todd
February 10, 2015 at 11:37 am
All I can do at this time is generate the estimated query plan, which doesn't show any index deletes
I wasn't sure the best way to do that here so here's 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.50.1617.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="9751.9" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="47.8277" StatementText="delete floats where status_cd = 'c' and closedatetime < '2012-02-10 00:00:00.000' and float_no not in (select isnull(float_no,0) from transactionlog) and float_no not in (select isnull(float_no,0) from transactionlog_tenders) and float_no not in (select isnull(float_no,0) from transaction_hdr) and float_no not in (select isnull(float_no,0) from transaction_tenders)" StatementType="DELETE" QueryHash="0xD942FE72B84AB855" QueryPlanHash="0x53EFDC4F5837A477">
<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="136" CompileTime="167" CompileCPU="167" CompileMemory="2472">
<RelOp AvgRowSize="9" EstimateCPU="0.0319862" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9751.9" LogicalOp="Assert" NodeId="1" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="47.8277">
<OutputList />
<Assert StartupExpression="false">
<RelOp AvgRowSize="10" EstimateCPU="0.0452922" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9751.9" LogicalOp="Left Semi Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="47.7957">
<OutputList>
<ColumnReference Column="Expr1062" />
<ColumnReference Column="Expr1063" />
<ColumnReference Column="Expr1064" />
<ColumnReference Column="Expr1065" />
<ColumnReference Column="Expr1066" />
<ColumnReference Column="Expr1067" />
<ColumnReference Column="Expr1068" />
<ColumnReference Column="Expr1069" />
<ColumnReference Column="Expr1070" />
<ColumnReference Column="Expr1071" />
</OutputList>
<NestedLoops Optimized="true" WithUnorderedPrefetch="true">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1071" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1077" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1071" />
</ProbeColumn>
<RelOp AvgRowSize="14" EstimateCPU="0.0503246" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10835.4" LogicalOp="Left Semi Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="42.7588">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1062" />
<ColumnReference Column="Expr1063" />
<ColumnReference Column="Expr1064" />
<ColumnReference Column="Expr1065" />
<ColumnReference Column="Expr1066" />
<ColumnReference Column="Expr1067" />
<ColumnReference Column="Expr1068" />
<ColumnReference Column="Expr1069" />
<ColumnReference Column="Expr1070" />
</OutputList>
<NestedLoops Optimized="false" WithUnorderedPrefetch="true">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1070" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1076" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1070" />
</ProbeColumn>
<RelOp AvgRowSize="13" EstimateCPU="0.0360949" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="12039.4" LogicalOp="Left Semi Join" NodeId="7" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="37.3366">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1062" />
<ColumnReference Column="Expr1063" />
<ColumnReference Column="Expr1064" />
<ColumnReference Column="Expr1065" />
<ColumnReference Column="Expr1066" />
<ColumnReference Column="Expr1067" />
<ColumnReference Column="Expr1068" />
<ColumnReference Column="Expr1069" />
</OutputList>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog_tenders]" Column="float_no" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[transactionlog_tenders].[float_no]=[cwstoretst].[dbo].[floats].[float_no]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog_tenders]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="12" EstimateCPU="0.00694191" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13377.1" LogicalOp="Left Semi Join" NodeId="8" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="37.2906">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1062" />
<ColumnReference Column="Expr1063" />
<ColumnReference Column="Expr1064" />
<ColumnReference Column="Expr1065" />
<ColumnReference Column="Expr1066" />
<ColumnReference Column="Expr1067" />
<ColumnReference Column="Expr1068" />
</OutputList>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[cash_pickup].[float_no]=[cwstoretst].[dbo].[floats].[float_no]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="12" EstimateCPU="0.00709054" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="14863.4" LogicalOp="Left Semi Join" NodeId="9" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="37.269">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1062" />
<ColumnReference Column="Expr1063" />
<ColumnReference Column="Expr1064" />
<ColumnReference Column="Expr1065" />
<ColumnReference Column="Expr1066" />
<ColumnReference Column="Expr1067" />
</OutputList>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[cash_pickup].[float_no]=[cwstoretst].[dbo].[floats].[float_no]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="12" EstimateCPU="0.0767027" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16514.9" LogicalOp="Left Semi Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="37.2473">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1062" />
<ColumnReference Column="Expr1063" />
<ColumnReference Column="Expr1064" />
<ColumnReference Column="Expr1065" />
<ColumnReference Column="Expr1066" />
</OutputList>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1066" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1075" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1066" />
</ProbeColumn>
<RelOp AvgRowSize="12" EstimateCPU="0.0852252" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="18349.9" LogicalOp="Left Semi Join" NodeId="12" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="24.7871">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1062" />
<ColumnReference Column="Expr1063" />
<ColumnReference Column="Expr1064" />
<ColumnReference Column="Expr1065" />
</OutputList>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1065" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1074" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1065" />
</ProbeColumn>
<RelOp AvgRowSize="12" EstimateCPU="0.0946947" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20388.8" LogicalOp="Left Semi Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="19.8686">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1062" />
<ColumnReference Column="Expr1063" />
<ColumnReference Column="Expr1064" />
</OutputList>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1064" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1073" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1064" />
</ProbeColumn>
<RelOp AvgRowSize="12" EstimateCPU="0.066116" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="22654.2" LogicalOp="Left Semi Join" NodeId="16" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="14.5292">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1062" />
<ColumnReference Column="Expr1063" />
</OutputList>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[transactionlog].[float_no]=[cwstoretst].[dbo].[floats].[float_no]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="12" EstimateCPU="0.072241" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25171.4" LogicalOp="Left Semi Join" NodeId="17" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="13.9303">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1062" />
</OutputList>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[transactionlog].[float_no]=[cwstoretst].[dbo].[floats].[float_no]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="11" EstimateCPU="0.0279682" EstimateIO="2.50784" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27968.2" LogicalOp="Delete" NodeId="18" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="13.3253">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OutputList>
<Update DMLRequestSort="false">
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Index="[PK__floats__2EDAF651]" IndexKind="Clustered" />
<RelOp AvgRowSize="11" EstimateCPU="0.00279682" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27968.2" LogicalOp="Top" NodeId="19" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="10.7895">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OutputList>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="11" EstimateCPU="0.12342" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27968.2" LogicalOp="Left Anti Semi Join" NodeId="20" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="10.7867">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OuterReferences>
<RelOp AvgRowSize="11" EstimateCPU="0.135903" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="29526.3" LogicalOp="Left Anti Semi Join" NodeId="21" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="6.39248">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OutputList>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
<ColumnReference Column="Expr1072" />
</OuterReferences>
<RelOp AvgRowSize="11" EstimateCPU="0.0772931" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="32512.6" LogicalOp="Left Anti Semi Join" NodeId="23" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="1.07362">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OutputList>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Column="Expr1061" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[float_no]=[Expr1061]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1061" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="11" EstimateCPU="0.0856028" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="32545.2" LogicalOp="Left Anti Semi Join" NodeId="24" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.96691">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OutputList>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[float_no]=[cwstoretst].[dbo].[transactionlog].[float_no]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="20" EstimateCPU="0.0752683" EstimateIO="0.672755" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="34419.3" LogicalOp="Clustered Index Scan" NodeId="25" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.748023" TableCardinality="68283">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</DefinedValue>
</DefinedValues>
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Index="[PK__floats__2EDAF651]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[closedatetime]<'2012-02-10 00:00:00.000' AND [cwstoretst].[dbo].[floats].[status_cd]='c'">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="closedatetime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-02-10 00:00:00.000'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="status_cd" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'c'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0454547" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2127.52" LogicalOp="Distinct Sort" NodeId="26" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.073192">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</OutputList>
<MemoryFractions Input="0.173469" Output="0.173469" />
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="11" EstimateCPU="0.0029807" EstimateIO="0.0134954" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2567" LogicalOp="Index Scan" NodeId="27" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0164761" TableCardinality="2567">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</DefinedValue>
</DefinedValues>
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Index="[IX_transactionlog_trans_status_trans_type_id_float_no_trans_no]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.00825701" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="46.1701" LogicalOp="Distinct Sort" NodeId="29" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0294114">
<OutputList>
<ColumnReference Column="Expr1061" />
</OutputList>
<MemoryFractions Input="0.0408163" Output="0.0408163" />
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1061" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="11" EstimateCPU="5.71E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="571" LogicalOp="Compute Scalar" NodeId="30" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00989313">
<OutputList>
<ColumnReference Column="Expr1061" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1061" />
<ScalarOperator ScalarString="isnull([cwstoretst].[dbo].[transactionlog_tenders].[float_no],(0))">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog_tenders]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="11" EstimateCPU="0.0007851" EstimateIO="0.00905093" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="571" LogicalOp="Index Scan" NodeId="31" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00983603" TableCardinality="571">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog_tenders]" Column="float_no" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog_tenders]" Column="float_no" />
</DefinedValue>
</DefinedValues>
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog_tenders]" Index="[IX_transactionlog_tenders_float_no_tender_id]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="32511.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="37" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="5.17971">
<OutputList />
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="9" EstimateCPU="0.00106665" EstimateIO="0.00460648" EstimateRebinds="32511.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="38" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="5.17646" TableCardinality="1690290">
<OutputList />
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_hdr]" Index="[IX_transaction_hdr_float]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_hdr]" Column="float_no" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[float_no]">
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="29525.3" EstimateRewinds="1.45519E-11" EstimateRows="1" LogicalOp="Top" NodeId="42" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="4.26786">
<OutputList />
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="11" EstimateCPU="2.59023" EstimateIO="46.9217" EstimateRebinds="0" EstimateRewinds="29525.3" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="44" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="3.59406" TableCardinality="2354680">
<OutputList />
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_tenders]" Index="[PK_transaction_tenders]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[float_no]=isnull([cwstoretst].[dbo].[transaction_tenders].[float_no],(0))">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_tenders]" Column="float_no" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0029807" EstimateIO="0.529792" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2567" LogicalOp="Index Scan" NodeId="49" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.532772" TableCardinality="2567">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</DefinedValue>
</DefinedValues>
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Index="[IX_transactionlog_float_no_trans_status_trans_type_id_trans_no]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0029807" EstimateIO="0.529792" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2567" LogicalOp="Index Scan" NodeId="51" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.532772" TableCardinality="2567">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Column="float_no" />
</DefinedValue>
</DefinedValues>
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog]" Index="[IX_transactionlog_float_no_trans_status_trans_type_id_trans_no]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.00039418" EstimateIO="0.003125" EstimateRebinds="22653.2" EstimateRewinds="0" EstimateRows="21.5618" LogicalOp="Index Seek" NodeId="53" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="5.24471" TableCardinality="1129620">
<OutputList />
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[float_counts]" Index="[PK__float_counts__55009F39]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[float_counts]" Column="float_no" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[float_no]">
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.00039418" EstimateIO="0.003125" EstimateRebinds="20387.8" EstimateRewinds="0" EstimateRows="21.5618" LogicalOp="Index Seek" NodeId="54" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="4.83323" TableCardinality="1129620">
<OutputList />
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[float_counts]" Index="[PK__float_counts__55009F39]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[float_counts]" Column="float_no" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[float_no]">
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.00179426" EstimateIO="0.00756944" EstimateRebinds="18348.9" EstimateRewinds="0" EstimateRows="148.842" LogicalOp="Index Seek" NodeId="55" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="12.3835" TableCardinality="2354680">
<OutputList />
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_tenders]" Index="[IX_transaction_tenders_float_no_tender_id]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_tenders]" Column="float_no" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[float_no]">
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.000100011" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sort" NodeId="56" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0146444">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</OutputList>
<MemoryFractions Input="0.0204082" Output="0.0204082" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="57" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="true" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</DefinedValue>
</DefinedValues>
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Index="[PK__cash_pickup__3864608B]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.000100011" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sort" NodeId="59" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0146444">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</OutputList>
<MemoryFractions Input="0.0204082" Output="0.0204082" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="60" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="true" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Column="float_no" />
</DefinedValue>
</DefinedValues>
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[cash_pickup]" Index="[PK__cash_pickup__3864608B]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0007851" EstimateIO="0.00905093" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="571" LogicalOp="Index Scan" NodeId="62" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00983603" TableCardinality="571">
<OutputList>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog_tenders]" Column="float_no" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog_tenders]" Column="float_no" />
</DefinedValue>
</DefinedValues>
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transactionlog_tenders]" Index="[IX_transactionlog_tenders_float_no_tender_id]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.00106665" EstimateIO="0.00460648" EstimateRebinds="12038.4" EstimateRewinds="0" EstimateRows="82.6952" LogicalOp="Index Seek" NodeId="64" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="5.37191" TableCardinality="1690290">
<OutputList />
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_hdr]" Index="[IX_transaction_hdr_float]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_hdr]" Column="float_no" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[float_no]">
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.00106665" EstimateIO="0.00460648" EstimateRebinds="10834.4" EstimateRewinds="0" EstimateRows="82.6952" LogicalOp="Index Seek" NodeId="65" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="4.99161" TableCardinality="1690290">
<OutputList />
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_hdr]" Index="[IX_transaction_hdr_float]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[transaction_hdr]" Column="float_no" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[cwstoretst].[dbo].[floats].[float_no]">
<Identifier>
<ColumnReference Database="[cwstoretst]" Schema="[dbo]" Table="[floats]" Column="float_no" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN NOT [Expr1062] IS NULL THEN (0) ELSE CASE WHEN NOT [Expr1063] IS NULL THEN (1) ELSE CASE WHEN NOT [Expr1064] IS NULL THEN (2) ELSE CASE WHEN NOT [Expr1065] IS NULL THEN (3) ELSE CASE WHEN NOT [Expr1066] IS NULL THEN (4) ELSE CASE WHEN NOT [Expr1067] IS NULL THEN (5) ELSE CASE WHEN NOT [Expr1068] IS NULL THEN (6) ELSE CASE WHEN NOT [Expr1069] IS NULL THEN (7) ELSE CASE WHEN NOT [Expr1070] IS NULL THEN (8) ELSE CASE WHEN NOT [Expr1071] IS NULL THEN (9) ELSE NULL END END END END END END END END END END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1062" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1063" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1064" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1065" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(3)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1066" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1067" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(5)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1068" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(6)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1069" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(7)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1070" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(8)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1071" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(9)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Rick Todd
February 10, 2015 at 11:48 am
1) That wasn't an "attached" query plan. Help me out here please. 🙂
2) Are the data types all proper?? Conversions can kill!
3) Maybe it would be best to DROP all indexes on the table (except perhaps clustered one) being deleted from and then run the cleanup since you are deleting 2/3rds of the data?
4) Can you put the keys to be deleted into a temp table (NOT table variable) and then try the delete from that?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 10, 2015 at 1:06 pm
TheSQLGuru (2/10/2015)
1) That wasn't an "attached" query plan. Help me out here please. 🙂2) Are the data types all proper?? Conversions can kill!
3) Maybe it would be best to DROP all indexes on the table (except perhaps clustered one) being deleted from and then run the cleanup since you are deleting 2/3rds of the data?
4) Can you put the keys to be deleted into a temp table (NOT table variable) and then try the delete from that?
1. Happy to "attach" if you can explain how to do that.
2. They're all INTs and the 4 columns in the tables in the WHERE are all FKs.
3. The floats table only has 1 index, the PK, which is float_no
4. So basically use a select statement first which populates the temp table, then run the much simpler delete that only refers to the temp table? OK.
It seems like your suggesting that the RBAR-ness could be just because of a poorly optimized query, and not because of something behind the scenes (like a trigger) that's causing it to run RBAR explicitly? I had assumed a poorly optimized set-based query would just take a long time, then operate as a set.
Thanks for your continued help,
Rick
Rick Todd
February 10, 2015 at 1:12 pm
What is the cash_pickups table? Seems to be in the plan but not the query? Is one of those tables a view? Or do you maybe have a referential integrity constraint that is being checked?
If not, I wonder if you'd get better performance replacing the NOT IN statements with a NOT EXISTS and a correlated subquery.
You are going to be scanning the deleted table regardless, but being the other tables are bigger, you may be able to get seeks for each of those conditions.
edit:
delete f
FROM floats f
where status_cd = 'c'
and closedatetime < @cutoff_dt
and not exists (select 1 from transactionlog tl WHERE tl.float_no = f.float_no)
and not exists (select 1 from transactionlog_tenders tt WHERE tt.float_no = f.float_no)
and not exists (select 1 from transaction_hdr th WHERE th.float_no = f.float_no)
and not exists (select 1 from transaction_tenders tnt WHERE tnt.float_no = f.float_no)
February 10, 2015 at 2:06 pm
I've found that building up a list of keys shared across multiple tables into a temp table using EXCEPT keyword is very efficient. Deleting or updating against an inner join on such a temp table is also efficient. Assuming that FLOAT_NO is the primary key in table FLOATS, the following alternative should be functionally equivalent to your original delete statement, but the query plan will be a lot simpler.
create table #d ( float_no int not null primary key );
insert into #d ( float_no )
select float_no from floats where status_cd = 'c' and closedatetime < @cutoff_dt
except
select float_no from transactionlog
except
select float_no from transactionlog_tenders
except
select float_no from transaction_hdr
except
select float_no from transaction_tenders;
delete f
from floats f
join #d on #d.float_no = f.float_no;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 10, 2015 at 2:21 pm
Make sure you have nonclustered indexes on:
transaction_hdr ( float_no )
transaction_tenders ( float_no )
You want SQL to scan the floats table, not use an index, since it will delete > 70% of the rows anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 10, 2015 at 2:22 pm
Also, verify that you have enough free log space on that db already allocated to handle all the deletes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 10, 2015 at 2:57 pm
Nevyn (2/10/2015)
What is the cash_pickups table? Seems to be in the plan but not the query? Is one of those tables a view? Or do you maybe have a referential integrity constraint that is being checked?If not, I wonder if you'd get better performance replacing the NOT IN statements with a NOT EXISTS and a correlated subquery.
You are going to be scanning the deleted table regardless, but being the other tables are bigger, you may be able to get seeks for each of those conditions.
edit:
delete fFROM floats f
where status_cd = 'c'
and closedatetime < @cutoff_dt
and not exists (select 1 from transactionlog tl WHERE tl.float_no = f.float_no)
and not exists (select 1 from transactionlog_tenders tt WHERE tt.float_no = f.float_no)
and not exists (select 1 from transaction_hdr th WHERE th.float_no = f.float_no)
and not exists (select 1 from transaction_tenders tnt WHERE tnt.float_no = f.float_no)
Yes, the cash_pickups table has a FK referring to float_no, so it's checking that each time. I have limited experience with DBs built this way, so how much of a performance hit are all these FKs with referential integrity checks? I just did a "view dependencies" on this table and there are 4 tables that rely on this table.
I rewrote the query with NOT EXISTS after your initial reply, before your edit, and feel into what I think is the same trap you did. I wrote it the same way and it was sub-second. But when I added the ISNULLs that I think are necessary to be consistent with the perceived logic from the 3rd party code, it's back to being slow. But now that I write that I don't actually think I need the ISNULLs unless there are float_no entries in dbo.floats that are 0 (there aren't) and that's some kind of crazy intentional logic.
Rick Todd
February 10, 2015 at 3:02 pm
Rick Todd (2/10/2015)
Nevyn (2/10/2015)
What is the cash_pickups table? Seems to be in the plan but not the query? Is one of those tables a view? Or do you maybe have a referential integrity constraint that is being checked?If not, I wonder if you'd get better performance replacing the NOT IN statements with a NOT EXISTS and a correlated subquery.
You are going to be scanning the deleted table regardless, but being the other tables are bigger, you may be able to get seeks for each of those conditions.
edit:
delete fFROM floats f
where status_cd = 'c'
and closedatetime < @cutoff_dt
and not exists (select 1 from transactionlog tl WHERE tl.float_no = f.float_no)
and not exists (select 1 from transactionlog_tenders tt WHERE tt.float_no = f.float_no)
and not exists (select 1 from transaction_hdr th WHERE th.float_no = f.float_no)
and not exists (select 1 from transaction_tenders tnt WHERE tnt.float_no = f.float_no)
Yes, the cash_pickups table has a FK referring to float_no, so it's checking that each time. I have limited experience with DBs built this way, so how much of a performance hit are all these FKs with referential integrity checks? I just did a "view dependencies" on this table and there are 4 tables that rely on this table.
I rewrote the query with NOT EXISTS after your initial reply, before your edit, and feel into what I think is the same trap you did. I wrote it the same way and it was sub-second. But when I added the ISNULLs that I think are necessary to be consistent with the perceived logic from the 3rd party code, it's back to being slow. But now that I write that I don't actually think I need the ISNULLs unless there are float_no entries in dbo.floats that are 0 (there aren't) and that's some kind of crazy intentional logic.
This ISNULL() is because NULLs within a NOT IN cause logic problems.
But you can do the same thing vastly more efficiently by just explicitly excluding NULLs from the NOT IN ... or by just using NOT EXISTS as you have now:
and float_no not in (select float_no from transactionlog tl WHERE float_no IS NOT NULL)
and float_no not in (select float_no from transactionlog_tenders tt WHERE float_no IS NOT NULL)
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 10, 2015 at 3:08 pm
Oh, beware of unindexed FK type fields. Doing iterative table scans to find things can be ugly. 🙂 Not sure if that applies here but it is general good advice.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 10, 2015 at 3:26 pm
Rick Todd (2/10/2015)
The reason it appears RBAR is that if I check the number of rows in the floats table over and over it goes down by 1 every second or so. By my calculations that means the query will take about 36 hours to run.
Rick Todd (2/10/2015)
At the start there were about 110k rows in floats, and it chose about 80k to delete.Counts for the other tables in the query:
transactionlog2,567
transactionlog_tenders571
transaction_hdr1,690,289
transaction_tenders2,354,679
Am I reading correctly above that you have only 110,000 rows in the FLOATS table, and you estimate 80,000 will be deleted? If that's the case, then I'd expect the INNER JOIN #D method I mentioned earlier to complete within a few seconds, assuming all foreign keys on FLOAT_NO are indexed. None of these tables contain more than a couple million rows, so even if some foreign keys are not indexed, it should still complete using a hash within several minutes, even on a low end server. Also, transaction log space required for 80,000 deletes should be trivial.
Once done, you can perform a DBCC DBREINDEX on the FLOATS table to take care of fragmentation and page density.
This is no big deal. It's just that those correlated sub-queries in the existing delete operation are creating a twisted execution plan, but that's not the best approach here.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply