It needs mentioning that the Admin_Configuration table contains only
18 rows, hence it shouldn't affect performance.
Why shouldn't an 18 row table affect performance? Take a look at the estimated query plan for the original query that takes 4 seconds. The query optimizer is saying that the loop join has roughly the same cost as the scan. The scan is only 59% of the total cost. The query with just the scan finishes in two seconds. It just so happens that the costs line up well here, but SQL Server is telling you that there is some overhead to sending all of those rows through the join. It would be more typical to write the query like this:
FROM [SalesOrder] as s
WHERE s.OrderDate > dateadd(DAY,
(SELECT -1 * MAX(NumValue)
FROM Admin_Configuration where SettingName = 'DaysBack'
However, that won't help you. You'll still have the overhead of the join. You're also unlikely to get good cardinality estimates. I assume that some of your views have more complex logic than just joining to a single table. It could be important to get cardinality estimates based on the actual setting value instead of a magic number guess or an average of all of the values in the table. By that I mean that if you change your setting value from 30 to 3000 you probably want some of the query plans to change. With your current strategy that may not happen, unless you have a filtered statistics on the table for each row.
Use a view or table-valued function
If you would need to create an object per row to get good estimates, you might as well put your data into views or table-valued functions. They seem to have mostly the same performance characteristics for this problem so I'll just cover views. Let's say that you create a view that holds your settings instead of your table:
CREATE OR ALTER VIEW SETTINGS_VIEW AS
SELECT 'DaysBack' SettingName , 85 SettingValue
SELECT 'Setting2', 0;
SQL Server won't create statistics on the view. Instead it'll look directly at the values. However, your cardinality estimates can still be off. For the data that I mocked up:
However, if I create a view for each setting:
CREATE OR ALTER VIEW DAYSBACK_SETTING_VIEW AS
SELECT 85 SettingValue;
And run this query:
FROM SalesOrder as S
WHERE S.[OrderDate] > DATEADD(DAY
SELECT -1 * MAX(SettingValue)
I get a better estimate that's directly based on the value in the view:
The overhead of the join, which you think will be a problem, is still present.
Use a one-row view
Or, write a one row view with one column per setting as described in SQL Server: Variables, Parameters or Literals? Or… Constants? by Jared Ko:
CREATE OR ALTER VIEW dbo.Constants AS
[DaysBack] = CONVERT(integer, 85),
[Setting2] = CONVERT(integer, 0),
[Setting3] = CONVERT(char(6), 'Banana')
The query becomes:
FROM dbo.SalesOrder AS SO
JOIN dbo.Constants AS C
ON SO.[OrderDate] > DATEADD(DAY, -C.DaysBack, GETDATE());
This gives a good estimate using the value in the view, without a join.
Use a scalar function
I almost never recommend this as an option, but you could consider using a scalar UDF in the query. Scalar UDFs are bad because they will force any query using the view to be fully run in serial, they can be executed per row instead of per query which can lead to poor performance, and the cardinality estimates from filtering against them are often based on magic number guesses and don't change with the input variables. However, your current query already runs serially and your current approach doesn't give good cardinality estimates. Consider the following function:
CREATE OR ALTER FUNCTION SETTINGS_UDF (@SettingName VARCHAR(100)) RETURNS INTEGER
RETURN CASE @SettingName
WHEN 'DaysBack' THEN 85
WHEN 'Setting2' THEN 0
If I use that in the query there's no longer a join in the plan:
The brief testing that I did suggests that a simple function like this can be executed once per query instead of once per row, if you're careful. The numbers vary a bit, but here's what I see when I execute the three queries:
║ Test ║ CPU Time (ms) ║ Elapsed Time (ms) ║
║ Hardcoded ║ 422 ║ 502 ║
║ UDF ║ 422 ║ 476 ║
║ View ║ 906 ║ 990 ║
In my experience, if your development framework is causing scalar UDFs to appear to be a good option then you probably need to change your framework if possible. I would start by validating that the two second performance degradation that you observed will actually be a problem. If it is, consider other solutions that don't require you to hardcode values into a set of views. That isn't really a typical thing to do.