Performance tuning

Database
Enthusiast

Performance tuning

Hello Gurus, I am looking for optimizing the response time of my client report. The report performs these 3 queries on a table(the only queries that are performed on table tbl) on an adhoc basis. sel

from tbl where (IDN IN ) AND (CRETN_DT <= ) AND (CRETN_DT >= ) ;

sel from tbl where (IDN IN ) AND (CLOSD_DT <= ) AND (CLOSD_DT >= );

sel from tbl where (IDN IN ) AND ((CLOSD_DT > ) OR (CLOSD_DT is NULL)) AND (CRETN_DT <= );

The table has UPI on 2 diff columns which arent accessed by the where clause of the queries and has a little over 11,000 recs.Month end and Month start date could be for any month between 2009 to 2011.Record count per IDN is highly varying so not preferable for PI.

I have tried the following:-

1. Created 2 secondary indexes on a combination of (IDN,CLOSD_DT) and (IDN,CRETN_DT) on the tbl hash orderd for IDN/value ordered for DT columns and performed collect stats on all the columns used in the where clause.

-- The explain doesnt show use of any index. It remains unchanged.

2. Created Multi column PPI - case_n (cretn_dt,.....),case_n ((closd_dt,.....)

-- The partitions are used..but response time doesnt show any change.

3. Added a NUSI on IDN in addition to PPI (step 2) (Hoping that NUSI would directly access the p# for the local amp and improvise performance).

-- The explain doesnt show use of any index.Remain same as that of 2. Please guide if there is anything else that I can try and if what I tried makes sense.

Tags (1)
3 REPLIES
Enthusiast

Re: Performance tuning

Seems like words in brackets are trimmed off.Reposting the same with added sqare brackets:-

Hello Gurus,

I am looking for optimizing the response time of my client report.

The report performs these 3 queries on a table(the only queries that are performed on table tbl) on an adhoc basis.

sel [clms] from tbl where (IDN IN [list of idns] ) AND (CRETN_DT <= [month end date] ) AND (CRETN_DT >= [month start date] ) ;

sel [clms] from tbl where (IDN IN [list of idns] ) AND (CLOSD_DT <= [month end date] ) AND (CLOSD_DT >= [month start date] );

sel [clms] from tbl where (IDN IN [list of idns] ) ((CLOSD_DT > [month end date]) OR (CLOSD_DT is NULL)) AND (CRETN_DT <= [month end date] ) ;

The table has UPI on 2 diff columns which arent accessed by the where clause of the queries and has a little over 11,000 recs.Month end and Month start date could be for any month between 2009 to 2011.Record count per IDN is highly varying so not preferable for PI.

I have tried the following:-

1. Created 2 secondary indexes on a combination of (IDN,CLOSD_DT) and (IDN,CRETN_DT) on the tbl hash orderd for IDN/value ordered for DT columns and performed collect stats on all the columns used in the where clause.

-- The explain doesnt show use of any index. It remains unchanged.

2. Created Multi column PPI - case_n (cretn_dt < [month end],.....),case_n ((closd_dt < [month end],.....)

-- The partitions are used..but response time doesnt show any change.

3. Added a NUSI on IDN in addition to PPI (step 2) (Hoping that NUSI would directly access the p# for the local amp and improvise performance).

-- The explain doesnt show use of any index.Remain same as that of 2.

Please guide if there is anything else that I can try and if what I tried makes sense.
Senior Apprentice

Re: Performance tuning

A table with "a little over 11,000 recs" is small, probably just a single datablock per AMP.
The optimizer will not choose an SI access for such small tables, as it's not faster than a Full Table Scan.
If it's using partition elimination, it's not more efficient, because it still has to read a datablock per AMP.

What's the current response time and what do you expect?
And what's the number of AMPs in your system and the average row size?

Dieter
Fan

Re: Performance tuning

HI,

I have a table containing 19 million records. I am executing a query having inner join. The query is taking huge time. Can anybody suggest me query tuning  option?

the query is:

SELECT

BOXINVTRY_HIST_SRC.CORP,

BOXINVTRY_HIST_SRC.BNUMB,

BOXINVTRY_HIST_SRC.REPLICATION_HOSTNAME,

CAST(CAST(BOXINVTRY_HIST_SRC.INCREMENTAL_LOAD_START_TS AS DATE FORMAT'YYYY-MM-DD') AS VARCHAR(30)) AS AUDIT_PROCESS_DT,

CAST(CURRENT_TIMESTAMP(0) AS VARCHAR(30)) AS AUDIT_UPDATED_TS,

CAST(BOXINVTRY_HIST_SRC.AUDIT_DML_TS AS VARCHAR(30)) AS RECORD_END_TS,

CAST(BOXINVTRY_TAR.RECORD_START_TS AS VARCHAR(30)) AS RECORD_START_TS

FROM

NDW_ETL_WORK.WRK_BOXINVTRY BOXINVTRY_HIST_SRC

INNER JOIN

NDW_JRNL_TABLES.BOXINVTRY BOXINVTRY_TAR

ON

BOXINVTRY_HIST_SRC.CORP = BOXINVTRY_TAR.CORP

AND Trim(BOXINVTRY_HIST_SRC.BNUMB) = Trim(BOXINVTRY_TAR.BNUMB)

AND Trim(BOXINVTRY_HIST_SRC.REPLICATION_HOSTNAME) = Trim(BOXINVTRY_TAR.REPLICATION_HOSTNAME)

WHERE

BOXINVTRY_HIST_SRC.REPLICATION_DML_ACTION_CD IN ('I','U','S')

AND BOXINVTRY_TAR.RECORD_END_TS = '9999-12-31 00:00:00'

AND

(

COALESCE(Trim(BOXINVTRY_HIST_SRC.ADDLEQ),'') <> COALESCE(Trim(BOXINVTRY_TAR.ADDLEQ), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.BEQUIP),'') <> COALESCE(Trim(BOXINVTRY_TAR.BEQUIP), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.MFG),'') <> COALESCE(Trim(BOXINVTRY_TAR.MFG), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.LOT),'') <> COALESCE(Trim(BOXINVTRY_TAR.LOT), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.PREVHOUSE),'') <> COALESCE(Trim(BOXINVTRY_TAR.PREVHOUSE), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.PREVCUST),'') <> COALESCE(Trim(BOXINVTRY_TAR.PREVCUST), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.OPTN1),'') <> COALESCE(Trim(BOXINVTRY_TAR.OPTN1), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.OPTN2),'') <> COALESCE(Trim(BOXINVTRY_TAR.OPTN2), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.OPTN3),'') <> COALESCE(Trim(BOXINVTRY_TAR.OPTN3), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.LOC),'') <> COALESCE(Trim(BOXINVTRY_TAR.LOC), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.PLACE),'') <> COALESCE(Trim(BOXINVTRY_TAR.PLACE), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.RECSTAT),'') <> COALESCE(Trim(BOXINVTRY_TAR.RECSTAT), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.BXSTATUS),'') <> COALESCE(Trim(BOXINVTRY_TAR.BXSTATUS), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.ADDRSABLE),'') <> COALESCE(Trim(BOXINVTRY_TAR.ADDRSABLE), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.EVTCPBL),'') <> COALESCE(Trim(BOXINVTRY_TAR.EVTCPBL), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.LASTPROG),'') <> COALESCE(Trim(BOXINVTRY_TAR.LASTPROG), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.BRGPIN),'') <> COALESCE(Trim(BOXINVTRY_TAR.BRGPIN), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.BRGRATG),'') <> COALESCE(Trim(BOXINVTRY_TAR.BRGRATG), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.BRGPPV),'') <> COALESCE(Trim(BOXINVTRY_TAR.BRGPPV), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.BRGEMG),'') <> COALESCE(Trim(BOXINVTRY_TAR.BRGEMG), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.CABLE),'') <> COALESCE(Trim(BOXINVTRY_TAR.CABLE), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.FMT),'') <> COALESCE(Trim(BOXINVTRY_TAR.FMT), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.TIERS),'') <> COALESCE(Trim(BOXINVTRY_TAR.TIERS), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.SPCLS),'') <> COALESCE(Trim(BOXINVTRY_TAR.SPCLS), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.HUB),'') <> COALESCE(Trim(BOXINVTRY_TAR.HUB), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.TINITFLAG),'') <> COALESCE(Trim(BOXINVTRY_TAR.TINITFLAG), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.NORESP),'') <> COALESCE(Trim(BOXINVTRY_TAR.NORESP), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.DWO),'') <> COALESCE(Trim(BOXINVTRY_TAR.DWO), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.HOUSE),'') <> COALESCE(Trim(BOXINVTRY_TAR.HOUSE), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.CUST),'') <> COALESCE(Trim(BOXINVTRY_TAR.CUST), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.WPCNT),'') <> COALESCE(Trim(BOXINVTRY_TAR.WPCNT), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.OPAID),'') <> COALESCE(Trim(BOXINVTRY_TAR.OPAID), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.OPADROP),'') <> COALESCE(Trim(BOXINVTRY_TAR.OPADROP), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.OPASUBMOD),'') <> COALESCE(Trim(BOXINVTRY_TAR.OPASUBMOD), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.PARTITION),'') <> COALESCE(Trim(BOXINVTRY_TAR.PARTITION), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.OPR),'') <> COALESCE(Trim(BOXINVTRY_TAR.OPR), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.BOXEQUIP),'') <> COALESCE(Trim(BOXINVTRY_TAR.BOXEQUIP), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.NVODCAPABLE),'') <> COALESCE(Trim(BOXINVTRY_TAR.NVODCAPABLE), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.IPPVPIN),'') <> COALESCE(Trim(BOXINVTRY_TAR.IPPVPIN), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.UNIT_ADD),'') <> COALESCE(Trim(BOXINVTRY_TAR.UNIT_ADD), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.PREVTIERS),'') <> COALESCE(Trim(BOXINVTRY_TAR.PREVTIERS), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.DELIVERYSYSID),'') <> COALESCE(Trim(BOXINVTRY_TAR.DELIVERYSYSID), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.MODEL),'') <> COALESCE(Trim(BOXINVTRY_TAR.MODEL), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.MACADDRESS),'') <> COALESCE(Trim(BOXINVTRY_TAR.MACADDRESS), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.IPADDR),'') <> COALESCE(Trim(BOXINVTRY_TAR.IPADDR), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.MODEMID),'') <> COALESCE(Trim(BOXINVTRY_TAR.MODEMID), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.EQUIP_VERSION),'') <> COALESCE(Trim(BOXINVTRY_TAR.EQUIP_VERSION), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.EXPANDED_TIERS),'') <> COALESCE(Trim(BOXINVTRY_TAR.EXPANDED_TIERS), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.ADDRESS_TYPE),'') <> COALESCE(Trim(BOXINVTRY_TAR.ADDRESS_TYPE), '') OR

COALESCE(Trim(BOXINVTRY_HIST_SRC.MDULOCATION_COMMENT),'') <> COALESCE(Trim(BOXINVTRY_TAR.MDULOCATION_COMMENT), '')

)

GROUP BY 1,2,3,4,5,6;

The table has index on following cols:

ACCTCORP,CORP and REPLICATION_HOSTNAME