SQL Server Performance Forum – Threads Archive
Page Lock / Tab lock
I have the following query in my procedure UPDATEdwitem.dbo.ProductSETProductSCDID = m.ProductSCDID,
ASDAdjustmentQuantity = m.ASDAdjustmentQuantity,
BestSalesValue = m.BestSalesValue,
BFEndOfDayStockQuantity = m.BFEndOfDayStockQuantity,
BFStockStatusIndicator = m.BFStockStatusIndicator,
CorrectedSalesQuantity = m.CorrectedSalesQuantity,
CorrectedSalesValue = m.CorrectedSalesValue,
CountIndicator = m.CountIndicator,
CustomerSalesQuantity = m.CustomerSalesQuantity,
CustomerSalesValue = m.CustomerSalesValue,
DeliveredAMQuantity = m.DeliveredAMQuantity,
DeliveredPMQuantity = m.DeliveredPMQuantity,
DeliveredPMValue = m.DeliveredPMValue,
DeliveryAdjustmentQuantity = m.DeliveryAdjustmentQuantity,
DirectDeliveredAMQuantity = m.DirectDeliveredAMQuantity,
DirectDeliveredAMValue = m.DirectDeliveredAMValue,
EndOfDayStockQuantity = m.EndOfDayStockQuantity,
EndOfDayStockValue = m.EndOfDayStockValue,
EstimatedSalesQuantity = m.EstimatedSalesQuantity,
EstimatedSalesValue = m.EstimatedSalesValue,
LastCustomerSaleTime = m.LastCustomerSaleTime,
MaxInLifeStockQuantity = m.MaxInLifeStockQuantity,
OutOfLifeWasteQuantity = m.OutOfLifeWasteQuantity,
OutOfLifeWasteValue = m.OutOfLifeWasteValue,
QualityWasteQuantity = m.QualityWasteQuantity,
QualityWasteValue = m.QualityWasteValue,
RTMQuantity = m.RTMQuantity,
RTMValue = m.RTMValue,
ScaleUpIndicator = m.ScaleUpIndicator,
SelloutIndicator = m.SelloutIndicator,
StockAdjustmentQuantity = m.StockAdjustmentQuantity,
StockScaleToZeroIndicator = m.StockScaleToZeroIndicator,
StockStatusIndicator = m.StockStatusIndicator,
UncataloguedSalesFlag = m.UncataloguedSalesFlag,
WasteSalesQuantity = m.WasteSalesQuantity,
WasteSalesValue = m.WasteSalesValue,
LoadID = m.LoadID
FROMdwitem.dbo.product p
INNERJOIN dbo.tempmirrortable m
ONp.PSDID = m.PSDID
WHEREm.PublishType = 2
ANDm.PartitionNumber = 3
dwitem.product has 20 million records tempmirrortable has 10 million records out of which 8 million records
qualify for the above join condition My server is healthy. Buffer cache hit ratio is > 98%.%cpu is very low ( 1 or 2%)
Have enough hard disk space. 4X2 hybrid cpus.
SP_lock displays around 5000 page locks on product table. This query is running for past 5 hours and not yet completed.
There are no indexes on joining columns. I havent put an index bcos it updates almost 40% of the records. I don’t know what is happening in backend and how to find out?
If I use tablockx option in the join hint, will it improve performance ?
Anything else should I have to look? Thanks for any help
quote:This query is running for past 5 hours and not yet completed.
There are no indexes on joining columns. I havent put an index bcos it updates almost 40% of the records.
Joins with no appropriate indexes will take so much more time to build, especially with the numbers of rows you’re dealing with, and so I doubt it outweighs the performance boost you might be able to gain in the update action itself (if any).
Have you compared response time with indexes and response time without indexes?
Also, does your staging table have any indexes?
There are no indexes on joining columns. I havent put an index bcos it updates almost 40% of the records.
Here are my indexes on dwitem.Product Product_02_IX_01 clustered located on PRIMARYBusinessDateID
Product_02_IX_02 nonclustered located on PRIMARYProductID
Product_02_IX_03 nonclustered located on PRIMARYStoreID
Product_02_IX_04 nonclustered located on PRIMARYPSCDID
Product_02_NK nonclustered, unique, unique key located on PRIMARYBusinessDateID, ProductID, StoreID
ProductStoreDay_02_PKnonclustered, unique, primary key located on PRIMARYPSDID,BusinessDateID No indexes on tempMirrortable I haven’t done any other tests.Indexes are useful if we are going to update less number of records.
Here i have to update almost 40% of my data.
One solution i am thinking is to delete all the rows in dwitem.Product for the matching rows
and do one full insert instead of update. so the query will be changed to delete p from dwitem.Product p
FROM dwitem.dbo.product p
INNER JOIN dbo.tempmirrortable m
ON p.PSDID = m.PSDID
WHERE m.PublishType = 2
AND m.PartitionNumber = 3 insert into dwitem.Product select * from dbo.tempmirrortable
Okay, let me repeat the main issue: "Joins with no appropriate indexes will take much more time to build." This is true on both ends of the join, except if a table has just a handful of rows. You need to add indexes on tempMirror for the columns that you use (1) in the join, and (2) for filtering – so we’re talking about PSDID (1), PublishType (2) and PartitionNumber (2). Delete queries may perform even worse than update queries if there are no appropriate indexes.
For update query composite clustered index on PublishType and PartitionNumber would offer the best performance. However, that index may slow down tempmirrortable population, if the data are not inserted in the order of these two columns.
]]>