SORT in Execution plan having higher cost

Question

SORT in Execution plan having higher cost

Meghana

SSC Eights!

Points: 840
More actions
May 2, 2005 at 2:06 am

#88262

Hi
I am getting a SORT in execution plan which uses 75% of the cost . Is there any way to reduce this. the sort is on the indexed column
this is the query I am facing problems with
insert dbo.idmbillingdocumentitem
(

        SalesDocumentNbr,
        SalesDocumentItemNbr,
        BillingDocumentNbr,
        BillingDocumentItemNbr,
        PlantCode,
        MaterialNbr,
        MaterialDesc,
        ActualInvoicedQty,
        SalesMeasurementUnitCode,
        ReferenceDocumentNbr,
        GoodsDepartureCountryCode,
        SalesOfficeCode,
        VolumeLevelCode,
        MOLPPoolCode,
        ProgramCode,
        OrderReasonCode,
        CreatedDtTm,
        CreatedByUserName,
        CurrencyCode,
        MSGLCompanyCode,
        ProductDivisionCode,
        SubTotal2Amt,
        SubTotal1Amt,
        DocumentCurrencyNetAmt,
        DocumentCurrencyTaxAmt,
        TaxSubTotalAmt
)
select

   bdi.SalesDocumentNbr,
   bdi.SalesDocumentItemNbr,
   bdi.BillingDocumentNbr,
   bdi.BillingDocumentItemNbr,
   bdi.PlantCode,
   bdi.MaterialNbr,
   bdi.MaterialDesc,
   bdi.ActualInvoicedQty,
   bdi.SalesMeasurementUnitCode,
   bdi.ReferenceDocumentNbr,
   bdi.GoodsDepartureCountryCode,
   bdi.SalesOfficeCode,
   bdi.VolumeLevelCode,
   bdi.MOLPPoolCode,
   bdi.ProgramCode,
   bdi.OrderReasonCode,
   bdi.CreatedDtTm,
   bdi.CreatedByUserName,
   bd.CurrencyCode,
   bd.MSGLCompanyCode,
   pr.ProductDivisionCode,
   SubTotal2Amt =
      CASE
         WHEN bdtmap.NegativeRevenueInd = 'Y' THEN bdi.SubTotal2Amt * -1
         ELSE bdi.SubTotal2Amt
      END,
   SubTotal1Amt =
      CASE
         WHEN bdtmap.NegativeRevenueInd = 'Y' THEN bdi.SubTotal1Amt * -1
         ELSE bdi.SubTotal1Amt
      END,
   DocumentCurrencyNetAmt =
      CASE
         WHEN bdtmap.NegativeRevenueInd = 'Y' THEN bdi.DocumentCurrencyNetAmt * -1
         ELSE bdi.DocumentCurrencyNetAmt
      END,
   DocumentCurrencyTaxAmt =
      CASE
         WHEN bdtmap.NegativeRevenueInd = 'Y' THEN bdi.DocumentCurrencyTaxAmt * -1
         ELSE bdi.DocumentCurrencyTaxAmt
      END,
   TaxSubTotalAmt =
      CASE
         WHEN bdtmap.NegativeRevenueInd = 'Y' THEN bdi.TaxSubTotalAmt * -1
         ELSE bdi.TaxSubTotalAmt
      END
from
        dbo.BillingDocumentItem bdi
        join dbo.wrkIDMBillingDocumentItem wBdi on
                wBdi.BillingDocumentNbr = bdi.BillingDocumentNbr
                and wBdi.BillingDocumentItemNbr = bdi.BillingDocumentItemNbr
        join dbo.BillingDocument bd on
                bd.BillingDocumentNbr = bdi.BillingDocumentNbr
        left join dbo.WHNBillDocTypeToNegativeRevMap bdtmap on
                bdtmap.BillingDocumentTypeCode = bd.BillingDocumentTypeCode
        left join dbo.ProductDim pr on
                bdi.materialNbr=pr.productnbr .

this is the plan
|--Clustered Index Insert(OBJECT[dwfactory].[dbo].[IDMBillingDocumentItem].[UCIIDMBillingDocumentItem]), SET[IDMBillingDocumentItem].[EuroCurrencyTaxAmt]=NULL, [IDMBillingDocumentItem].[CompanyCurrencyTaxAmt]=NULL, [IDMBillingDocumentItem].[USDTaxAmt
       |--Sort(ORDER BY[bdi].[BillingDocumentNbr] ASC, [bdi].[BillingDocumentItemNbr] ASC))
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE[Expr1006]=Convert([bd].[CurrencyCode]), [Expr1007]=If ([bdtmap].[NegativeRevenueInd]='Y') then ([bdi].[SubTotal2Amt]*-1.00) else [bdi].[SubTotal2Amt], [Expr1008]=If ([bdtmap].[NegativeRevenueInd]='Y') then ([bdi]
                      |--Hash Match(Right Outer Join, HASH[tblProductDim].[ProductNbr])=([bdi].[MaterialNbr]), RESIDUAL[bdi].[MaterialNbr]=[tblProductDim].[ProductNbr]))
                           |--Clustered Index Scan(OBJECT[Warehouse].[dbo].[tblProductDim].[CUnqIdxtblProductDim]))
                           |--Hash Match(Right Outer Join, HASH[bdtmap].[BillingDocumentTypeCode])=([bd].[BillingDocumentTypeCode]), RESIDUAL[bdtmap].[BillingDocumentTypeCode]=[bd].[BillingDocumentTypeCode]))
                                |--Clustered Index Scan(OBJECT[dwfactory].[dbo].[WHNBillDocTypeToNegativeRevMap].[PK__WHNBillDocTypeTo__4CAB505A] AS [bdtmap]))
                                |--Merge Join(Inner Join, MANY-TO-MANY MERGE[wBdi].[BillingDocumentNbr])=([bd].[BillingDocumentNbr]), RESIDUAL[wBdi].[BillingDocumentNbr]=[bd].[BillingDocumentNbr]))
                                     |--Merge Join(Inner Join, MANY-TO-MANY MERGE[bdi].[BillingDocumentNbr])=([wBdi].[BillingDocumentNbr]), RESIDUAL[bdi].[BillingDocumentNbr]=[wBdi].[BillingDocumentNbr] AND [bdi].[BillingDocumentItemNbr]=[wBdi].[Billin
                                     |    |--Clustered Index Scan(OBJECT[dwfactory].[dbo].[BillingDocumentItem].[UCIBillDocItem] AS [bdi]), ORDERED FORWARD)
                                     |    |--Clustered Index Scan(OBJECT[dwfactory].[dbo].[wrkIDMBillingDocumentItem].[UCIwrkIDMBillingDocumentItem] AS [wBdi]), ORDERED FORWARD)
                                     |--Clustered Index Scan(OBJECT[dwfactory].[dbo].[BillingDocument].[UCIBillDoc] AS [bd]), ORDERED FORWARD)
Can someone help me with this ?
TIA
Meghana
Regards,
Meghana

Viewing 3 posts - 1 through 2 (of 2 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

The divide by percentage in the plans is a relative value. 75% isn't necessarily something to be concerned about. It depends on how "much" 100% is.

If 100% is "a problem", then it may be a good idea to look at the part that constitutes as much as 75% of the total.

However, if the overall performance is ok, then 75% by itself is of no concern.

So, the question then becomes: Do you have an overall performance problem with this query? (ie is it "not good enough"?)

/Kenneth

Antares686 SSC Guru Points: 125444 More actions · Answer 2

I beleive the sort i due to the Custered Index from what I can see. It is sorting for insert performance to minumize data movement on pages as much as possible. For example suppose you have a page with one slot open fo data and al the data on the page begins AA to begining AY for the field that is clustered. Well if you have an insert that would insert AZ as well as a record AD and if left unsorted would insert AZ first then you woul get a page split where sorted AD goes in and AZ may be on the next page. I would not be concerned unless as stated you see a perfomance issue. Overall the plan looks clean and optimal.