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)

FROM (select *, sum(Quantity) over (order by id) as RunningQty
      from #PurchaseOrderProducts
     ) P
WHERE P.RunningQty - Quantity < 12;
  • \$\begingroup\$ Have you checked the query plan? \$\endgroup\$
    – dfhwze
    Commented Aug 19, 2019 at 6:35
  • \$\begingroup\$ 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. \$\endgroup\$
    – Diane M
    Commented Aug 19, 2019 at 11:48

1 Answer 1


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.

Query plan with code as written

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. rowstore batch mode
  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. clustered index scan nonclustered index scankey lookup :(

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,

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

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

                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 )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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