SQL Server Performance Forum – Threads Archive

Update statements performance

Hello, I am relativly new to sql server. I am using 2005.
I have a large database (100gb) And ‘all’ I want to do is update each record based on several rules. Complete the row with data form a small lookup table etc, etc. So I build about 30+ stored procedures, each with several update statements on the large file. Sometimes I need to join a small table to find some data and a few times I need a temp lookup file, witch is a summary of the large table. When I run the stored procedures in a row, It will run for almost a week.
I tried the query optimizer tool, but what ever I do, it says that I don’t need any indexes. Would it be better to just step row by row through the table and apply the rules or does anyone have some other help. TIA, Gerben Kessen

I’ve moved to relevant forum.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

something is seriously wrong if it takes a week to run
the execution plan should say that what you are doing is horribly expensive
or there is something wrong with your system configuration try saving the execution plan in XML format (*.showplan) then paste the xml into a reply box here,
i will try to see if it can be recovered and displayed graphically

I would recommend dividing the problem and working on the most expensive pieces. Look for the stored procedures which take most of the time. Just a small tip – Breaking the update operations into small batches should help. Gaurav
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
http://blogs.msdn.com/gauravbi/default.aspx

This is one of the stored procedures which runs about 30 hours. <?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />si="http://www.w3.org/2001/XMLSchema-instance" xmlns<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />sd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2040.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="3" StatementEstRows="169773" StatementId="3" StatementOptmLevel="FULL" StatementSubTreeCost="5366.09" StatementText="UPDATE FLOWFILE_AU#xD;#xA;SET cntry_cdeu = Rf_country.cntry_ceu,#xD;#xA; cntry_cdcm = Rf_country.cntry_ccm,#xD;#xA; cntry_cdrpu = Rf_country.cntry_crpu,#xD;#xA; cntry_cdprnt = Rf_country.cntry_cprnt,#xD;#xA; cntry_cdbu = Rf_country.cntry_cbu#xD;#xA;FROM Rf_country#xD;#xA;WHERE RTRIM(cntry_ccntry) = RTRIM(ff_cdcntry)#xD;#xA;#xD;#xA;#xD;#xA; #xD;#xA;" StatementType="UPDATE"> <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="119"> <RelOp AvgRowSize="9" EstimateCPU="0.169773" EstimateIO="172.259" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="169773" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Table Update" EstimatedTotalSubtreeCost="5366.09"> <OutputList /> <Update WithUnorderedPrefetch="true"> <Object Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" /> <SetPredicate> <ScalarOperator ScalarString="[ICEA].[dbo].[flowfile_au].[cntry_cdeu] = [ICEA].[dbo].[rf_country].[cntry_ceu],[ICEA].[dbo].[flowfile_au].[cntry_cdcm] = [Expr1008],[ICEA].[dbo].[flowfile_au].[cntry_cdrpu] = [ICEA].[dbo].[rf_country].[cntry_crpu],[ICEA].[dbo].[flowfile_au].[cntry_cdprnt] = [ICEA].[dbo].[rf_country].[cntry_cprnt],[ICEA].[dbo].[flowfile_au].[cntry_cdbu] = [ICEA].[dbo].[rf_country].[cntry_cbu]"> <ScalarExpressionList /> </ScalarOperator> </SetPredicate> <RelOp AvgRowSize="28" EstimateCPU="0.0169773" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="169773" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="5193.66"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> <ColumnReference Column="Expr1008" /> </OutputList> <Top RowCount="true" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(0)"> <Const ConstValue="(0)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="28" EstimateCPU="0.260303" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="169773" LogicalOp="Gather Streams" NodeId="3" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="5193.65"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> <ColumnReference Column="Expr1008" /> </OutputList> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />arallelism> <RelOp AvgRowSize="28" EstimateCPU="0.0153047" EstimateIO="0.00328125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="169773" LogicalOp="Eager Spool" NodeId="4" Parallel="true" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="5193.39"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> <ColumnReference Column="Expr1008" /> </OutputList> <Spool> <RelOp AvgRowSize="28" EstimateCPU="0.00424432" EstimateI O="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="169773" LogicalOp="Compute Scalar" NodeId="5" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="5193.37"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> <ColumnReference Column="Expr1008" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1008" /> <ScalarOperator ScalarString="CONVERT_IMPLICIT(char(1),[ICEA].[dbo].[rf_country].[cntry_ccm],0)"> <Convert DataType="char" Length="1" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="31" EstimateCPU="2.47424" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="169773" LogicalOp="Aggregate" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5193.36"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> </OutputList> <MemoryFractions Input="0.900154" Output="1" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_ccm])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_ceu])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_cprnt])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifi er> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_cbu])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_crpu])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Column="Bmk1000" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[Bmk1000] = [Bmk1000]"> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Column="Bmk1000" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Bmk1000" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="31" EstimateCPU="1.11322" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="679091" LogicalOp="Repartition Streams" NodeId="7" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="5190.89"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> </OutputList> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />arallelism PartitioningType="Hash"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />artitionColumns> <ColumnReference Column="Bmk1000" /> </PartitionColumns> <RelOp AvgRowSize="31" EstimateCPU="71.9805" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="679091" LogicalOp="Partial Aggregate" NodeId="8" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5189.77"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ColumnReferen ce Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_ccm])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_ceu])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_cprnt])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_cbu])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> <ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_crpu])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Column="Bmk1000" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[Bmk1000] = [Bmk1000]"> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Column="Bmk1000" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Bmk1000" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="31" EstimateCPU="182.019" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="40745400" LogicalOp="Inner Join" NodeId="9" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5117.79"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> </OutputList> <MemoryFractions Input="1" Output="0.0998464" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Column="Expr1023" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Column="Expr1024" /> </HashKeysProbe> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />robeResidual> <ScalarOperator ScalarString="[Expr1023]=[Expr1024]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1023" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1024" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="28" EstimateCPU="0.0288576" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="240" LogicalOp="Repartition Streams" NodeId="10" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.062928"> <OutputList> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> <ColumnReference Column="Expr1023" /> </Outp utList> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />arallelism PartitioningType="Hash"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />artitionColumns> <ColumnReference Column="Expr1023" /> </PartitionColumns> <RelOp AvgRowSize="28" EstimateCPU="6E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="240" LogicalOp="Compute Scalar" NodeId="11" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0340704"> <OutputList> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> <ColumnReference Column="Expr1023" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1023" /> <ScalarOperator ScalarString="rtrim([ICEA].[dbo].[rf_country].[cntry_ccntry])"> <Intrinsic FunctionName="rtrim"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccntry" /> </Identifier> </ScalarOperator> </Intrinsic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="26" EstimateCPU="0.0297776" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="240" LogicalOp="Distribute Streams" NodeId="12" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0340644"> <OutputList> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccntry" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> </OutputList> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />arallelism PartitioningType="RoundRobin"> <RelOp AvgRowSize="26" EstimateCPU="0.000421" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="240" LogicalOp="Table Scan" NodeId="13" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.00428674"> <OutputList> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccntry" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" />&l t;br /> </OutputList> <TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccntry" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /> </DefinedValue> </DefinedValues> <Object Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" /> </TableScan> </RelOp> </Parallelism> </RelOp> </ComputeScalar> </RelOp> </Parallelism> </RelOp> <RelOp AvgRowSize="20" EstimateCPU="38.1816" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43911000" LogicalOp="Repartition Streams" NodeId="17" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="4935.71"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Column="Expr1024" /> </OutputList> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />arallelism PartitioningType="Hash"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />artitionColumns> <ColumnReference Column="Expr1024" /> </PartitionColumns> <RelOp AvgRowSize="20" EstimateCPU="1.09777" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43911000" LogicalOp="Compute Scalar" NodeId="18" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="4897.53"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Column="Expr1024" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1024" /> <ScalarOperator ScalarString="rtrim([ICEA].[dbo].[flowfile_au].[ff_cdcntry])"> <Intrinsic FunctionName="rtrim"> <ScalarOperator> <Identifier> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" Column="ff_cdcntry" /> </Identifier> </ScalarOperator> </Intrinsic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="18" EstimateCPU="12.0755" EstimateIO="4884.36" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43911000" LogicalOp="Table Scan" NodeId="19" Parallel="true" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="4896.43"> <OutputList> <ColumnReference Column="Bmk1000" /> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" Column="ff_cdcntry" /> </OutputList> <TableScan Ordered="true" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Column="Bmk1000" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" Column="ff_cdcntry" /> </DefinedValue> </DefinedValues> <Object Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" /> </TableScan> </RelOp> </ComputeScalar> </RelOp> </Parallelism> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Hash> </RelOp> </ComputeScalar> </RelOp> </Spool> </RelOp> </Parallelism> </RelOp> </Top> </RelOp> </Update> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>

assuming that it was your intent to update a moderate number rows part of your problem comes from:
WHERE RTRIM(cntry_ccntry) = RTRIM(ff_cdcntry) you need store data in your tables correctly so you do not do silly things like this, which kills the execution plan even then, it should still have run,
it might also be a parallelism problem or a combination parallelism HT problem disable parallelism and or HT I am out of country right now and do not have access to my 2005 system if someone could paste the above XML into a *.sqlplan file, then try to open it in SQL 2005 Management Studio and tell me if it shows a graphical plan,
if so, this will significantly improve the ability of others to diagnose query problems through this forum

Joe, Yes it does show plan in management studio. Table Scan on [ICEA].[dbo].[flowfile_au] is taking 91% of time and spitting out 431 million rows. There is a hash join on the WHERE RTRIM(cntry_ccntry) = RTRIM(ff_cdcntry) clause. See if you can convert the same to Nested Loop join by writing the query in smarter way. Gaurav
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
http://blogs.msdn.com/gauravbi/default.aspx

]]>