May 24, 2010 at 1:40 am
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>
May 24, 2010 at 5:09 am
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 ?
May 24, 2010 at 5:38 am
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
May 24, 2010 at 7:05 am
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
Change is inevitable... Change for the better is not.
May 24, 2010 at 7:11 am
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
Change is inevitable... Change for the better is not.
May 24, 2010 at 8:00 am
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.
May 24, 2010 at 9:08 am
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
May 24, 2010 at 10:24 am
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.
May 24, 2010 at 3:39 pm
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
May 25, 2010 at 7:34 am
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.
May 26, 2010 at 1:47 am
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.
May 27, 2010 at 9:10 am
Any news about this ?
I am curious 😎
May 27, 2010 at 10:41 am
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