Fine tuning a query

Analytics
Enthusiast

Fine tuning a query

Hi all!

I want to optimize this query given below. There are 50 such queries and each query takes about 2 min. These queries are auto-generated and are dependent on the user interface.
So if the user logic changes these queries can get updated,deleted or added.As of today there are 50 such queries. Each of this query is joined
with the 'BIG' table which has 2 million records. Also this part of the query(SELECT DISTINCT FLD1, NEWFLD, FLD3 FROM DB.RUN2 WHERE
FIELD_NM='ABD' AND FLD2='EFG') is repeated in all the 50 queries with just the values of FIELD_NM changing. I tried to collect all the FIELD_NM
in the 50 queries and ran this query once but it almost takes the same time as it takes currently to run them separately.
Can you please help me in optimizing?

INSERT INTO DB.MAIN
(FLD1,FLD2,FLD3,FLD4,FLD5,FLD6,FLD7,FLD8,FLD9)
SELECT DISTINCT P.FLD1, 'HARDCODE1',P.FLD3,'HARDCODE2','1.0000,'No','1' , 123, 4
FROM (
SELECT DISTINCT T.FLD1, T.FLD3
FROM (SELECT T.FLD1, FLD3
FROM db.big T -----> BIG TABLE WITH 2 MILLION RECORDS
INNER JOIN DB.RUN T1 -----> Table which has scope of records for todays run.
ON T.FLD1 = T1.FLD1 AND T1.LGC = 1 ) T ------> Lgc will change from 1 to 50 depending on the no. of logics.
LEFT JOIN (SELECT DISTINCT FLD1, NEWFLD, FLD3
FROM DB.RUN2
WHERE FIELD_NM='ABD' AND FLD2='EFG' ---> FIELD_NM subject to change but fld2 has only 2 values.
) A1
ON T.FLD1 = A1.FLD1 AND T.FLD3 = A1.FLD3 ----> Join condition for all 50 queries.
AND A1.NEWFLD IN ('CRU1','CRU2') ----> NEWFLD values sublect to change in each query.
LEFT JOIN (SELECT DISTINCT FLD1, NEWFLD, FLD3
FROM DB.RUN2
WHERE FIELD_NM='DEF' AND FLD2='EFG' ---> FIELD_NM subject to change but fld2 has only 2 values.
) A2
ON T.FLD1 = A2.FLD1 AND T.FLD3 = A2.FLD3 ---> Join condition for all 50 queries.
AND CAST(A2.NEWFLD AS INT) > 0
WHERE (A1.FLD1 IS NOT NULL AND A2.FLD1 IS NOT NULL) ----> where condition can change ofr each fo the 50 queries.
) P ;

Thanks!
8 REPLIES

Re: Fine tuning a query

instead of using (distinct) use (group by)
Teradata Employee

Re: Fine tuning a query

Here is something you could try. If your 50 queries are all inserting into that same table "DB.MAIN" the queries will lock that table and run sequentially. If you could change the queries to write to their own temp table then all 50 queries can be run in parallel and at the end you can then insert the temp table values into DB.MAIN.

Re: Fine tuning a query

Also, make sure that you have collect stats on the columns on which your condition is.(T.FLD1,A1.FLD1,T1.LGC) and use group by instead of distinct.
Enthusiast

Re: Fine tuning a query

SELECT

W_INT_ORG_D.INTEGRATION_ID BU_ID,

W_POSITION_D.OU_ID DIVISION_ID,

MIN(W_POSITION_D.INTEGRATION_ID) POSITION_ID,

(CASE WHEN SUBSTR( trim(cast(( current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))), POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)))),3) >= 12 THEN CAST(SUBSTR(TRIM(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))),0,POSITION(' ' in trim( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ))) AS DECIMAL(10))+1 ELSE CAST(SUBSTR(TRIM( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ),0,POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))))) AS DECIMAL(10)) END) LAST_TXN_BEFORE_DAYS, MIN(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' or W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_VEH_ORDER_DT,

Max(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then

CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_VEH_INV_DT,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE ='Sales Order' OR W_ORDER_D.ORDER_TYPE ='Sales Order-Direct Billing'

then W_ORDER_D.ORDER_NUM end ) NO_OF_VEH_ORDERS,

MIN(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_VEH_INV_DT,

Max(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_VEH_INV_DT1,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing'

then W_INVOICE_F.INVC_NUM end ) NO_OF_VEH_INVOICED,

min(case when W_ORDER_D.ORDER_TYPE ='OTC Sales' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_OTC_INV_DT ,

max(case when W_ORDER_D.ORDER_TYPE ='OTC Sales' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_OTC_INV_DT,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'OTC Sales' then W_INVOICE_F.INVC_NUM end ) NO_OF_OTC_INVOICES,

min(case when W_ORDER_D.ORDER_TYPE ='Service Order' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_SERV_INV_DT,

MAX(case when W_ORDER_D.ORDER_TYPE ='Service Order' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_SERV_INV_DT,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'Service Order'

then W_INVOICE_F.INVC_NUM end ) NO_OF_SERV_INVOICES,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'Service Order' then W_ORDER_D.ORDER_NUM end ) NO_OF_JOB_CARDS ,

MAX(CAST(CAST( ( a.OPEN_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) ) LAST_OPTY_DT ,

MIN(CAST(CAST( (a.OPEN_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) ) FIRST_OPTY_DT ,

Max(case when W_ORDER_D.ORDER_TYPE ='Sales Order' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_JC_CREATED_DT,

Min(case when W_ORDER_D.ORDER_TYPE ='Sales Order'then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_JC_CREATED_DT

FROM W_ORDER_D

LEFT JOIN W_INT_ORG_D on

W_ORDER_D.VIS_PR_BU_ID = W_INT_ORG_D.INTEGRATION_ID

LEFT JOIN W_INVOICE_F ON

W_ORDER_D.ROW_WID = W_INVOICE_F.ORDER_WID

LEFT JOIN W_POSITION_D ON

W_ORDER_D.VIS_PR_POS_ID = W_POSITION_D.INTEGRATION_ID

LEFT JOIN W_OPTY_D a ON

W_ORDER_D.VIS_PR_BU_ID = a.VIS_PR_BU_ID

WHERE W_ORDER_D.ORDER_TYPE IN ('Sales Order-Direct Billing', 'Sales Order', 'Service Order', 'OTC Sales')

AND W_ORDER_D.STATUS_CD NOT IN ('CANCELLED','Cancel','Cancelled')

AND W_INT_ORG_D.NAME <> 'Unspecified'

AND w_invoice_f.X_INVC_DT IS NOT NULL

AND COALESCE(W_INVOICE_F.INVC_TYPE_CD_WID,11515) = 11515

AND COALESCE(W_INVOICE_F.STATUS_CD_WID,16778 ) = 16778

GROUP BY ROLLUP(W_POSITION_D.OU_ID), W_INT_ORG_D.INTEGRATION_ID,

(CASE WHEN SUBSTR( trim(cast(( current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))), POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)))),3) >= 12 THEN CAST(SUBSTR(TRIM(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))),0,POSITION(' ' in trim( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ))) AS DECIMAL(10))+1 ELSE CAST(SUBSTR(TRIM( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ),0,POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))))) AS DECIMAL(10)) END)

Please suggested me , how to tune this queryDear All, I Did all collect stats , created NUSI index for all filter conditions

Enthusiast

Re: Fine tuning a query

Dear All,

         Problem has been solved by creating partition on  W_ORDER_D.ORDER_TYPE table

Enthusiast

Re: Fine tuning a query

Unequality operators query :-

SELECT WC_SPARES_STOCK_F.ROW_WID, ETL_PROC_WID , PR_VIS_ORG_WID , INVLOC_WID, PROD_WID, CLOSING as OPENING, 0 as SHIP, 0 as RECEIVE, CLOSING, StartDT.CALENDAR_DATE as StartDT, EndDT.CALENDAR_DATE as EndDT , DEALER_NAME, BU_NAME, PROD_NAME,PROD_DESC,PROD_UOM, FABRIC_CD,TM_IND, LOCATION_NAME, StartDT.ROW_WID as START_DT_WID, EndDT.ROW_WID as END_DT_WID,
PRODINV_CLOSING as PRODINV_OPENING , PRODINV_CLOSING,WEIGHTED_AVG
FROM WC_SPARES_STOCK_F, W_DAY_D StartDT, W_DAY_D EndDT
WHERE (
StartDT.ROW_WID > TXN_DT_WID
AND StartDT.ROW_WID <= VALID_DT_WID
AND StartDT.ROW_WID <= EndDT.ROW_WID)

Explain Plan :-

is eligible for synchronized scanning. The result goes into Spool
4 (all_amps), which is built locally on the AMPs. The result
spool file will not be cached in memory. The size of Spool 4 is
estimated with low confidence to be 862,271,585,302 rows (
318,178,214,976,438 bytes). The estimated time for this step is
72 hours and 47 minutes.
6) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to Spool 4 (Last Use) by way of an
all-rows scan. Spool 2 and Spool 4 are joined using a product
join, with a join condition of ("ROW_WID <= ROW_WID"). The result
goes into Spool 1 (all_amps), which is built locally on the AMPs.
The result spool file will not be cached in memory. The size of
Spool 1 is estimated with low confidence to be *** rows (***
bytes). The estimated time for this step is 725,506 hours and 56
minutes.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 725,579 hours and 44
minutes.

Equi join
SELECT WC_SPARES_STOCK_F.ROW_WID, ETL_PROC_WID ,
PR_VIS_ORG_WID , INVLOC_WID, PROD_WID, CLOSING as OPENING,
0 as SHIP, 0 as RECEIVE, CLOSING, StartDT.CALENDAR_DATE as StartDT,
EndDT.CALENDAR_DATE as EndDT , DEALER_NAME, BU_NAME, PROD_NAME,PROD_DESC,PROD_UOM, FABRIC_CD,
TM_IND, LOCATION_NAME, StartDT.ROW_WID as START_DT_WID,
EndDT.ROW_WID as END_DT_WID,
PRODINV_CLOSING as PRODINV_OPENING , PRODINV_CLOSING,WEIGHTED_AVG
FROM WC_SPARES_STOCK_F
INNER JOIN W_DAY_D StartDT
ON StartDT.ROW_WID = TXN_DT_WID
AND StartDT.ROW_WID = VALID_DT_WID
INNER JOIN W_DAY_D EndDT
ON StartDT.ROW_WID = EndDT.ROW_WID

memory. The result goes into Spool 1 (all_amps), which is built
locally on the AMPs. The size of Spool 1 is estimated with low
confidence to be 1,502,800 rows (680,768,400 bytes). The
estimated time for this step is 41.68 seconds.
6) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 41.89 seconds.

How can I tune same (NON-EQUIALITY) Query please help me form this situation?

This is query (NON-EQUIALITY-above query) which we generated by OBIEE as per business logic, help me out from this situation 

Enthusiast

Re: Fine tuning a query

Would you please give me solution ?

Awaiting for your response :(

Enthusiast

Re: Fine tuning a query

Hi Mahesh,

> collect the stats on where clause columns as explain show the low confidence on each step.

> You can add the where clause columns to Primary index if its unique and usage is more . or create the Secondary Index on those columns which can create and drop at any time.

> Mention the table alias properly before the column names in where caluse as its doing product Join.

Try this,

SELECT   WC_SPARES_STOCK_F.ROW_WID, ETL_PROC_WID , PR_VIS_ORG_WID  , INVLOC_WID, PROD_WID, CLOSING as OPENING, 0 as SHIP, 0 as RECEIVE, CLOSING, StartDT.CALENDAR_DATE as StartDT,  EndDT.CALENDAR_DATE as EndDT , DEALER_NAME, BU_NAME, PROD_NAME,PROD_DESC,PROD_UOM, FABRIC_CD,TM_IND, LOCATION_NAME, StartDT.ROW_WID as START_DT_WID,  EndDT.ROW_WID as END_DT_WID,

PRODINV_CLOSING as PRODINV_OPENING , PRODINV_CLOSING,WEIGHTED_AVG

FROM WC_SPARES_STOCK_F as STK 

Inner Join W_DAY_D StartDT on 

StartDT.ROW_WID > STK.TXN_DT_WID 

and StartDT.ROW_WID <= VALID_DT_WID

inner join W_DAY_D EndDT  on

StartDT.ROW_WID <= EndDT.ROW_WID)

 

Let me know if it works.