3

I want to know how to efficiently store query-affecting settings in a large analytical database (MS SQL Server). By efficiency I mean no hardcode (1) and fast/optimal query processing (2).

My example

I've got a massive table of sales orders. Let's simplify it to two columns:

SalesOrder
 OrderID      int      Primary Key
 OrderDate    date

I need to retrieve the list of orders for the last X days and this X should be configurable. That's why I introduce a table of settings where I define it:

Admin_Configuration
 SettingName   (PK)|      SettingValue
 ------------------|------------------
 DaysBack          |          30   

It allows me not to hardcode this "30" in query definitions. Then I would run queries like:

SELECT OrderId
  FROM SalesOrder as S
  JOIN Admin_Configuration as A
    ON A.SettingName = 'DaysBack'
  WHERE [OrderDate] > DATEADD(DAY, -A.SettingValue, GETDATE());

Performance

It avoids hardcoding, but leads to a performance problem. This query runs much slower than it would've, had I simply hard-coded 30 in the WHERE clause.

My understanding is that optimizer doesn't know how large my SettingValue is and assumes the worst - that all rows would be retrieved from the SalesOrders table, thus leading to a suboptimal query plan.

Trying a scalar function yielded even worse results. I'm desperate enough to have a separate view which stores these hardcoded settings but I've been refraining from it with hopes for a better solution.

Here is the original query plan (with a join): 4 seconds elapsed Original Plan

Here is the plan with subquery, as gbn suggested:

SELECT orderid
FROM [SalesOrder] as s
WHERE s.OrderDate > dateadd(DAY, 
  (SELECT -NumValue
   FROM Admin_Configuration where SettingName = 'DaysBack'
  )  
  , getdate())

7 seconds elapsed, slightly slower Plan2

As for the ideal performance: I just hardcoded 20, the query took 2 seconds. It needs mentioning that the Admin_Configuration table contains only 18 rows, hence it shouldn't affect performance.

SELECT OrderId
  FROM SalesOrder as S
  JOIN Admin_Configuration as A
    ON A.SettingName = 'DaysBack'
  WHERE [OrderDate] > DATEADD(DAY, -20, GETDATE());

Using parameters would mean that I'd have to replace views (that I use now) with stored procedures/table-valued functions, which is unacceptable in my case

3

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:

SELECT orderid
FROM [SalesOrder] as s
WHERE s.OrderDate > dateadd(DAY, 
  (SELECT -1 * MAX(NumValue)
   FROM Admin_Configuration where SettingName = 'DaysBack'
  )  
  , getdate());

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
UNION ALL
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:

enter image description here

However, if I create a view for each setting:

CREATE OR ALTER VIEW DAYSBACK_SETTING_VIEW AS
SELECT 85 SettingValue;

And run this query:

SELECT OrderId
FROM SalesOrder as S
WHERE S.[OrderDate] > DATEADD(DAY
, (
    SELECT -1 * MAX(SettingValue)  
    FROM DAYSBACK_SETTING_VIEW
)
, GETDATE());

I get a better estimate that's directly based on the value in the view:

enter image description here

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
SELECT
    [DaysBack] = CONVERT(integer, 85),
    [Setting2] = CONVERT(integer, 0),
    [Setting3] = CONVERT(char(6), 'Banana')
    ...

The query becomes:

SELECT
    SO.OrderId
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
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE @SettingName
        WHEN 'DaysBack' THEN 85
        WHEN 'Setting2' THEN 0
        ELSE NULL
    END;
END;

If I use that in the query there's no longer a join in the plan:

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.