I’m working on a warehouse where I’m drawing every box location with some simple parameters (is a box present, is it available, …).
For this, I have SQL-bound properties on the box template.
SELECT BoxId
FROM Boxes
WHERE Location = '{tmpBox.Location}'
There are currently some 440 boxes. As I know where the machinery is getting or putting a box, I already optimised the refresh so that only those locations are queried again. It’s only when loading that it queries all locations.
However, when I check the SQL activity monitor, I see it has 430-ish plans for those queries, which means they aren’t parameterised and every query is compiled and planned again. Causing slowdown and extra memory usage Especially if the warehouse will be enlarged and contains thousands of boxes.
Is there a way to optimise this and let the server only calculate 1 plan?
I’m not sure if it will keep it from being planned again but can you move the query to a stored procedure and then just call the stored procedure passing it the tmpBox.location?
Thanks for the suggestion. Stored procedures would indeed probably make the plan caching better. Though we never use stored procedures for simple queries, so it would obfuscate the code IMO.
Trying to declare variables in the query didn’t work out. It still seemed to recalculate the plans (I guess it calculates the plans for a complete query, including the variable declaration).
But I found the option to “force parametrization” (https://msdn.microsoft.com/en-us/library/ms175037.aspx). This seems to help. Profiling the queries from a fresh DB schema makes the query time half as long for those queries. So the plan compilation is certainly taken out. And it must also mean less stress on the memory usage of the db. I just hope it doesn’t have other effects that could harm performance.
