This happened a quite ago but only now I have had time to write about it.

Once upon a time, I was asked to look into a customer database (in a big engagement of helping them improving performance overall)

One thing stand out is this query

WITH CTE AS
(SELECT * FROM dbo.OrderGroupNote 
WHERE OrderGroupId = @OrderGroupId)
MERGE CTE AS T
USING @OrderGroupNotes AS S
ON T.OrderNoteId = S.OrderNoteId
WHEN NOT MATCHED BY TARGET
	THEN INSERT (
		[OrderGroupId],
		[CustomerId],
		[Title],
		[Type],
		[Detail],
		[Created],
		[LineItemId],
		[Channel],
		[EventType])
	VALUES(S.OrderGroupId,
		S.CustomerId,
		S.Title,
		S.Type,
		S.Detail,
		S.Created,
		S.LineItemId,
		S.Channel,
		S.EventType)
WHEN NOT MATCHED BY SOURCE
	THEN DELETE
WHEN MATCHED AND (S.IsModified = 1) THEN 
UPDATE SET
	[OrderGroupId] = S.OrderGroupId,
	[CustomerId] = S.CustomerId,
	[Title] = S.Title,
	[Type] = S.Type,
	[Detail] = S.Detail,
	[Created] = S.Created,
	[LineItemId] = S.LineItemId,
	[Channel] = S.Channel,
	[EventType] = S.EventType;

If you can guess, that is the query to save the notes of an order. Normally it’s … fine. But for this customer, it is not, each save could result in almost 10GB, yes, you read it right, ten gigabytes of logical reads. Insane

The reason was, this customer has some orders with an absurd number of notes attached to it. The most one has 52k notes. And there are, in total, 94 orders with more than 1000 notes.

Upon investigation, they have a job to validate payment of invalid orders, which runs every 10 minutes. If the validation failed, a note will be added to the order. But because of no “limit” or “cut off”, that’s kept going for forever and continuing to add notes to orders. Each time, the operation becomes more expensive.

As a side note, this is note only expensive on the saving (to the database). It’s expensive to load from database, and it’s expensive to create all the objects in the memory.

The fix in this case is obviously to trim old notes, and to make sure that if the validation failed for X times, stop processing further.

But you might ask, could we do better. could we not save the entire order notes collection just because one note is added? That’s a good question. A really good one. I took a shot at that, but it’s … complicated. This is where we are held back by our promises of keeping thing backward compatible. When we try to make it better – you can do it better yourself as well. Make sure you do not have orders with an unusually high amount of notes.

SELECT 
    ordergroupid, 
    COUNT(OrderNoteId) AS notecount
FROM 
    OrderGroupNote
GROUP BY 
    ordergroupid
ORDER BY 
    notecount DESC;

If the most you have is less than 10, all good, less than 20 is fine. More than that and you might want to check why those orders have that many notes.