# SQL Sub-query with running total optimization

The codes below get the rows of data until it reaches the total running quantity limit.

Notice that the SQL uses sub-query. select *, sum(Quantity) over (order by id) as RunningQty from #PurchaseOrderProducts. I'm worried that the sub-query would get all the data from the table. Is there a way to improve the performance?

CREATE TABLE #PurchaseOrderProducts
(
Id int,
Product varchar(10),
Quantity int
)

INSERT INTO #PurchaseOrderProducts VALUES (1, 'Item A', 5)
INSERT INTO #PurchaseOrderProducts VALUES (2, 'Item B', 1)
INSERT INTO #PurchaseOrderProducts VALUES (3, 'Item C', 8)
INSERT INTO #PurchaseOrderProducts VALUES (4, 'Item D', 2)
INSERT INTO #PurchaseOrderProducts VALUES (5, 'Item E', 1)

SELECT P.*
FROM (select *, sum(Quantity) over (order by id) as RunningQty
from #PurchaseOrderProducts
) P
WHERE P.RunningQty - Quantity < 12;

• Have you checked the query plan? – dfhwze Aug 19 '19 at 6:35
• You could add "LIMIT 12" to your subquery if your items respect quantity >= 1. This will bound your outer query performance. Note that this is a quite simple table manipulation even with a subquery it will be most likely optimised to be O(nlogn) so if a query as simple as this causes performance issues, then you have a design problem elsewhere. – Arthur Hv Aug 19 '19 at 11:48

Assuming that #PurchaseOrderProducts is a realistic depiction of your real table but with less data (and that your real table is also a heap), then I get this plan.

This is a pretty reasonable plan, where I wouldn't necessarily expect it to be that bad.

There are a few things we could do to make this better, including:

1. Batch-mode for rowstore is enabled (via hacks) in SQL Server 2016 if you include a CCI in your query, even if it isn't used. This can likely make the window function better. This gets me a batch-mode operator for everything but the table scan, and cleans up a lot of the aggregation.
2. Creating an appropriate index that your window function can use will help as well by removing the sort (the index maintains a sorted order). A clustered index on the Id column moves all of the cost onto the index scan. A non-clustered index on Id that INCLUDEs Quantity and Product (order didn't matter in my testing) does the same. You'll have to INCLUDE any columns you want in the output, however - otherwise it will just table scan, or if you force it to use the index it'll key lookup. That won't be great for performance.

If we look at all of these side-by-side, we notice that the non-clustered without an INCLUDE is the worst by far, and the rest all look about the same.

You may notice that the batch-mode query is also using the non-clustered index; getting all of the plans side-by-side without indices is a pain. If you use the batch-mode hack without any indices then it will still have to sort the data, but it can do that in batch mode which will provide a nice performance bump. This is also good if you rely on the table being a heap elsewhere (e.g. parallel inserts, minimal logging, etc).

You may also notice that the plans using the clustered index vs non-clustered index have no apparent difference (besides the index they use). This is because all of the columns in the table are selected in both queries, and all indices include all columns. You can get substantial performance and memory savings if you only need a subset of columns and have a good non-clustered index; this might be helpful for your real data if the temp table isn't representative.

Query samples:

Batch mode w/ rowstore table

DROP TABLE IF EXISTS #FakeCciForRowstoreBatchMode;
CREATE TABLE #FakeCciForRowstoreBatchMode
(
__zzDontUseMe bit NULL,
INDEX CCI CLUSTERED COLUMNSTORE
);

DROP TABLE IF EXISTS #PurchaseOrderProducts;
CREATE TABLE #PurchaseOrderProducts
(
Id       int,
Product  varchar(10),
Quantity int
);

INSERT INTO #PurchaseOrderProducts
VALUES
( 1, 'Item A', 5 ),
( 2, 'Item B', 1 ),
( 3, 'Item C', 8 ),
( 4, 'Item D', 2 ),
( 5, 'Item E', 1 );

SELECT P.Id,
P.Product,
P.RunningQty,
P.Quantity
FROM ( SELECT Id,
Product,
Quantity,
SUM( Quantity ) OVER ( ORDER BY Id ) RunningQty
FROM #PurchaseOrderProducts
LEFT OUTER JOIN #FakeCciForRowstoreBatchMode
ON 1 = 0 ) P
WHERE P.RunningQty - P.Quantity < 12;


Indices I created for #2 (plans generated using INDEX hints)

CREATE CLUSTERED INDEX whatever ON #PurchaseOrderProducts( Id ASC )
CREATE NONCLUSTERED INDEX whatever2 ON #PurchaseOrderProducts( Id ASC ) INCLUDE( Quantity, Product )
CREATE NONCLUSTERED INDEX whatever3 ON #PurchaseOrderProducts( Id ASC ) INCLUDE( Product, Quantity )
CREATE NONCLUSTERED INDEX whatever4 ON #PurchaseOrderProducts( Id ASC )