Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Issue with sort/distinct when updating

Status
Not open for further replies.
collierd2

collierd2

MIS
Joined
Feb 26, 2007
Messages
61
Location
DE
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 one:one updates

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

Thanks

Damian.
 
Sort by date Sort by votes
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?
 
Upvote 0 Downvote
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 one:one update
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Code: 
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
 
Upvote 0 Downvote
I'm going to try a 50k rows at a time approach and see if that works better
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gussy81
  • Locked
  • Question Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question Question
Update with a month name
Replies
9
Views
1K
gmmastros
gmmastros
Jago11
  • Locked
  • Question Question
When to use 1=1 logic
Replies
7
Views
665
Andrzejek
Andrzejek
TheBugSlayer
  • Locked
  • Question Question
Transaction with SELECT and UPDATE with XLOCK causing deadlocks
Replies
10
Views
1K
gmmastros
gmmastros
Billz66
  • Locked
  • Question Question
need to udate table entry with number containing a *
Replies
11
Views
846
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top