-1

I have a table (refer to below structure) where I track all stock ins and outs. Now I want to generate a report from this table to find the opening_stock, purchased, purchased_return, sales, sales_returned, damaged_inventory based on provided date range.

Query:

SELECT ITEM_ID,  

(SELECT (SUM(PURCHASE_QUANTITY) - SUM(SALE_QUANTITY)) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE < '2019-09-31' AND ITEM_ID = l.ITEM_ID) AS OPENING
, (SELECT COALESCE(SUM(PURCHASE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'PURCHASE') AS PURCHASE
, (SELECT COALESCE(SUM(SALE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'PURCHASE RETURN') AS PURCHASE_RETURN
, (SELECT COALESCE(SUM(SALE_QUANTITY),0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'SALE') AS SALE
, (SELECT COALESCE(SUM(PURCHASE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'SALE RETURN') AS SALE_RETURN
, (SELECT COALESCE(SUM(SALE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'DMG') AS DAMAGED_INVENTORY
FROM INV_ITEM_LEDGER l
GROUP BY ITEM_ID

When I run this query on 47,619 records (Total current queries), the system hangs and stops responding.

Table Structure:

CREATE TABLE INV_ITEM_LEDGER (
  ITEM_LEDGER_ID     INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ITEM_ID            INTEGER,
  REF_ID             INTEGER,
  REF_TYPE           VARCHAR(20),
  PURCHASE_QUANTITY  DECIMAL(18,4),
  SALE_QUANTITY      DECIMAL(18,4),
  DESCRIPTION        VARCHAR(512),
  BATCH_NO           VARCHAR(20),
  EXPIRY             VARCHAR(20),
  STOCK_ID           INTEGER,
  BRANCH_CODE        VARCHAR(20),
  LAST_SYNCED        VARCHAR(20),
  ENTRY_DATE         VARCHAR(20),
  LAST_UPDATED       VARCHAR(20),
  ENTRY_DATE2        VARCHAR(20),
  /* Keys */
  PRIMARY KEY (ITEM_LEDGER_ID)
);

CREATE INDEX INV_ITEM_LEDGER_INDEX01
  ON INV_ITEM_LEDGER
  (ITEM_LEDGER_ID, ITEM_ID);

Is there anything to enhance the query?

1

The index on (item_id, ref_type, entry_date) is mandatory in this case. This would avoid creating a temporary table for the group by:

    CREATE INDEX INV_ITEM_LEDGER_INDEX02
    ON INV_ITEM_LEDGER
    (ITEM_ID, REF_TYPE, ENTRY_DATE);
| improve this answer | |
2

As far as I can tell, you can rewrite this to a conditional aggregation for each time range (before 2019-09-31 and after). Typically a "plain" aggregation is faster then multiple scalar sub-queries.

SELECT l.item_id, 
       l.opening, 
       x.purchase, 
       x.purchase_return,
       x.sale,
       x.sale_return,
       x.damaged_inventory
FROM (
  -- aggregate items before 2019-09-31
  SELECT item_id, 
         sum(purchase_quantity) - sum(sale_quantity) as opening, 
  FROM inv_item_ledger
  WHERE entry_date < '2019-09-31'
  GROUP BY ITEM_ID              
) l
  JOIN (
    -- calculate the conditional aggregates for rows after 2019-09-31
    SELECT item_id, 
           sum(CASE WHEN ref_type = 'PURCHASE' THEN purchase_quantity END) as purchase,
           sum(CASE WHEN ref_type = 'PURCHASE RETURN' THEN sale_quantity END) as purchase_return,
           sum(CASE WHEN ref_type = 'SALE' THEN sale_quantity END) as sale,
           sum(CASE WHEN ref_type = 'SALE RETURN' THEN purchase_quantity END) as sale_return,
           sum(CASE WHEN ref_type = 'DMG' THEN sale_quantity END) as damaged_inventory
    FROM inv_item_ledger
    WHERE entry_date >= '2019-09-31'
    GROUP BY ITEM_ID   
  ) x ON x.Item_id = l.item_id

This uses the fact that aggregate functions (like sum()) ignore NULL values. The CASE expression inside the sum() function only returns a value if the ref_type of that row matches, and NULL for all others.

| improve this answer | |
  • Thanks, but it will only sum only for dates < '2019-09-31', while I want opening before '2019-09-31' and others after '2019-09-31'. – Core Nov 26 '19 at 7:01
  • @0_0: ah, I didn't see that. But you can split it up into two queries - which should still be better than 6 – a_horse_with_no_name Nov 26 '19 at 7:07
  • I am using fastreport with delphi and merging them back in the delphi would be another performance issue. (I might need many nested loops). but I will give it a try – Core Nov 26 '19 at 7:13
  • Why would you need to "merge" anything back? That is a single query that should return the same result as your existing query. – a_horse_with_no_name Nov 26 '19 at 7:14
  • never mind, I didn't check your answer and I was thinking to run 2 separate quires. but still my code runs faster than your code :D. – Core Nov 26 '19 at 7:18

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.