I have 2 tables StockIn and StockOut and calculated stock balance (in-out).

Both tables contain millions of records.

When I calculate the stock balance, due to the millions of records, my query crashes.

Can anybody help me to speed up or fix this query?

SELECT Main.Item, SUM(Main.Qty) AS Qty
FROM     (SELECT item, Qty
                  FROM      StockIn
                   UNION ALL
                  SELECT item, -Qty
                  FROM     Stockout) AS Main 
GROUP BY Main.Item 

enter image description here

  • Have you tried creating columnstore index? Also provide details of your specification of SQL Server, memory and CPU/ Sep 19, 2023 at 9:48

1 Answer 1


Get the subtotals of all stock in and stock out separately, then sum them to get the balance.

SELECT Main.Item, SUM(Main.Qty) AS Qty FROM 
    (SELECT item, SUM(Qty) as Qty FROM StockIn GROUP BY item
     SELECT item, -SUM(Qty) as Qty FROM Stockout GROUP BY item) 
    AS Main 
GROUP BY Main.Item

Also make sure that both tables are indexed on the item column

  • Dear Andrea prior when the records in thousand query well work but now records in millions so query crash, can u tell how can faster query in million or billion records
    – Umar
    Sep 19, 2023 at 5:40

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.