The approach of the
etl team is to optimize the sql given by the end clients without doing any of
the following:
- Separating the transaction and the reporting layers
- Developing another transaction layer.
In this module we
shall analyze the joins that where used as part of the execution process.
Thereafter we shall optimize the joins by converting hash joins(no index usage)
to nested loop joins(index usage from one table) and thereafter to merge
joins(optimize index usage from both the tables).
For the process of
join optimization, the etl resource chose the stored procedure
sp_item_purchase_order from the datawarehouse load of turbodata as given under.
DECLARE @SUPPLIER INT;
SET
@SUPPLIER=(SELECT
DIM_SUPPLIER_ID FROM DIM_SUPPLIER WHERE SUPPLIER_NAME LIKE
'N/A');
UPDATE [STATUS] SET [DISPLAYTEXT]='ITEM_PURCHASE_ORDER LOAD DONE',[COMPLETED]=66;
WITH
CTE1 AS(
SELECT A.COMPANY_NAME,A.ORDER_NO,A.PARTY_LEDGER_NAME,A.ENTERED_BY,ORDER_DATE,
A.AMOUNT,A.AMOUNT_SUBSTRING,A.AMOUNT_SUBSTRING2,A.AMOUNT_SUBSTRING_AFTER$,A.CATEGORY,A.COST_CENTER,
A.VOUCHER_TYPE_NAME,A.XYZ,
ISNULL(D.DIM_DATE_ID,1) AS DIM_DATE_ID,
LEDGERNAME
FROM STG_DAYBOOK A
LEFT OUTER JOIN DIM_DATE D
ON A.ORDER_DATE=D.DATE_DESC
WHERE (A.VOUCHER_TYPE_NAME IN
(SELECT DISTINCT
VOUCHER_TYPE_NAME
FROM STG_VOUCHER_TYPE
WHERE VOUCHER_TYPE IN ('PURCHASE')))
--AND A.IS_DEEMED_POSITIVE LIKE '%YES%'
)
,
CTE2 AS (
SELECT SUM (CAST(A.AMOUNT_SUBSTRING2 AS
FLOAT)) AS AMOUNT_SUBSTRING2
,SUM(CAST(A.AMOUNT_SUBSTRING_AFTER$
AS FLOAT)) AS
AMOUNT_SUBSTRING_AFTER$
,A.ORDER_DATE, A.ENTERED_BY,
A.ORDER_NO, A.VOUCHER_TYPE_NAME, ISNULL(A.PARTY_LEDGER_NAME,'N/A') AS PARTY_LEDGER_NAME ,
ISNULL(A.LEDGERNAME,'N/A') AS LEDGERNAME,
K.ITEM_SUPPLIER_ORDER_ID,
A.DIM_DATE_ID,A.COMPANY_NAME,A.CATEGORY,
A.COST_CENTER,A.XYZ
FROM CTE1 A
LEFT OUTER JOIN ITEM_SUPPLIER_ORDER K
ON A.ORDER_NO = K.ORDER_NO
AND
A.VOUCHER_TYPE_NAME=K.VOUCHER_TYPE_NAME
AND A.DIM_DATE_ID=K.DIM_DATE_ID
AND A.COMPANY_NAME=K.COMPANY_NAME
AND A.PARTY_LEDGER_NAME=K.PARTY_LEDGER_NAME
AND
A.LEDGERNAME=K.LEDGERNAME
AND
A.COST_CENTER=K.COST_CENTER
GROUP BY
--A.DIM_ITEM_ID,
A.ORDER_DATE, A.ENTERED_BY,
A.ORDER_NO, A.VOUCHER_TYPE_NAME, K.ITEM_SUPPLIER_ORDER_ID,
ISNULL(A.PARTY_LEDGER_NAME,'N/A')
,A.COMPANY_NAME,A.DIM_DATE_ID,A.CATEGORY,
A.COST_CENTER,A.XYZ,
ISNULL(A.LEDGERNAME,'N/A')
)
,
CTE3 AS (
SELECT COUNT (DISTINCT(ORDER_NO))AS COUNT_ORDER_ID,
ITEM_SUPPLIER_ORDER_ID
FROM CTE2
GROUP BY
ITEM_SUPPLIER_ORDER_ID
),
CTE4 AS (
SELECT A.AMOUNT_SUBSTRING2,
A.AMOUNT_SUBSTRING_AFTER$
,A.ORDER_DATE, A.ENTERED_BY,
A.ORDER_NO, A.VOUCHER_TYPE_NAME, A.PARTY_LEDGER_NAME ,
A.ITEM_SUPPLIER_ORDER_ID,
A.DIM_DATE_ID,A.COMPANY_NAME,A.CATEGORY,
A.COST_CENTER,A.XYZ,A.LEDGERNAME
FROM CTE2 A
JOIN CTE3 B ON
A.ITEM_SUPPLIER_ORDER_ID=B.ITEM_SUPPLIER_ORDER_ID
WHERE B.COUNT_ORDER_ID=1
),
CTE5 AS (
SELECT a.AMOUNT_SUBSTRING2,
a.AMOUNT_SUBSTRING_AFTER$,A.ORDER_DATE, A.ENTERED_BY,
A.ORDER_NO, A.VOUCHER_TYPE_NAME, A.PARTY_LEDGER_NAME ,
A.ITEM_SUPPLIER_ORDER_ID,
--A.DIM_ITEM_ID,
A.DIM_DATE_ID,A.COMPANY_NAME,A.CATEGORY,
A.COST_CENTER,A.XYZ,A.LEDGERNAME
FROM CTE2 A
JOIN CTE3 B ON
A.ITEM_SUPPLIER_ORDER_ID=B.ITEM_SUPPLIER_ORDER_ID
WHERE B.COUNT_ORDER_ID>1
AND A.ENTERED_BY LIKE '%ADMIN%'
),
CTE6 AS (
SELECT a.AMOUNT_SUBSTRING2,
a.AMOUNT_SUBSTRING_AFTER$,A.ORDER_DATE, A.ENTERED_BY,
A.ORDER_NO, A.VOUCHER_TYPE_NAME, A.PARTY_LEDGER_NAME ,
A.ITEM_SUPPLIER_ORDER_ID,
A.DIM_DATE_ID,A.COMPANY_NAME,A.CATEGORY,
A.COST_CENTER,A.XYZ,A.LEDGERNAME
FROM CTE4 A
UNION ALL
SELECT a.AMOUNT_SUBSTRING2,
a.AMOUNT_SUBSTRING_AFTER$,A.ORDER_DATE, A.ENTERED_BY,
A.ORDER_NO, A.VOUCHER_TYPE_NAME, A.PARTY_LEDGER_NAME ,
A.ITEM_SUPPLIER_ORDER_ID,
A.DIM_DATE_ID,A.COMPANY_NAME,A.CATEGORY,
A.COST_CENTER,A.XYZ,A.LEDGERNAME
FROM CTE5 A
)
MERGE ITEM_PURCHASE_ORDER AS TARGET
USING CTE6 AS SOURCE
ON(TARGET.ITEM_SUPPLIER_ORDER_ID
= SOURCE.ITEM_SUPPLIER_ORDER_ID
AND TARGET.DIM_DATE_ID= SOURCE.DIM_DATE_ID
AND TARGET.COMPANY_NAME=SOURCE.COMPANY_NAME
--AND
TARGET.PARTY_LEDGER_NAME=SOURCE.PARTY_LEDGER_NAME
--AND TARGET.LEDGERNAME=SOURCE.LEDGERNAME
)
WHEN
NOT MATCHED BY TARGET
THEN INSERT(AMOUNT_SUBSTRING2,AMOUNT_SUBSTRING_AFTER$,
--ORDER_DATE,
ENTERED_BY,
--ORDER_NO,VOUCHER_TYPE_NAME,
--PARTY_LEDGER_NAME,
ITEM_SUPPLIER_ORDER_ID,
DIM_DATE_ID,
COMPANY_NAME
--,CATEGORY,COST_CENTER,XYZ,LEDGERNAME
)
VALUES(SOURCE.AMOUNT_SUBSTRING2,SOURCE.AMOUNT_SUBSTRING_AFTER$,
--SOURCE.ORDER_DATE ,
SOURCE.ENTERED_BY ,
--SOURCE.ORDER_NO ,
--SOURCE. VOUCHER_TYPE_NAME ,
--SOURCE. PARTY_LEDGER_NAME ,
SOURCE. ITEM_SUPPLIER_ORDER_ID ,
SOURCE. DIM_DATE_ID ,
SOURCE.COMPANY_NAME
--SOURCE.CATEGORY,
--SOURCE.COST_CENTER,
--SOURCE.XYZ,
--SOURCE.LEDGERNAME
)
WHEN
MATCHED
THEN UPDATE SET TARGET.AMOUNT_SUBSTRING2= SOURCE.AMOUNT_SUBSTRING2
,TARGET.AMOUNT_SUBSTRING_AFTER$=SOURCE.AMOUNT_SUBSTRING_AFTER$;
Thereafter the ETL
resource ran the uery execution panel to check for the joins and the index
usage. The resource identified the usage of hash joins wherever possible.
The ETL resource looked for the following:
·
Hash match cost
·
Table scan cost.
Within the hash match cost, the etl
resource looked for the following:
·
The join creating the hash
match.
·
The table size
·
The total costs including the
subtree cost.
The same operation shall be repeated for
the table scans:
Hereafter the ETL resource identified the
join to be worked upon from the given sql.
Steps to convert the hash join to nested
loop join:
- · Take the cte output and store in a temporary table in the same order as the joins.
- · Create index in the same order as joins.
Developing the temporary table for the
second table alo9ng with the indexes in the same order in which the joins have
been done.
Hereafter the ETL developer has 2 options:
- Use the join conditions
- Change the join conditions to where clause
Understanding the impact of where clause.
Using the join conditions instead of the
where clause: in our case instead of using the join conditions instead of where
clause the optimization process remains the same.
Summary of the findings:
·
In converting the hash joins to
nested loops, the maximum memory is used by the following:
o
Sort operation
o
Index seek: indexed nested loop
scan
o
Table scan
For the next steps regarding the join optimization, please contact the following:
Name: Apoorv Chaturvedi
Email: apoorv@mnnbi.com
Phone: +91-8802466356
Website: www.mnnbi.com
Prepared by Ankur Gupta(Intern): https://www.linkedin.com/in/ankur-gupta-3b9a71179
