Issue with sort/distinct when updating

[collierd2]

Hello

I am using SQL server 2000

I have a piece of code that performs a large amount of updates against every row on a table

The code runs in about a couple of minutes against 5 of my 6 databases
The other one takes over an hour
All 6 databases are sychronised (Using SQL compare), such that the indexes etc. are identical

When I look into the execution plan, it's creating a Sort/Distinct step that looks to be the source of the problem

How does this happen with an update
There is no grouping in my code. Just a number of left outer joins to ensure onene updates

Does anybody have any thoughts?
I've looked on a few sites and can't find any guidance

Thanks

Damian.

 

[SimonSellick]

If the databases share a common structure and they are running under the same version of SQL Server with the same config and options settings, then the only points where there might be differences are host platform (probably outside the scope of this forum, I imagine, but you could just check log file, data file and free space sizes) and data. Do the left joins you mentioned specify unindexed columns, and are there widely varying row counts in the joined tables across the fast and slow databases?

 

[collierd2]

Thanks Simon

Row counts do vary from database to database.
I've now realised that all the joins are actually appearing as a right out join within the execution plan - despite ALL join been left outer joins (?!?)

There's something strange happening here as I really can't think why it would want to do a distinct/sort on a onene update

 

[gmmastros]

Can you post the code you are running?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[collierd2]

update FactSalesOrderLineAggregate
set 
Company_ID = isnull(DimOpCo.Company_ID,'*NONE'),
FullAccountingDate_AT = isnull(DimAccountingPeriod.FullAccountingDate_AT,0),
CustomerBranchID_AT = isnull(DimBranch.BranchID_AT,'*NONE'),
CustomerCountryCode_AT = isnull(DimCountry.CountryCode_AT,'*NONE'),
Salesman_SK = isnull(DimCustomerAccountSK.Salesman_SK,0),
CustomerDefaultSalesmanID_AT = isnull(DimCustomerAccountSK.CustomerDefaultSalesmanID_AT,'*NONE'),
CustomerID_AT = isnull(DimCustomerAccountSK.CustomerID_AT,'*NONE'),
CustomerClassification_SK = isnull(DimCustomerAccountSK.CustomerClassification_SK,0),
CustomerClassificationID_AT = isnull(DimCustomerAccountSK.CustomerClassificationID_AT,'*NONE'),
CustomerCategory1_SK = isnull(DimCustomerAccountSK.CustomerCategory1_SK,0),
CustomerCategory1Code_AT = isnull(DimCustomerAccountSK.CustomerCategory1Code_AT,'*NONE'),
CustomerCategory2_SK = isnull(DimCustomerAccountSK.CustomerCategory2_SK,0),
CustomerCategory2Code_AT = isnull(DimCustomerAccountSK.CustomerCategory2Code_AT,'*NONE'),
CustomerCategory3_SK = isnull(DimCustomerAccountSK.CustomerCategory3_SK,0),
CustomerCategory3Code_AT = isnull(DimCustomerAccountSK.CustomerCategory3Code_AT,'*NONE'),
CustomerCategory4_SK = isnull(DimCustomerAccountSK.CustomerCategory4_SK,0),
CustomerCategory4Code_AT = isnull(DimCustomerAccountSK.CustomerCategory4Code_AT,'*NONE'),
CustomerCategory5_SK = isnull(DimCustomerAccountSK.CustomerCategory5_SK,0),
CustomerCategory5Code_AT = isnull(DimCustomerAccountSK.CustomerCategory5Code_AT,'*NONE'),
CustomerCategory6_SK = isnull(DimCustomerAccountSK.CustomerCategory6_SK,0),
CustomerCategory6Code_AT = isnull(DimCustomerAccountSK.CustomerCategory6Code_AT,'*NONE'),
BusinessUnit_SK = isnull(DimCustomerAccountSK.BusinessUnit_SK,0),
BusinessUnitCode_AT = isnull(DimCustomerAccountSK.BusinessUnitCode_AT,'*NONE'),
Area_SK = isnull(DimCustomerAccountSK.Area_SK,0),
AreaCode_AT = isnull(DimCustomerAccountSK.AreaCode_AT,'*NONE'),
BuyingGroup_SK = isnull(DimCustomerAccountSK.BuyingGroup_SK,0),
BuyingGroupCode_AT = isnull(DimCustomerAccountSK.BuyingGroupCode_AT,'*NONE'),
ItemID_AT = isnull(DimItemSK.Item_SK,0),
ItemGroup_SK = isnull(DimItemSK.ItemGroup_SK,0),
ItemGroupCode_AT = isnull(DimItemSK.ItemGroupCode_AT,'*NONE'),
ItemReportingGroup_SK = isnull(DimItemSK.ItemReportingGroup_SK,0),
ItemReportingGroupCode_AT = isnull(DimItemSK.ItemReportingGroupCode_AT,'*NONE'),
ItemBrandName_SK = isnull(DimItemSK.ItemBrandName_SK,0),
ItemBrandNameCode_AT = isnull(DimItemSK.ItemBrandNameCode_AT,'*NONE'),
ItemSupplierAccount_SK = isnull(DimItemSK.ItemSupplierAccount_SK,0),
ItemMainSupplierID_AT = isnull(DimItemSK.ItemMainSupplierID_AT,'*NONE'),
ProductAppearance_SK = isnull(DimItemSK.ProductAppearance_SK,0),
ProductAppearanceCode_AT = isnull(DimItemSK.ProductAppearanceCode_AT,'*NONE'),
ItemCategory1_SK = isnull(DimItemSK.ItemCategory1_SK,0),
ItemCategory1Code_AT = isnull(DimItemSK.ItemCategory1Code_AT,'*NONE'),
ItemResponsible_SK = isnull(DimItemSK.ItemResponsible_SK,0),
ItemResponsibleCode_AT = isnull(DimItemSK.ItemResponsibleCode_AT,'*NONE'),
ItemSector_SK = isnull(DimItemSK.ItemSector_SK,0),
ItemSectorCode_AT = isnull(DimItemSK.ItemSectorCode_AT,'*NONE'),
ItemClass_SK = isnull(DimItemSK.ItemClass_SK,0),
ItemClassCode_AT = isnull(DimItemSK.ItemClassCode_AT,'*NONE'),
ItemPriceGroup_SK = isnull(DimItemSK.ItemPriceGroup_SK,0),
ItemPriceGroupCode_AT = isnull(DimItemSK.ItemPriceGroupCode_AT,'*NONE'),
ItemAccountGroup_SK = isnull(DimItemSK.ItemAccountGroup_SK,0),
ItemAccountGroupCode_AT = isnull(DimItemSK.ItemAccountGroupCode_AT,'*NONE'),
ItemCategory2_SK = isnull(DimItemSK.ItemCategory2_SK,0),
ItemCategory2Code_AT = isnull(DimItemSK.ItemCategory2Code_AT,'*NONE'),
ItemMillReviewProductGroup_SK = isnull(DimItemSK.ItemMillReviewProductGroup_SK,0),
ItemMillReviewProductGroupCode_AT = isnull(DimItemSK.ItemMillReviewProductGroupCode_AT,'*NONE'),
ItemCategory4_SK = isnull(DimItemsK.ItemCategory4_SK,0),
ItemCategory4Code_AT = isnull(DimItemSK.ItemCategory4Code_AT,'*NONE'),
ItemCategory6_SK = isnull(DimItemSK.ItemCategory6_SK,0),
ItemCategory6Code_AT = isnull(DimItemSK.ItemCategory6Code_AT,'*NONE'),
ItemStockUnit_SK = isnull(DimItemSK.ItemStockUnit_SK,0),
ItemStockUnitID_AT = isnull(DimItemSK.ItemStockUnitID_AT,'*NONE'),
ItemBrandOwner_SK = isnull(DimItemSK.ItemBrandOwner_SK,0),
ItemBrandOwnerCode_AT = isnull(DimItemSK.ItemBrandOwnerCode_AT,'*NONE'),
ProductName_SK = isnull(DimItemSK.ProductName_SK,0),
ProductNameCode_AT = isnull(DimItemSK.ProductNameCode_AT,'*NONE'),
ItemCaliper_SK = isnull(DimItemSK.ItemCaliper_SK,0),
ItemCaliperValue_AT = isnull(DimItemSK.ItemCaliperValue_AT,'*NONE'),
ItemColourGroupReporting_SK = isnull(DimItemSK.ItemColourGroupReporting_SK,0),
ItemColourGroupReportingValue_AT = isnull(DimItemSK.ItemColourGroupReportingValue_AT,'*NONE'),
ItemFinishFrontType_SK = isnull(DimItemSK.ItemFinishFrontType_SK,0),
ItemFinishFrontTypeValue_AT = isnull(DimItemSK.ItemFinishFrontTypeValue_AT,'*NONE'),
ItemForestryClassification_SK = isnull(DimItemSK.ItemForestryClassification_SK,0),
ItemForestryClassificationValue_AT = isnull(DimItemSK.ItemForestryClassificationValue_AT,'*NONE'),
ItemGrainDirection_SK = isnull(DimItemSK.ItemGrainDirection_SK,0),
ItemGrainDirectionValue_AT = isnull(DimItemSK.ItemGrainDirectionValue_AT,'*NONE'),
ItemGSM_SK = isnull(DimItemSK.ItemGSM_SK,0),
ItemGSMValue_AT = isnull(DimItemSK.ItemGSMValue_AT,'*NONE'),
ItemLength_SK = isnull(DimItemSK.ItemLength_SK,0),
ItemLengthValue_AT = isnull(DimItemSK.ItemLengthValue_AT,'*NONE'),
ItemPackaging_SK = isnull(DimItemSK.ItemPackaging_SK,0),
ItemPackagingValue_AT = isnull(DimItemSK.ItemPackagingValue_AT,'*NONE'),
ItemSizeCode_SK = isnull(DimItemSK.ItemSizeCode_SK,0),
ItemSizeCodeValue_AT = isnull(DimItemSK.ItemSizeCodeValue_AT,'*NONE'),
ItemWidth_SK = isnull(DimItemSK.ItemWidth_SK,0),
ItemWidthValue_AT = isnull(DimItemSK.ItemWidthValue_AT,'*NONE'),
SalesOrderTypeCode_AT = isnull(DimOrderType.OrderTypeCode_AT,'*NONE'),
SourceWarehouseID_AT = isnull(DimWarehouse.WarehouseID_AT,'*NONE'),
OrderHandlerID_AT = isnull(DimHandler.HandlerID_AT,'*NONE'),
TransactionCurrencyCode_AT = isnull(DimCurrency.CurrencyCode_AT,'*NONE'),
SalesOrderLineStatusID_AT = isnull(DimSalesOrderLineStatus.SalesOrderLineStatusID_AT,'*NONE'),
SalesOrderPaymentTermsID_AT = isnull(DimPaymentTerms.PaymentTermsID_AT,'*NONE'),
MannerOfTransportID_AT = isnull(DimMannerOfTransport.MannerOfTransportID_AT,'*NONE')
from FactSalesOrderLineAggregate
left outer join DimOpCo on (FactSalesOrderLineAggregate.Company_SK = DimOpCo.Company_SK and DimOpCo.Primary_OpCo_AT = 1)
left join DimAccountingPeriod
  on FactSalesOrderLineAggregate.AccountingPeriod_SK = DimAccountingPeriod.AccountingPeriod_SK 
left join DimBranch
  on DimBranch.Branch_SK = FactSalesOrderLineAggregate.Branch_SK
left join DimCountry
  on DimCountry.Country_SK = FactSalesOrderLineAggregate.Country_SK
left join DimCustomerAccountSK 
  on DimCustomerAccountSK.CustomerAccount_SK = FactSalesOrderLineAggregate.CustomerAccount_SK
left join DimItemSK
  on DimItemSK.Item_SK = FactSalesOrderLineAggregate.Item_SK
left join DimOrderType
  on DimOrderType.OrderType_SK = FactSalesOrderLineAggregate.OrderType_SK
left join DimWarehouse
  on DimWarehouse.Warehouse_SK = FactSalesOrderLineAggregate.Warehouse_SK
left join DimHandler
  on DimHandler.Handler_SK = FactSalesOrderLineAggregate.Handler_SK
left join DimCurrency
  on DimCurrency.Currency_SK = FactSalesOrderLineAggregate.TransactionCurrency_SK
left join DimSalesOrderLineStatus
  on DimSalesOrderLineStatus.SalesOrderLineStatus_SK = FactSalesOrderLineAggregate.SalesOrderLineStatus_SK
left join DimPaymentTerms
  on DimPaymentTerms.PaymentTerms_SK = FactSalesOrderLineAggregate.PaymentTerm_SK
left join DimMannerOfTransport
  on DimMannerOfTransport.MannerOfTransport_SK = FactSalesOrderLineAggregate.MannerOfTransport_SK

 

[collierd2]

I'm going to try a 50k rows at a time approach and see if that works better

 

[gmmastros]

I'm wondering if there is a significant difference with your statistics that could be causing the difference? When was the last time you undated statistics or re-created your indexes?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[collierd2]

I updated statistics this morning.
I've also dropped and reapplied indexes.
It was a thought that cross my mind.
Like I say, it just seems to create a different (inefficient!) execution plan.

Right now i'm just rewriting to perform 50k at a time.
I'll look at the pland for that.
I'll also print to screen each time it ticks through 50k.

I don't usually like having to adopt another approach but, in this case, i think i'll have to

Thanks

 

[collierd2]

Whilst I still don't know the source of the problem, it seems as though the 50k rows at a time has worked.

This method builds approx 15x faster so I will apply it to the other databases


Thanks