Slow Query - Timestamp

Hi Martin,

We are having a great slowdown when making the time recording through the terminal.
Monitoring the database I found a query that reads all batches in the system and adds up the amount of each item per deposit, as we have many records in the batch table we are suffering.
This query is run multiple times a day.
I rewrote a query to gain performance for all clients without changing the result, so everyone wins.

The comparative and query follows.

------------------------------------------------------------------------------------------------------
---slow query

SELECT
(
SELECT COALESCE(SUM("IGE1"."Quantity"), 0)
FROM "IGE1"
WHERE "U_belnrid" = "BEAS_FTSTL"."BELNR_ID"
AND "U_belposid" = "BEAS_FTSTL"."BELPOS_ID"
AND "U_posid" = "BEAS_FTSTL"."POS_ID"
) AS abgang,
(
SELECT COALESCE(SUM("IGN1"."Quantity"), 0)
FROM "IGN1"
WHERE "U_belnrid" = "BEAS_FTSTL"."BELNR_ID"
AND "U_belposid" = "BEAS_FTSTL"."BELPOS_ID"
AND "U_posid" = "BEAS_FTSTL"."POS_ID"
) AS zugang,
"BEAS_FTPOS"."MENGE_VERBRAUCH" + COALESCE("BEAS_FTPOS"."MENGE_VERSCHNITT", 0) - "BEAS_FTPOS"."GEL_MENGE" AS "BAUGRUPPE",
"BEAS_FTSTL"."ART1_ID" + '' AS "ART1_ID",
COALESCE("BEAS_FTSTL"."MENGE_JE", 1) AS "MENGE_JE",
COALESCE("BEAS_FTSTL"."MENGE_LAGER", 1) AS "MENGE",
COALESCE("BEAS_FTSTL"."WhsCode", "OITM"."DfltWH") AS "WhsCode",

CASE
WHEN "OITM"."ManBtchNum" = 'Y'
THEN
(
SELECT SUM("OBTQ"."Quantity")
FROM "OBTN",
"OBTQ"
WHERE "OBTN"."ItemCode" = "OBTQ"."ItemCode"
AND "OBTN"."SysNumber" = "OBTQ"."SysNumber"
AND "OBTQ"."WhsCode" = COALESCE("BEAS_FTSTL"."WhsCode", "OITM"."DfltWH")
AND "OBTN"."Status" = '0'
)
ELSE "OITW"."OnHand"
END AS "OnHand"

FROM "BEAS_FTSTL",
"BEAS_FTPOS",
"OITM",
"OITW"
WHERE "OITM"."InvntItem" <> 'N'
AND "BEAS_FTSTL"."BELNR_ID" = 431657
AND "BEAS_FTSTL"."BELPOS_ID" = 10
AND "BEAS_FTPOS"."BELNR_ID" = 431657
AND "BEAS_FTPOS"."BELPOS_ID" = 10
AND (COALESCE("BEAS_FTSTL"."APLANPOS_ID", 0) = 30
OR (COALESCE("BEAS_FTSTL"."APLANPOS_ID", 0) = 0))
AND "BEAS_FTSTL"."ABGKZ" <> 'J'
AND "OITM"."ItemCode" = "BEAS_FTSTL"."ART1_ID"
AND "OITW"."ItemCode" = "OITM"."ItemCode"
AND "OITW"."WhsCode" = COALESCE("BEAS_FTSTL"."WhsCode", "OITM"."DfltWH");

------------------------------------------------------------------------------------------------------
--Query optimized

WITH TAB1 AS (
SELECT
(
SELECT COALESCE(SUM("IGE1"."QUANTITY"), 0)
FROM "IGE1"
WHERE "U_BELNRID" = "BEAS_FTSTL"."BELNR_ID"
AND "U_BELPOSID" = "BEAS_FTSTL"."BELPOS_ID"
AND "U_POSID" = "BEAS_FTSTL"."POS_ID"
) AS ABGANG,
(
SELECT COALESCE(SUM("IGN1"."QUANTITY"), 0)
FROM "IGN1"
WHERE "U_BELNRID" = "BEAS_FTSTL"."BELNR_ID"
AND "U_BELPOSID" = "BEAS_FTSTL"."BELPOS_ID"
AND "U_POSID" = "BEAS_FTSTL"."POS_ID"
) AS ZUGANG,

"BEAS_FTPOS"."MENGE_VERBRAUCH" + COALESCE("BEAS_FTPOS"."MENGE_VERSCHNITT", 0) - "BEAS_FTPOS"."GEL_MENGE" AS "BAUGRUPPE",
"BEAS_FTSTL"."ART1_ID" + '' AS "ART1_ID",
COALESCE("BEAS_FTSTL"."MENGE_JE", 1) AS "MENGE_JE",
COALESCE("BEAS_FTSTL"."MENGE_LAGER", 1) AS "MENGE",
COALESCE("BEAS_FTSTL"."WHSCODE", "OITM"."DFLTWH") AS "WHSCODE",
"OITW"."ONHAND",
"OITM"."MANBTCHNUM"

FROM "BEAS_FTSTL",
"BEAS_FTPOS",
"OITM",
"OITW"
WHERE "OITM"."INVNTITEM" <> 'N'
AND "BEAS_FTSTL"."BELNR_ID" = 431657
AND "BEAS_FTSTL"."BELPOS_ID" = 10
AND "BEAS_FTPOS"."BELNR_ID" = 431657
AND "BEAS_FTPOS"."BELPOS_ID" = 10
AND (COALESCE("BEAS_FTSTL"."APLANPOS_ID", 0) = 30
OR (COALESCE("BEAS_FTSTL"."APLANPOS_ID", 0) = 0))
AND "BEAS_FTSTL"."ABGKZ" <> 'J'
AND "OITM"."ITEMCODE" = "BEAS_FTSTL"."ART1_ID"
AND "OITW"."ITEMCODE" = "OITM"."ITEMCODE"
AND "OITW"."WHSCODE" = COALESCE("BEAS_FTSTL"."WHSCODE", "OITM"."DFLTWH")
),
TAB2 AS (
SELECT SUM("OBTQ"."QUANTITY") "ONHAND",
"OBTQ"."WHSCODE",
"OBTN".ITEMCODE

FROM "OBTN",
"OBTQ"
WHERE "OBTN"."ITEMCODE" = "OBTQ"."ITEMCODE"
AND "OBTN"."SYSNUMBER" = "OBTQ"."SYSNUMBER"
AND "OBTN"."STATUS" = '0'
GROUP BY
"OBTQ"."WHSCODE",
"OBTN".ITEMCODE
)

SELECT
tab1.abgang,
tab1.zugang,
tab1."BAUGRUPPE",
tab1."ART1_ID",
tab1."MENGE_JE",
tab1."MENGE",
tab1."WhsCode",
CASE
WHEN TAB1."MANBTCHNUM" = 'Y'
THEN isnull(TAB2."ONHAND",0)
ELSE TAB1."ONHAND"
END AS "ONHAND"

FROM TAB1
LEFT JOIN TAB2 ON TAB2.ITEMCODE = TAB1.ART1_ID
AND TAB2.WHSCODE = TAB1."WHSCODE"

Regards

João Antonio