Performance
查詢速度提升(期初庫存、執行庫存和期末庫存)
我有一張表格(請參閱下面的結構),我可以在其中跟踪所有庫存進出。現在我想從這個表中生成一個報告,根據提供的日期範圍查找opening_stock、purchased、purchased_return、sales、sales_returned、damaged_inventory 。
詢問:
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
當我對47,619條記錄(目前查詢總數)執行此查詢時,系統掛起並停止響應。
表結構:
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);
有什麼可以增強查詢的嗎?
在這種情況下,(item_id, ref_type, entry_date) 上的索引是強制性的。這將避免通過以下方式為組創建臨時表:
CREATE INDEX INV_ITEM_LEDGER_INDEX02 ON INV_ITEM_LEDGER (ITEM_ID, REF_TYPE, ENTRY_DATE);
據我所知,您可以將其重寫為每個時間範圍(2019-09-31 之前及之後)的條件聚合。通常,“普通”聚合比多個標量子查詢更快。
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
這使用了聚合函式(如
sum()
)忽略 NULL 值的事實。ref_type
sum() 函式中的 CASE 表達式僅在該行的 匹配時返回一個值,而對於所有其他行則返回 NULL。