Updating 20M rows takes 5 hours

  • Yes... like I said... "accidental cross join" in the form of a many-to-many join. Any chance of you saving the execution plan as a "real" execution plan so I can load it up is SSMS to have a peek?

    Hi jeff

    Please forgive my ignorance.I am not sure what u mean by "Real" Execution plan. The one that was attached was actual execution plan.

    Please find below the xml for the execution plan. Please note this one is for estimated execution plan as i'll have to leave the query running again for several hours to generate the actual execution plan again.

    Please let me know if this is not what you wanted?

    Many thanks.

    <?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.0" Build="9.00.4053.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

    <BatchSequence>

    <Batch>

    <Statements>

    <StmtSimple StatementCompId="1" StatementEstRows="20417000000" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="207838" StatementText="-- Create the new table SELECT p.auditId , p.vFrom , p.vTo , p.assetId , p.availability , p.status , p.opMode , p.qtyIn , p.qtyOut , p.qtyProcessed , p.qtyRejected , p.countUnitId , p.rate , p.shiftId , p.runId , p.productId , p.crewId , p.crewSize , p.stopEventRefId , p.rejectEventRefId , p.xnCode , p.version , p.shiftAuditId , p.cellAssetId , --p.assetname , --p.assetdesc , --p.assetRunCostPerHour , --p.assettype , p.assetname , p.assetdesc , p.assetRunCostPerHour, p.assettype , p.countUnitDesc , p.shiftName , p.shiftDesc , p.runname , p.productName , p.productDesc , p.productCountUnitId , p.productCountUnitDesc , p.materialCost , p.crewName , p.crewCostPerHourPerHead , p.cellAssetName , p.cellAssetDesc , sh.auditid as lastStatusChangeAuditId INTO dbo.New FROM dbo.productionAudit p left join statushistory sh on p.assetid = sh.assetid AND p.vFrom >= sh.vFrom AND p.vTo <= sh.vTo" StatementType="SELECT INTO">

    <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />

    <QueryPlan CachedPlanSize="98" CompileTime="146" CompileCPU="146" CompileMemory="584">

    <RelOp AvgRowSize="9" EstimateCPU="20417" EstimateIO="151559" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20417000000" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="207838">

    <OutputList />

    <Update>

    <Object Schema="[dbo]" Table="[New]" />

    <SetPredicate>

    <ScalarOperator ScalarString="[auditId] = [york].[dbo].[productionAudit].[auditId] as [p].[auditId],[vFrom] = [york].[dbo].[productionAudit].[vFrom] as [p].[vFrom],[vTo] = [york].[dbo].[productionAudit].[vTo] as [p].[vTo],[assetId] = [york].[dbo].[productionAudit].[assetId] as [p].[assetId],[availability] = [york].[dbo].[productionAudit].[availability] as [p].[availability],[status] = [york].[dbo].[productionAudit].[status] as [p].[status],[opMode] = [york].[dbo].[productionAudit].[opMode] as [p].[opMode],[qtyIn] = [york].[dbo].[productionAudit].[qtyIn] as [p].[qtyIn],[qtyOut] = [york].[dbo].[productionAudit].[qtyOut] as [p].[qtyOut],[qtyProcessed] = [york].[dbo].[productionAudit].[qtyProcessed] as [p].[qtyProcessed],[qtyRejected] = [york].[dbo].[productionAudit].[qtyRejected] as [p].[qtyRejected],[countUnitId] = [york].[dbo].[productionAudit].[countUnitId] as [p].[countUnitId],[rate] = [york].[dbo].[productionAudit].[rate] as [p].[rate],[shiftId] = [york].[dbo].[productionAudit].[shiftId] as [p].[shiftId],[runId] = [york].[dbo].[productionAudit].[runId] as [p].[runId],[productId] = [york].[dbo].[productionAudit].[productId] as [p].[productId],[crewId] = [york].[dbo].[productionAudit].[crewId] as [p].[crewId],[crewSize] = [york].[dbo].[productionAudit].[crewSize] as [p].[crewSize],[stopEventRefId] = [york].[dbo].[productionAudit].[stopEventRefId] as [p].[stopEventRefId],[rejectEventRefId] = [york].[dbo].[productionAudit].[rejectEventRefId] as [p].[rejectEventRefId],[xnCode] = [york].[dbo].[productionAudit].[xnCode] as [p].[xnCode],[version] = [york].[dbo].[productionAudit].[version] as [p].[version],[shiftAuditId] = [york].[dbo].[productionAudit].[shiftAuditId] as [p].[shiftAuditId],[cellAssetId] = [york].[dbo].[productionAudit].[cellAssetId] as [p].[cellAssetId],[assetname] = [york].[dbo].[productionAudit].[assetname] as [p].[assetname],[assetdesc] = [york].[dbo].[productionAudit].[assetdesc] as [p].[assetdesc],[assetRunCostPerHour] = [york].[dbo].[productionAudit].[assetRunCostPerHour] as [p].[assetRunCostPerHour],[assettype] = [york].[dbo].[productionAudit].[assettype] as [p].[assettype],[countUnitDesc] = [york].[dbo].[productionAudit].[countUnitDesc] as [p].[countUnitDesc],[shiftName] = [york].[dbo].[productionAudit].[shiftName] as [p].[shiftName],[shiftDesc] = [york].[dbo].[productionAudit].[shiftDesc] as [p].[shiftDesc],[runname] = [york].[dbo].[productionAudit].[runname] as [p].[runname],[productName] = [york].[dbo].[productionAudit].[productName] as [p].[productName],[productDesc] = [york].[dbo].[productionAudit].[productDesc] as [p].[productDesc],[productCountUnitId] = [york].[dbo].[productionAudit].[productCountUnitId] as [p].[productCountUnitId],[productCountUnitDesc] = [york].[dbo].[productionAudit].[productCountUnitDesc] as [p].[productCountUnitDesc],[materialCost] = [york].[dbo].[productionAudit].[materialCost] as [p].[materialCost],[crewName] = [york].[dbo].[productionAudit].[crewName] as [p].[crewName],[crewCostPerHourPerHead] = [york].[dbo].[productionAudit].[crewCostPerHourPerHead] as [p].[crewCostPerHourPerHead],[cellAssetName] = [york].[dbo].[productionAudit].[cellAssetName] as [p].[cellAssetName],[cellAssetDesc] = [york].[dbo].[productionAudit].[cellAssetDesc] as [p].[cellAssetDesc],[lastStatusChangeAuditId] = [york].[dbo].[statusHistory].[auditId] as [sh].[auditId]">

    <ScalarExpressionList>

    <ScalarOperator>

    <MultipleAssign>

    <Assign>

    <ColumnReference Column="auditId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="auditId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="vFrom" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vFrom" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="vTo" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vTo" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="assetId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="availability" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="availability" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="status" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="status" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="opMode" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="opMode" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="qtyIn" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyIn" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="qtyOut" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyOut" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="qtyProcessed" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyProcessed" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="qtyRejected" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyRejected" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="countUnitId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="rate" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rate" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="shiftId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="runId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="productId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="crewId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="crewSize" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewSize" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="stopEventRefId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="stopEventRefId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="rejectEventRefId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rejectEventRefId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="xnCode" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="xnCode" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="version" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="version" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="shiftAuditId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftAuditId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="cellAssetId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="assetname" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetname" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="assetdesc" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetdesc" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="assetRunCostPerHour" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetRunCostPerHour" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="assettype" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assettype" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="countUnitDesc" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitDesc" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="shiftName" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftName" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="shiftDesc" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftDesc" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="runname" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runname" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="productName" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productName" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="productDesc" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productDesc" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="productCountUnitId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="productCountUnitDesc" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitDesc" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="materialCost" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="materialCost" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="crewName" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewName" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="crewCostPerHourPerHead" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewCostPerHourPerHead" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="cellAssetName" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetName" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="cellAssetDesc" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetDesc" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    <Assign>

    <ColumnReference Column="lastStatusChangeAuditId" />

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="auditId" />

    </Identifier>

    </ScalarOperator>

    </Assign>

    </MultipleAssign>

    </ScalarOperator>

    </ScalarExpressionList>

    </ScalarOperator>

    </SetPredicate>

    <RelOp AvgRowSize="764" EstimateCPU="2041.7" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20417000000" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="35861.8">

    <OutputList>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="auditId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vFrom" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vTo" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="availability" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="status" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="opMode" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyIn" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyOut" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyProcessed" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyRejected" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rate" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewSize" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="stopEventRefId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rejectEventRefId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="xnCode" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="version" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftAuditId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetname" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetdesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetRunCostPerHour" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assettype" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runname" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="materialCost" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewCostPerHourPerHead" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="auditId" />

    </OutputList>

    <Top RowCount="true" IsPercent="false" WithTies="false">

    <TopExpression>

    <ScalarOperator ScalarString="(0)">

    <Const ConstValue="(0)" />

    </ScalarOperator>

    </TopExpression>

    <RelOp AvgRowSize="764" EstimateCPU="33561.6" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20417000000" LogicalOp="Right Outer Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="33820.1">

    <OutputList>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="auditId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vFrom" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vTo" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="availability" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="status" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="opMode" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyIn" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyOut" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyProcessed" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyRejected" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rate" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewSize" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="stopEventRefId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rejectEventRefId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="xnCode" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="version" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftAuditId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetname" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetdesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetRunCostPerHour" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assettype" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runname" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="materialCost" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewCostPerHourPerHead" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="auditId" />

    </OutputList>

    <MemoryFractions Input="1" Output="1" />

    <Hash>

    <DefinedValues />

    <HashKeysBuild>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="assetId" />

    </HashKeysBuild>

    <HashKeysProbe>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetId" />

    </HashKeysProbe>

    <ProbeResidual>

    <ScalarOperator ScalarString="[york].[dbo].[productionAudit].[assetId] as [p].[assetId]=[york].[dbo].[statusHistory].[assetId] as [sh].[assetId] AND [york].[dbo].[productionAudit].[vFrom] as [p].[vFrom]>=[york].[dbo].[statusHistory].[vFrom] as [sh].[vFrom] AND [york].[dbo].[productionAudit].[vTo] as [p].[vTo]<=[york].[dbo].[statusHistory].[vTo] as [sh].[vTo]">

    <Logical Operation="AND">

    <ScalarOperator>

    <Compare CompareOp="EQ">

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetId" />

    </Identifier>

    </ScalarOperator>

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="assetId" />

    </Identifier>

    </ScalarOperator>

    </Compare>

    </ScalarOperator>

    <ScalarOperator>

    <Compare CompareOp="GE">

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vFrom" />

    </Identifier>

    </ScalarOperator>

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="vFrom" />

    </Identifier>

    </ScalarOperator>

    </Compare>

    </ScalarOperator>

    <ScalarOperator>

    <Compare CompareOp="LE">

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vTo" />

    </Identifier>

    </ScalarOperator>

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="vTo" />

    </Identifier>

    </ScalarOperator>

    </Compare>

    </ScalarOperator>

    </Logical>

    </ScalarOperator>

    </ProbeResidual>

    <RelOp AvgRowSize="31" EstimateCPU="0.514036" EstimateIO="1.84387" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="467163" LogicalOp="Index Scan" NodeId="3" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.3579">

    <OutputList>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="auditId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="assetId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="vFrom" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="vTo" />

    </OutputList>

    <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">

    <DefinedValues>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="auditId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="assetId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="vFrom" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[statusHistory]" Alias="[sh]" Column="vTo" />

    </DefinedValue>

    </DefinedValues>

    <Object Database="[york]" Schema="[dbo]" Table="[statusHistory]" Index="[missing_index_2965_2964_statusHistory]" Alias="[sh]" />

    </IndexScan>

    </RelOp>

    <RelOp AvgRowSize="760" EstimateCPU="8.18611" EstimateIO="247.962" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7441770" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="256.148">

    <OutputList>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="auditId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vFrom" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vTo" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="availability" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="status" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="opMode" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyIn" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyOut" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyProcessed" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyRejected" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rate" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewSize" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="stopEventRefId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rejectEventRefId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="xnCode" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="version" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftAuditId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetname" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetdesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetRunCostPerHour" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assettype" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runname" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitId" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitDesc" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="materialCost" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewCostPerHourPerHead" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetName" />

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetDesc" />

    </OutputList>

    <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">

    <DefinedValues>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="auditId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vFrom" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="vTo" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="availability" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="status" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="opMode" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyIn" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyOut" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyProcessed" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="qtyRejected" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rate" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewSize" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="stopEventRefId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="rejectEventRefId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="xnCode" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="version" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftAuditId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetname" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetdesc" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assetRunCostPerHour" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="assettype" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="countUnitDesc" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftName" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="shiftDesc" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="runname" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productName" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productDesc" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitId" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="productCountUnitDesc" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="materialCost" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewName" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="crewCostPerHourPerHead" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetName" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[york]" Schema="[dbo]" Table="[productionAudit]" Alias="[p]" Column="cellAssetDesc" />

    </DefinedValue>

    </DefinedValues>

    <Object Database="[york]" Schema="[dbo]" Table="[productionAudit]" Index="[pk_productionAudit]" Alias="[p]" />

    </IndexScan>

    </RelOp>

    </Hash>

    </RelOp>

    </Top>

    </RelOp>

    </Update>

    </RelOp>

    </QueryPlan>

    </StmtSimple>

    </Statements>

    </Batch>

    </BatchSequence>

    </ShowPlanXML>

  • Jeff Moden (5/23/2010)


    I thought I'd "bump" this one for the OP since he provided everything requested but hasn't gotten a reply yet. 🙂

    Give me a break. 😉

    Dont you have weekends in your country ?

  • ss-457805 (5/21/2010)


    p.cellAssetDesc ,

    sh.auditid as lastStatusChangeAuditId

    INTO dbo.New2

    FROM dbo.productionAudit p

    Left JOIN statushistory sh on p.assetid = sh.assetid

    AND p.vFrom >= sh.vFrom

    AND p.vTo <= sh.vTo

    You need to be really careful here.

    As Jeff keeps saying: You seem to have an "accidental cross-join" here.

    The join above returns more than one row for each row in ProductionAudit. This means that your New2 table will contain more rows than the original productionAudit table. There will be duplicates on AuditID!

    This is probably not at all what you want.

    The question is what you want to do when there are multiple entries in the statushistory that matches the assetid. Whatever you do you must make sure that you only get at most one row from statushistory for each row in productionaudit.

    I would probably write this query something like this:

    p.cellAssetDesc ,

    (SELECT TOP 1 sh.auditid

    FROM statushistory sh

    WHERE p.assetid = sh.assetid

    AND p.vFrom >= sh.vFrom

    AND p.vTo <= sh.vTo

    -- NOTE! You might want an ORDER BY here to explicitly determine which row to use if there are multiple possibilities.

    ) as lastStatusChangeAuditId

    INTO dbo.New2

    FROM dbo.productionAudit p

    To speed this up you should make sure that you have a non-clustered index on statushistory (assetid, vFrom, vTo, auditid)

    But, after seeing what you are doing here it seems like you have several UPDATEs that are working on the same productionaudit table and you are now rewriting them as a sequence of SELECT INTO statements.

    If this is the case you should really think about doing all your updates as a single SELECT INTO. You could easily join with both statushistory and asset in the same SELECT INTO.

    If you have more updates using other tables you can join with them too.

    Using a single SELECT INTO will speed up things dramatically as long as you have appropriate indexes on the source tables.

    /SG

  • Stefan_G (5/24/2010)


    Jeff Moden (5/23/2010)


    I thought I'd "bump" this one for the OP since he provided everything requested but hasn't gotten a reply yet. 🙂

    Give me a break. 😉

    Dont you have weekends in your country ?

    Heh... oh yes. That's why I bumped it instead of digging into it. It wasn't directed at you... but I guess the bump worked just fine. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ss-457805 (5/24/2010)


    Please forgive my ignorance.I am not sure what u mean by "Real" Execution plan.

    Nope... please forgive my ignorance. I thought you had done something else because of the XML extension. All I needed to do was change the extension to ".SQLPLAN" and everything went fine.

    It looks like Stefan has it sussed along with a great explanation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • First of all, thank you for your continous help. It's much appreciated.

    The question is what you want to do when there are multiple entries in the statushistory that matches the assetid. Whatever you do you must make sure that you only get at most one row from statushistory for each row in productionaudit.

    There will be multiple records for an asset in statushistory table. In the statushistory table there can be 2 records for an asset, status of 0 and 2. So we need to update all the records in productionaudit table for that asset during the time specified in the criteria. So i don't think the below script will work in this case as it can only update with the top value.

    p.cellAssetDesc ,

    (SELECT TOP 1 sh.auditid

    FROM statushistory sh

    WHERE p.assetid = sh.assetid

    AND p.vFrom >= sh.vFrom

    AND p.vTo <= sh.vTo

    -- NOTE! You might want an ORDER BY here to explicitly determine which row to use if there are multiple possibilities.

    ) as lastStatusChangeAuditId

    INTO dbo.New2

    But, after seeing what you are doing here it seems like you have several UPDATEs that are working on the same productionaudit table and you are now rewriting them as a sequence of SELECT INTO statements.

    Yes you are rite, i've got several updates and i am doing this exercise because it takes a lot of time. I combined 7 updates and it does it in 5 minutes on a 2 million row.. But as soon as i add this last update to my select into statetment, it takes forever. Please see below the select INTO that i am doing:

    SELECT p.auditId ,

    p.vFrom ,

    p.vTo ,

    p.assetId ,

    p.availability ,

    p.status ,

    p.opMode ,

    p.qtyIn ,

    p.qtyOut ,

    p.qtyProcessed ,

    p.qtyRejected ,

    p.countUnitId ,

    p.rate ,

    p.shiftId ,

    p.runId ,

    p.productId ,

    p.crewId ,

    p.crewSize ,

    p.stopEventRefId ,

    p.rejectEventRefId ,

    p.xnCode ,

    p.version ,

    p.shiftAuditId ,

    p.cellAssetId ,

    --p.assetname ,

    --p.assetdesc ,

    --p.assetRunCostPerHour ,

    --p.assettype ,

    a.assetname ,

    a.description AS assetdesc ,

    a.runCostPerHour AS assetRunCostPerHour,

    a.assettype ,

    cu.description as countUnitDesc ,

    s.shiftname as shiftName ,

    s.description as shiftDesc ,

    pr.runname as runname ,

    prod.productname as productName ,

    prod.description as productDesc ,

    prod.countunitid as productCountUnitId ,

    cu1.description as productCountUnitDesc ,

    prod.unitmaterialcost as materialCost ,

    c.crewname as crewName ,

    c.costperhourperhead as crewCostPerHourPerHead ,

    a.assetname as cellAssetName ,

    a.description as cellAssetDesc ,

    sh.auditid as lastStatusChangeAuditId

    INTO dbo.New

    FROM dbo.productionAudit p

    Left JOIN dbo.asset a ON p.assetId = a.assetId

    Left join countunit cu on p.countunitid = cu.countunitid

    left join shift s on p.shiftid = s.shiftid

    left join productionrun pr on p.runid = pr.runid

    left join product prod on p.productid = prod.productid

    left join countunit cu1 on p.productcountunitid = cu1.countunitid

    left join crew c on p.crewid = c.crewid

    left join asset a1 on p.cellassetid = a1.assetid

    left join statushistory sh on p.assetid = sh.assetid

    AND p.vFrom >= sh.vFrom

    AND p.vTo <= sh.vTo

    Again thank you so much for your help and support..Much apreciated.

  • ss-457805 (5/24/2010)


    The question is what you want to do when there are multiple entries in the statushistory that matches the assetid. Whatever you do you must make sure that you only get at most one row from statushistory for each row in productionaudit.

    There will be multiple records for an asset in statushistory table. In the statushistory table there can be 2 records for an asset, status of 0 and 2. So we need to update all the records in productionaudit table for that asset during the time specified in the criteria. So i don't think the below script will work in this case as it can only update with the top value.

    Hmm

    We need to calculate exactly one single value for lastStatusChangeAuditId for each row in the ProductionAudit table.

    each ProductionAudit row has a single asset. The asset has multiple entries in the statushistory. I suppose that your join condition

    WHERE p.assetid = sh.assetid

    AND p.vFrom >= sh.vFrom

    AND p.vTo <= sh.vTo

    is supposed to get the correct row from statushistory.

    The problem is that for some productionaudit rows the above condition generates more than one row.

    In that situation my query just selected the first row returned - of course this is not a good solution because which row is returned is essentially random.

    That is why you need to determine which row to use if there are multiple rows that satisfy the join condition.

    I am curious, what is the business interpretation of p.vFrom, p.vTo, sh.vFrom and sh.vTo ?

    If you explain what you are trying to accomplish with this join maybe I can help you a bit more.

    /SG

  • Everytime a status changes it writes a record in the statushistory which then gets audited and is written in productionaudit table.

    So for instance the status was 2 between '2010-05-24 06:30:00' and '2010-05-24 07:30:00' . it will have a record in statushistory like below:

    vfrom: '2010-05-24 06:30:00'

    vto: '2010-05-24 07:30:00'

    status=2

    assetid:1

    unique Auditid:

    So in productionaudit it should update the laststatuschangeauditid with the auditid where the status =2 for assetid =1 between '2010-05-24 06:30:00' and '2010-05-24 07:30:00' .

    Same logic applies for status= 0.

    PLease let me know if this is still not clear.

  • ss-457805 (5/24/2010)


    Everytime a status changes it writes a record in the statushistory which then gets audited and is written in productionaudit table.

    So for instance the status was 2 between '2010-05-24 06:30:00' and '2010-05-24 07:30:00' . it will have a record in statushistory like below:

    vfrom: '2010-05-24 06:30:00'

    vto: '2010-05-24 07:30:00'

    status=2

    assetid:1

    unique Auditid:

    So in productionaudit it should update the laststatuschangeauditid with the auditid where the status =2 for assetid =1 between '2010-05-24 06:30:00' and '2010-05-24 07:30:00' .

    Same logic applies for status= 0.

    PLease let me know if this is still not clear.

    Hmm.

    Not quite clear.

    If I understand you correctly, a possible sequence of records could be something like this:

    statushistory:

    auditid assetid status vfrom vto

    10 1 0 20100520 20100530

    20 1 2 20100530 null

    (Or in words: The asset is first created on 20100520 with status 0. The status is then changed to 2 on 20100530)

    productionaudit:

    auditid assetid vfrom vto laststatuschangeauditid

    1 1 20100520 20100521 10

    1 1 20100521 20100522 10

    1 1 20100522 20100525 10

    1 1 20100525 20100530 10

    1 1 20100530 20100601 20

    1 1 20100601 20100605 20

    If I have interpreted you correctly laststatuschangeauditid could be calculated using:

    SELECT

    p.auditid,

    (SELECT TOP 1 auditid

    FROM statushistory sh

    WHERE sh.assetid = p.assetid AND sh.vFrom <= p.vFrom

    ORDER BY sh.vFrom DESC) as laststatuschangeauditid

    FROM productionaudit p

    To get maximum speed use a nonclustered index (assetid,vFrom,auditid) on statushistory

  • Says that you update 5% row each time in big table A, you still have to avoid table scan.

    To avoid table scan Table A, you can create index for table B and index scan table B.

    Please check FILLFACTOR , FILLFACTOR should less than 80% if you have too many varchar column to be updated.

  • Hi Stefan

    your interpretation is correct. I have been on client site for the last two days. Will test it today when i am in the office.

  • Any news about this ?

    I am curious 😎

  • Sorry Stefan.. Just been really busy with Upgrades.

    Here's the feedback:

    On 7 million rows:

    the updates took 10 minutes with your suggestion.

    Creating the indexes took about 25 minutes. So overall 35 minutes.

    I can live with that on customers with Data less than 10 Million. The real test will be when there's

    > 20 Million rows. Will see how long that takes?

    I would like to thank you for all your help.. I will update this thread again when i have tested it on >20 Million rows. Any tips on this? shall i follow the same procedure as i did for 7 Mllion rows?

Viewing 13 posts - 31 through 42 (of 42 total)

You must be logged in to reply to this topic. Login to reply