Just been doing some query optimisation and trying to work out what part of a Stored Procedure is slowing the query down.
I found this line: AND CategoryId = ISNULL(@CategoryId, CategoryId)
This is the lazy way to conditionally include clauses!
This is much better: (@CategoryId IS NULL OR CategoryId = @CategoryId)
Changing that line increased the performance by 0.5 second. Now it runs in < 150ms.