SQL Server Performance

Update statements performance

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by gerben, May 22, 2006.

  1. gerben New Member

    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


  2. Luis Martin Moderator

    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.



  3. joechang New Member

    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
  4. gaurav_bindlish New Member

    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
  5. gerben New Member

    This is one of the stored procedures which runs about 30 hours.<br /><br /><br />&lt;?xml version="1.0" encoding="utf-16"?&gt;<br />&lt;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"&gt;<br /> &lt;BatchSequence&gt;<br /> &lt;Batch&gt;<br /> &lt;Statements&gt;<br /> &lt;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"&gt;<br /> &lt;StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" /&gt;<br /> &lt;QueryPlan CachedPlanSize="119"&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList /&gt;<br /> &lt;Update WithUnorderedPrefetch="true"&gt;<br /> &lt;Object Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" /&gt;<br /> &lt;SetPredicate&gt;<br /> &lt;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]"&gt;<br /> &lt;ScalarExpressionList /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/SetPredicate&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;ColumnReference Column="Expr1008" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;Top RowCount="true" IsPercent="false" WithTies="false"&gt;<br /> &lt;TopExpression&gt;<br /> &lt;ScalarOperator ScalarString="(0)"&gt;<br /> &lt;Const ConstValue="(0)" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/TopExpression&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;ColumnReference Column="Expr1008" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />arallelism&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;ColumnReference Column="Expr1008" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;Spool&gt;<br /> &lt;RelOp AvgRowSize="28" EstimateCPU="0.00424432" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="169773" LogicalOp="Compute Scalar" NodeId="5" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="5193.37"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;ColumnReference Column="Expr1008" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;ComputeScalar&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Column="Expr1008" /&gt;<br /> &lt;ScalarOperator ScalarString="CONVERT_IMPLICIT(char(1),[ICEA].[dbo].[rf_country].[cntry_ccm],0)"&gt;<br /> &lt;Convert DataType="char" Length="1" Style="0" Implicit="true"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Convert&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;MemoryFractions Input="0.900154" Output="1" /&gt;<br /> &lt;Hash&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_ccm])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_ceu])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_cprnt])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_cbu])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_crpu])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;HashKeysBuild&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;/HashKeysBuild&gt;<br /> &lt;BuildResidual&gt;<br /> &lt;ScalarOperator ScalarString="[Bmk1000] = [Bmk1000]"&gt;<br /> &lt;Compare CompareOp="IS"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/BuildResidual&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />arallelism PartitioningType="Hash"&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />artitionColumns&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;/PartitionColumns&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;MemoryFractions Input="0" Output="0" /&gt;<br /> &lt;Hash&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_ccm])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_ceu])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_cprnt])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_cbu])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;ScalarOperator ScalarString="ANY([ICEA].[dbo].[rf_country].[cntry_crpu])"&gt;<br /> &lt;Aggregate AggType="ANY" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;HashKeysBuild&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;/HashKeysBuild&gt;<br /> &lt;BuildResidual&gt;<br /> &lt;ScalarOperator ScalarString="[Bmk1000] = [Bmk1000]"&gt;<br /> &lt;Compare CompareOp="IS"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/BuildResidual&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;MemoryFractions Input="1" Output="0.0998464" /&gt;<br /> &lt;Hash&gt;<br /> &lt;DefinedValues /&gt;<br /> &lt;HashKeysBuild&gt;<br /> &lt;ColumnReference Column="Expr1023" /&gt;<br /> &lt;/HashKeysBuild&gt;<br /> &lt;HashKeysProbe&gt;<br /> &lt;ColumnReference Column="Expr1024" /&gt;<br /> &lt;/HashKeysProbe&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />robeResidual&gt;<br /> &lt;ScalarOperator ScalarString="[Expr1023]=[Expr1024]"&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Expr1023" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Expr1024" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/ProbeResidual&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;ColumnReference Column="Expr1023" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />arallelism PartitioningType="Hash"&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />artitionColumns&gt;<br /> &lt;ColumnReference Column="Expr1023" /&gt;<br /> &lt;/PartitionColumns&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;ColumnReference Column="Expr1023" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;ComputeScalar&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Column="Expr1023" /&gt;<br /> &lt;ScalarOperator ScalarString="rtrim([ICEA].[dbo].[rf_country].[cntry_ccntry])"&gt;<br /> &lt;Intrinsic FunctionName="rtrim"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccntry" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Intrinsic&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccntry" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />arallelism PartitioningType="RoundRobin"&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccntry" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccntry" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ccm" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_ceu" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cprnt" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_cbu" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" Column="cntry_crpu" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[ICEA]" Schema="[dbo]" Table="[rf_country]" /&gt;<br /> &lt;/TableScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Parallelism&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/ComputeScalar&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Parallelism&gt;<br /> &lt;/RelOp&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Column="Expr1024" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />arallelism PartitioningType="Hash"&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />artitionColumns&gt;<br /> &lt;ColumnReference Column="Expr1024" /&gt;<br /> &lt;/PartitionColumns&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Column="Expr1024" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;ComputeScalar&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Column="Expr1024" /&gt;<br /> &lt;ScalarOperator ScalarString="rtrim([ICEA].[dbo].[flowfile_au].[ff_cdcntry])"&gt;<br /> &lt;Intrinsic FunctionName="rtrim"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" Column="ff_cdcntry" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Intrinsic&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;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"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" Column="ff_cdcntry" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;TableScan Ordered="true" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Column="Bmk1000" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" Column="ff_cdcntry" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[ICEA]" Schema="[dbo]" Table="[flowfile_au]" /&gt;<br /> &lt;/TableScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/ComputeScalar&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Parallelism&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Hash&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Hash&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Parallelism&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Hash&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/ComputeScalar&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Spool&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Parallelism&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Top&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Update&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/QueryPlan&gt;<br /> &lt;/StmtSimple&gt;<br /> &lt;/Statements&gt;<br /> &lt;/Batch&gt;<br /> &lt;/BatchSequence&gt;<br />&lt;/ShowPlanXML&gt;
  6. joechang New Member

    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
  7. gaurav_bindlish New Member

    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

Share This Page