Update Performance

pvaughn · September 2021

This is less of a question and more of a Linqpad development suggestion.

When you try to update multiple rows with Linqpad it tries to create an update statement for every row in the query. So for instance the c# (or Linq) version to change the default job code # for an entire table would be:

var jobCodeId = JobCode.FirstOrDefault(x => x.Number.Equals("049329")).IDJobCode;
ItemModel.ForEach(x => x.IDDefaultJobCode = jobCodeId);
SubmitChanges();

And the SQL it generates is:

-- Region Parameters
DECLARE @p0 VarChar(1000) = '049329'
-- EndRegion
SELECT TOP (1) [t0].[IDJobCode], [t0].[Number], [t0].[Description], [t0].[IsClosed]
FROM [JobCode] AS [t0]
WHERE [t0].[Number] = @p0
GO

SELECT [t0].[IDItemModel], [t0].[IDItemSubCategory], [t0].[IDUOMPurchase], [t0].[IDUOMStock], [t0].[IDDefaultLocation], [t0].[IDInventoryCode], [t0].[IDItemComponentType], [t0].[IDDefaultJobCode], [t0].[IDDefaultCostCode], [t0].[IDItemType], [t0].[ItemNumber], [t0].[Description], [t0].[Notes], [t0].[InitialEstimatedCost], [t0].[UOMFactor], [t0].[MinStockLevel], [t0].[NominalStockLevel], [t0].[EstimatedCoreValue], [t0].[SafetyStockLevel], [t0].[IsCriticalPart], [t0].[IsStockable], [t0].[IsActive], [t0].[ReplacementCost], [t0].[IsCapitalSpare], [t0].[IsKit], [t0].[isTaxable] AS [IsTaxable], [t0].[IDOwnership], [t0].[IsPTCPart]
FROM [ItemModel] AS [t0]
GO

SELECT [t0].[IDItemModel], [t0].[IDItemSubCategory], [t0].[IDUOMPurchase], [t0].[IDUOMStock], [t0].[IDDefaultLocation], [t0].[IDInventoryCode], [t0].[IDItemComponentType], [t0].[IDDefaultJobCode], [t0].[IDDefaultCostCode], [t0].[IDItemType], [t0].[ItemNumber], [t0].[Description], [t0].[Notes], [t0].[InitialEstimatedCost], [t0].[UOMFactor], [t0].[MinStockLevel], [t0].[NominalStockLevel], [t0].[EstimatedCoreValue], [t0].[SafetyStockLevel], [t0].[IsCriticalPart], [t0].[IsStockable], [t0].[IsActive], [t0].[ReplacementCost], [t0].[IsCapitalSpare], [t0].[IsKit], [t0].[isTaxable] AS [IsTaxable], [t0].[IDOwnership], [t0].[IsPTCPart]
FROM [ItemModel] AS [t0]
GO

SELECT [t0].[IDItemModel], [t0].[IDItemSubCategory], [t0].[IDUOMPurchase], [t0].[IDUOMStock], [t0].[IDDefaultLocation], [t0].[IDInventoryCode], [t0].[IDItemComponentType], [t0].[IDDefaultJobCode], [t0].[IDDefaultCostCode], [t0].[IDItemType], [t0].[ItemNumber], [t0].[Description], [t0].[Notes], [t0].[InitialEstimatedCost], [t0].[UOMFactor], [t0].[MinStockLevel], [t0].[NominalStockLevel], [t0].[EstimatedCoreValue], [t0].[SafetyStockLevel], [t0].[IsCriticalPart], [t0].[IsStockable], [t0].[IsActive], [t0].[ReplacementCost], [t0].[IsCapitalSpare], [t0].[IsKit], [t0].[isTaxable] AS [IsTaxable], [t0].[IDOwnership], [t0].[IsPTCPart]
FROM [ItemModel] AS [t0]
GO

SELECT [t0].[IDItemModel], [t0].[IDItemSubCategory], [t0].[IDUOMPurchase], [t0].[IDUOMStock], [t0].[IDDefaultLocation], [t0].[IDInventoryCode], [t0].[IDItemComponentType], [t0].[IDDefaultJobCode], [t0].[IDDefaultCostCode], [t0].[IDItemType], [t0].[ItemNumber], [t0].[Description], [t0].[Notes], [t0].[InitialEstimatedCost], [t0].[UOMFactor], [t0].[MinStockLevel], [t0].[NominalStockLevel], [t0].[EstimatedCoreValue], [t0].[SafetyStockLevel], [t0].[IsCriticalPart], [t0].[IsStockable], [t0].[IsActive], [t0].[ReplacementCost], [t0].[IsCapitalSpare], [t0].[IsKit], [t0].[isTaxable] AS [IsTaxable], [t0].[IDOwnership], [t0].[IsPTCPart]
FROM [ItemModel] AS [t0]
GO

SELECT [t0].[IDItemModel], [t0].[IDItemSubCategory], [t0].[IDUOMPurchase], [t0].[IDUOMStock], [t0].[IDDefaultLocation], [t0].[IDInventoryCode], [t0].[IDItemComponentType], [t0].[IDDefaultJobCode], [t0].[IDDefaultCostCode], [t0].[IDItemType], [t0].[ItemNumber], [t0].[Description], [t0].[Notes], [t0].[InitialEstimatedCost], [t0].[UOMFactor], [t0].[MinStockLevel], [t0].[NominalStockLevel], [t0].[EstimatedCoreValue], [t0].[SafetyStockLevel], [t0].[IsCriticalPart], [t0].[IsStockable], [t0].[IsActive], [t0].[ReplacementCost], [t0].[IsCapitalSpare], [t0].[IsKit], [t0].[isTaxable] AS [IsTaxable], [t0].[IDOwnership], [t0].[IsPTCPart]
FROM [ItemModel] AS [t0]
GO

SELECT [t0].[IDItemModel], [t0].[IDItemSubCategory], [t0].[IDUOMPurchase], [t0].[IDUOMStock], [t0].[IDDefaultLocation], [t0].[IDInventoryCode], [t0].[IDItemComponentType], [t0].[IDDefaultJobCode], [t0].[IDDefaultCostCode], [t0].[IDItemType], [t0].[ItemNumber], [t0].[Description], [t0].[Notes], [t0].[InitialEstimatedCost], [t0].[UOMFactor], [t0].[MinStockLevel], [t0].[NominalStockLevel], [t0].[EstimatedCoreValue], [t0].[SafetyStockLevel], [t0].[IsCriticalPart], [t0].[IsStockable], [t0].[IsActive], [t0].[ReplacementCost], [t0].[IsCapitalSpare], [t0].[IsKit], [t0].[isTaxable] AS [IsTaxable], [t0].[IDOwnership], [t0].[IsPTCPart]
FROM [ItemModel] AS [t0]
GO

SELECT [t0].[IDItemModel], [t0].[IDItemSubCategory], [t0].[IDUOMPurchase], [t0].[IDUOMStock], [t0].[IDDefaultLocation], [t0].[IDInventoryCode], [t0].[IDItemComponentType], [t0].[IDDefaultJobCode], [t0].[IDDefaultCostCode], [t0].[IDItemType], [t0].[ItemNumber], [t0].[Description], [t0].[Notes], [t0].[InitialEstimatedCost], [t0].[UOMFactor], [t0].[MinStockLevel], [t0].[NominalStockLevel], [t0].[EstimatedCoreValue], [t0].[SafetyStockLevel], [t0].[IsCriticalPart], [t0].[IsStockable], [t0].[IsActive], [t0].[ReplacementCost], [t0].[IsCapitalSpare], [t0].[IsKit], [t0].[isTaxable] AS [IsTaxable], [t0].[IDOwnership], [t0].[IsPTCPart]
FROM [ItemModel] AS [t0]
GO

SELECT [t0].[IDItemModel], [t0].[IDItemSubCategory], [t0].[IDUOMPurchase], [t0].[IDUOMStock], [t0].[IDDefaultLocation], [t0].[IDInventoryCode], [t0].[IDItemComponentType], [t0].[IDDefaultJobCode], [t0].[IDDefaultCostCode], [t0].[IDItemType], [t0].[ItemNumber], [t0].[Description], [t0].[Notes], [t0].[InitialEstimatedCost], [t0].[UOMFactor], [t0].[MinStockLevel], [t0].[NominalStockLevel], [t0].[EstimatedCoreValue], [t0].[SafetyStockLevel], [t0].[IsCriticalPart], [t0].[IsStockable], [t0].[IsActive], [t0].[ReplacementCost], [t0].[IsCapitalSpare], [t0].[IsKit], [t0].[isTaxable] AS [IsTaxable], [t0].[IDOwnership], [t0].[IsPTCPart]
FROM [ItemModel] AS [t0]
GO

There's more... a LOT more. It selects EVERY SINGLE ROW and then AFTER it selects every row updates them one at a time. It takes a query that should take 0.0123 sec and turns it into a 5 minute operation. This is UGLY and should be seriously looked at by the developers. The ACTUAL SQL that it SHOULD generate is:

-- Region Parameters
DECLARE @p0 VarChar(1000) = '049329'
-- EndRegion
SELECT TOP (1) [t0].[IDJobCode], [t0].[Number], [t0].[Description], [t0].[IsClosed]
FROM [JobCode] AS [t0]
WHERE [t0].[Number] = @p0
GO

DECLARE
    @p0 uniqueidentifier = 'the guid that matches the above query'
UPDATE
    [ItemModel]
SET
    [IDDefaultJobCode] = @p0

I mean... correct me if I'm way off base here but it seems like there's a HUGE bug in updating using Linq. And this is a table with only 4K rows. What happens when it has 200K??? I really think this should be looked at and fixed.

sgmoore · September 2021

In cases like this, I use Terry Aney's extensions see https://terryaney.wordpress.com/2008/04/14/batch-updates-and-deletes-with-linq-to-sql/

Update Performance

Comments