-1

I'll provide a toy example here:

I have 2 tables, a Thing table and an AdditionGroup table. Things may be members of an AdditionGroup, or they may not. Both Thing and AdditionGroup have a Quantity column, and I want the sum of those for each Thing, where applicable, demonstrated in the view vwThingAugmented. I have the Quantity indexed on both tables because I obviously wish to sort and filter on this column, but of course the index doesn't help if I want to sort and filter on vwThingAugmented.

CREATE TABLE AdditionGroup (
    ID INT IDENTITY (1, 1) NOT NULL
    , Quantity INT NOT NULL
    , CONSTRAINT PK_Addition_ID PRIMARY KEY CLUSTERED (ID)
    , INDEX IX_Addition_Quantity NONCLUSTERED (Quantity, ID) --this does not help
);
GO

CREATE TABLE Thing (
    ID INT IDENTITY (1, 1) NOT NULL
    , Quantity INT NOT NULL
    , AdditionGroupID INT NULL
    , CONSTRAINT PK_Thing_ID PRIMARY KEY CLUSTERED (ID)
    , CONSTRAINT FK_Thing_AdditionGroupID FOREIGN KEY (AdditionGroupID) REFERENCES AdditionGroup(ID)
    , INDEX IX_Thing_Quantity NONCLUSTERED (Quantity, ID) --neither does this help
);
GO

CREATE VIEW vwThingAugmented
AS SELECT t.ID
, Quantity = t.Quantity + ISNULL(a.Quantity, 0) 
, AdditionGroupID
FROM Thing t
LEFT JOIN AdditionGroup a ON t.AdditionGroupID = a.ID

This same pattern occurs for various cases - calculated Status IDs sums, etc.

Here are the various approaches I've tried that have failed:

  1. Naive approach, shown above: this does not scale. Even six-figures of rows can produce pathological performance if the calculation isn't lightning-fast.

  2. Indexed views. No left joins allowed. In other projects it's been the no-self-joins rule, or the NOEXPAND requirement, but it's always something.

  3. Persisted Computed Columns. No queries to other tables allowed.

  4. Abusing SQLCLR. Also not allowed in indexed views or persisted calculated columns.

  5. Triggers and a normal column for the calculated value. They work but exceptionally difficult because you have to avoid infinite recursion, and there's heavy code-duplication because the trigger needs to be on 2 different tables and several events, and I can't figure out how to factor out the repeated code (if this is a good strategy, examples would be appreciated).

  6. Same as (5), but manually using procs. Similar problem exists because what's the performant way to parametrize the list of rows that need to be refreshed, when that list may include a massive number of rows?

  7. Calculating and setting the value in the business layer. I want a refund on my SQL server license fees, then. What am I paying for if basic performant "once and only once" is impossible? That there's no clean way to have a derived value an implement an observer pattern?

This is insanely basic functionality but it's been the bugaboo of my career across several projects and I've never seen it solved. Am I just dumb?

5
  • I gave it some though and almost wrote an answer but the question is what kind of queries do you intend to support? i mean. Is it quantity of the view equals? <=, >= between? anything? the base issue is the + isnull as that causes it to check a lot more as it has to check for all the combinations. Jan 14, 2021 at 22:02
  • If you could provide a little more context to an actual problem you're trying to solve, as your question is a bit various and hard to follow. For example you say "Things may be members of an AdditionGroup, or they may not." But your table definition of Thing has a foreign key constraint CONSTRAINT FK_Thing_AdditionGroupID FOREIGN KEY (AdditionGroupID) REFERENCES AdditionGroup(ID) and the AdditionGroupID you've made not nullable (INT NOT NULL). So your Thing table definition conflicts with your problem description (unless Thing.AdditionGroupID has orphaned AdditionGroup.IDs.)
    – J.D.
    Jan 14, 2021 at 22:40
  • 1
    My mistake, it should be nullable - thanks for the correction. I've changed it in the example. And this is a toy-example of a real problem I'm encountering repeatedly, where the data is displayed into a grid and users wish to be able to ORDER BY or filter a computed column in a view and performance tanks. For example, cases where the computed column is a DateTimeOffset of projected events and users wish to see the events for tomorrow, in date order.
    – Pxtl
    Jan 14, 2021 at 22:51
  • @Pxtl Noted, thanks for updating your example. Short answer is you're not stupid, but it's not SQL Server's fault either. It's a "don't hate the player, hate the game situation", because albeit the problem you describe is logically simple, it's not necessarily relationally simple (from a performance perspective) and one you would run into on pretty much any standard RDBMS out there. But it's certainly a solvable problem using the tools available to you in SQL Server.
    – J.D.
    Jan 14, 2021 at 23:04
  • I'm going to come up with a concrete solution to post, but other things that would be helpful to know is your time requirements (e.g. sub-second, a few seconds, under 10 seconds), and a concrete example query you might run off the above schema you provided. E.g. SELECT * and SELECT t.ID can yield completely different execution plans that affect performance. So the devil is in the details of the example queries you'd want to make performant.
    – J.D.
    Jan 14, 2021 at 23:05

2 Answers 2

2

I have the Quantity indexed on both tables because I obviously wish to sort and filter on this column, but of course the index doesn't help if I want to sort and filter on vwThingAugmented.

Based on your view and the comment on filtering I am going to assume queries like this:

SELECT * 
FROM  dbo.vwThingAugmented
WHERE Quantity = 5;

Where there is a filter operator late in the execution plan:

enter image description here

One of the things you could try, depending on the size of the tables and the type of filters you do would be to create a view with a UNION an INNER JOIN and a NOT EXISTS instead of a LEFT JOIN

CREATE VIEW dbo.vwThingAugmented_2
AS SELECT t.ID
, Quantity = t.Quantity + a.Quantity
, AdditionGroupID
FROM dbo.Thing t
INNER JOIN dbo.AdditionGroup a ON  t.AdditionGroupID = a.ID
UNION ALL
SELECT t.ID
, Quantity = t.Quantity
, AdditionGroupID
FROM dbo.Thing t
WHERE NOT EXISTS (SELECT * FROM dbo.AdditionGroup a WHERE  t.AdditionGroupID = a.ID);

You do get two accesses to dbo.Thing and dbo.AdditionGroup in this case, YMMV depending on the type of query you run against the view. You could try running the same query again:

SELECT * 
FROM dbo.vwThingAugmented_2
WHERE Quantity = 5;

This removes the filter operator but you do have double index access with a concatenate. enter image description here

And you still have residual predicates on both parts of the query,

Because the nonclustered index is not covering:

DROP INDEX IX_Thing_Quantity ON dbo.Thing;
CREATE INDEX IX_Thing_Quantity ON dbo.Thing (Quantity, ID) 
INCLUDE (AdditionGroupID);

Where now the bottom part is improved to use the nonclustered index (without a key lookup) instead of the clustered index :

enter image description here

Lastly, as mentioned by you, you could add an indexed view to the first part of the view. Remember to use NOEXPAND for best performance, even in Enterprise edition.

5
  • This approach has the advantage that the computation can be then moved into an indexed view... but at that point it's tremendous duplication of code, and indexed views can't reference each other, so nesting these becomes impossible as more computer columns appear. It's workable, but I'm worried it's impractical in practice. Is this an approach people actually use?
    – Pxtl
    Jan 14, 2021 at 21:45
  • 2
    @Pxtl I personally use UNION a lot to split certain parts to make it as easy as possible for the optimizer. A more common example is to reduce or remove OR filters. I would try to stay away from nesting views since that increases complexity for your queries. Duplication or an increase of T-SQL can be fine in my book, defenitely if it helps performance. But again as mentioned, if it improves performance will depend on the queries and your data. Jan 14, 2021 at 22:08
  • 2
    with the where exists condition you can replace union for union all and drop that merge join for simple concat. Jan 14, 2021 at 22:08
  • @VladislavZalesak you are right, I missed that, thank you! Jan 14, 2021 at 22:13
  • @RandiVertongen but based on the queries, this may not solve the problem as it still doesnt remain sargable due to the +. Depending on the queries, i think a stored procedure may offer possibilities here (ie. for = you could limit the quantity in both tables and lower ammount of rows read) Jan 14, 2021 at 22:50
0

So as I mentioned in the comments, we really need more concrete information to be able to best help you. I understand theoretically what you're trying to achieve, but there's no context on numbers in terms of expected execution time and size of the tables you're dealing with. Also knowing the hardware your server is running on isn't a bad thing to include either.

I created your schema near exactly above. I left out the Foreign Key reference on AdditionGroupID, because it won't let me create orphaned references in the Thing table with it, and is almost completely unrelated to performance issues anyway (if anything having the Foreign Key can only help execution plan generation in edge cases).

Using your schema I inserted 1,000,000 records into both tables. I did a semi-even distribution of picking non-existent vs existent unique AdditionGroupID values during the insert into the Thing table (meaning every other row references an AdditionGroupID that doesn't exist or is null) for diversity. For the ORDER BY query test I SELECT the entire View, and to minimize external variables unrelated to SQL Server's performance I select the results into a TempTable instead of back to the client (SSMS). For the filter test, I SELECT everything back to the client since the amount of data is small (and the overhead of the external variables is negligible).

These first set of tests were ran on my modest laptop:

For the following query:

SELECT *
INTO #TEMP
FROM vwThingAugmented
ORDER BY Quantity

The total runtime was about 1/3rd of a second. The CPU time was slightly over 1.5 seconds. (CPU time is the sum of time across all cores of your processor spent working on the query, which is why it can be a higher value than the total runtime.):

Query Time Statistics

Execution Plan for reference (nothing special going on in here).

I saw similar total runtime and CPU time for the following filter query test:

SELECT *
FROM vwThingAugmented
WHERE Quantity = 18

Query 2 Time Statistics

Execution Plan for reference

To cross-check myself on another server, I created a SQLFiddle. (Disregard the jankiness of how I'm generating the schema, this was due to timeout limitations during schema generation on SQLFiddle to try to maximize the amount of records I insert into my test tables.) The most records I was able to insert into the tables on SQLFiddle before I hit their limitation was about 250,000.

On their limited server, the first test query (ORDER BY test) ran in about 1 second:

SQLFiddle Test Query 1 Results

The second test query (filter test) ran in about 0.25 seconds:

SQLFiddle Test Query 2 Results

The moral of the story is your example View above isn't doing anything too crazy for SQL Server to handle (even with a sub-optimal execution plan) for things under 1 million records. If you're seeing more than a couple seconds in this scenario on your server instance, then you either have a misconfigured server, a way under-provisioned server (less than a dated laptop), or you're not comparing apples-to-apples and your actual problem with your real use cases are other things (unrelated to the above example) that are solvable if you do standard query tuning analysis (leveraging the execution plan, query statistics, etc).

If you post a new question with a real schema and query you're running into issues with, and its execution plan, then we can help you understand the root cause of the issue and the best way to correct it.

4
  • The problem is that the actual code is too much to include here - like a half-dozen tables involved in a very convoluted view. But we've narrowed performance to doing anything with one complex calculated column that is fed by complicated arithmetic. So basically, I'm looking for "what's the best way to make sorting and filtering by a computed data-point SARGable when persistent calculated columns and indexed views are not allowed by the SQL server?" I don't know why the toy example doesn't hit the same performance... sorry for posting it on the assumption it would be the same.
    – Pxtl
    Jan 15, 2021 at 6:04
  • @Pxtl Unfortunately the corrrct answer to "how do I always make my queries sargable?" is going to vary from query to query. Using ISNULL() in a predicate is not sargable but as shown by my tests above, that's not the performance issue anyway. So sargability is the wrong problem to solve in this case. Again if you have an actual query similar to your example above and it's running a lot slower than you have some other issue your efforts are best focussed on exploring (and the execution plan probably indicates what that issue is). Otherwise if your use cases are a lot more...
    – J.D.
    Jan 15, 2021 at 14:11
  • ...complicated than your example, then unfortunately there's not much advice we can give without seeing them (or an example that accurately represents them). If your issue is truly non-sargability then it depends on your exact query, and likely involves understanding the logic and end goal of that query to look for ways to re-write it. In other cases persisting the data like an indexed view or computed column are sufficient enough ways to solve the problem. But without your schema, query, and execution plan we're kind of flying blind here. It only takes seconds to copy and...
    – J.D.
    Jan 15, 2021 at 14:15
  • ...paste the actual View definition and problematic query (despite having six tables). And if you're worried about sharing the actual object names in your schema, there's obfuscation tools you can run it through first.
    – J.D.
    Jan 15, 2021 at 14:16

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