I have a view that needs to look up a configuration value and return it as one of the columns in every row of the view results. This value is essentially a constant for the duration of the SELECT (and in fact, is generally constant for months on end, and is definitely different for every customer). Here's a contrived example:
CREATE VIEW Baz AS SELECT foo, (SELECT [Value] FROM [Config] WHERE [Key] = N'MyKey') AS ConstantValue, bar FROM myTable
The problem with this, is that the SQL plan shows that the value is being looked up for each and every row in the table. This is a waste, and is definitely the "hot-spot" in the plan. I'd like it to look up the value just ONCE, and then use it in every row.
Now, in this contrived example, the plan doesn't show it, but in every plan I make, no matter how I try to get that constant value (using a WITH CTE, CROSS APPLY the value into the results of the SELECT, etc.) the plan always shows that the Index Seek for the value happens once per row, not once TOTAL.
I can't hard code the value, because it's a configuration value that can and does change over time, and is definitely uniquely different for every customer database.
I'm showing as much as 40% of the query time (in both estimated and actual plans) being spent in just this one lookup. Doing it ONCE would be a significant optimization. Any ideas?
See the part of the plan below:
Move the expression to the
from clause. It will only be evaluated once:
CREATE VIEW Baz AS SELECT t.foo, k.ConstantValue, t.bar FROM myTable t CROSS JOIN (SELECT [Value] as ConstantValue FROM [Config] WHERE [Key] = N'MyKey') k;