Performance tuning-2

Database
Enthusiast

Performance tuning-2

How can i increase the performance for the following query
where the EXPLAIN commend give the following result:

Explanation
1) First, we lock GPW_DATA.B01_BKP1 for access.
2) Next, we do an all-AMPs RETRIEVE step from GPW_DATA.B01_BKP1 by
way of an all-rows scan with a condition of (
"((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'AR') OR
((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'ND') OR
((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'LD') OR
((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'SD') OR
((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'MD') OR
(GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'DD')))))) AND
((GPW_DATA.B01_BKP1.GPWB01_FILL_RPRT_Y <= DATE '2007-11-30') AND
(GPW_DATA.B01_BKP1.GPWB01_FILL_RPRT_Y >= DATE '2007-11-01'))")
into Spool 1 (group_amps), which is built locally on the AMPs.
Then we do a SORT to order Spool 1 by the sort key in spool field1.
The size of Spool 1 is estimated with no confidence to be 697,168
rows. The estimated time for this step is 0.93 seconds.
3) 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 0.93 seconds.
9 REPLIES
Teradata Employee

Re: Performance tuning-2

Did you try using a secondary index for the GPWB01_ORDLN_STS_C column. That could help.
Make sure you have stats for the index and the column.
Enthusiast

Re: Performance tuning-2

GPW_DATA.B01_BKP1.GPWB01_FILL_RPRT_Y <= DATE '2007-11-30') AND
(GPW_DATA.B01_BKP1.GPWB01_FILL_RPRT_Y >= DATE '2007-11-01'))")

this two,
if you didn't use '=', no index will be used
Enthusiast

Re: Performance tuning-2

i saw you use date between xxxx and xxxx
if this is a very big table, and you data distributed commonly by date,
perhaps you may use PPI
Enthusiast

Re: Performance tuning-2

Yes.. I understand your point that when we are using NON-EQUALITY in the where clause, the SI is not used effectively, and Full table scan will take place. But in this case we are not able to get rid of it.

But any way the table is already using SI along with UPI.
UNIQUE PRIMARY INDEX ( GPWB01_ORDER_ID_R )
INDEX ( GPWB01_FILL_RPRT_Y ,GPWB01_ORDLN_STS_C )
INDEX ( GPWB01_1STCK_DEP_C )
INDEX ( GPWB01_STCK_FLOC_C )
INDEX ( GPWB01_ORIG_DEP_C );
Enthusiast

Re: Performance tuning-2

Hi Madhavi,

The following options can be tried out. But activities like creating index, collecting stats etc are resource intensive and comes with a cost.
So use them judiciously(i.e if they give you the result you want :) )

- Create Partitions on Date Column.(PPIs and NUSIs typically help Range queries)
- Try using IN clause instead of OR clauses and replace = with Between clause and evaluate the explain.
- Collect statistics on the two SI columns used in the where clause(Your Explain Plan says No confidence even while retrieving data directly from a single table).
- Can create a single table join index with those two columns as the primary index.
- Check if you are using an Order By clause to sort data and remove it if not needed.
- Remove OR clause and write it as Union of 6 statements and see the explain.(Also see if BMSMS happens)
- Try using a derived query to first retrieve based on one Index column and from that retrieve the necessary rows based on the second index column(Ideally this should not effective as this consumes spool space, but donno how effective is your query in a relative sense)

Regards,
Annal T
Enthusiast

Re: Performance tuning-2

Madhavi,
I strongly agree with Annal T,
you must avoid useing the expression that will cause the SI doesn't work, it's a rule
Enthusiast

Re: Performance tuning-2

Thanks Annal.

The actual select clause was happening against a view. and the base table is really very big and is used for various other purposes also, like joins etc.

And the actual query used IN clause as well as Between clause only, But only the EXPLAIN command retrived the answer like that.
This is the reason in which i am not able to decide for defining PPI or Join index.

Thanks
Madhavi.
Enthusiast

Re: Performance tuning-2

Hi Madhavi,

This is because the parser will decide the final execution plan which ever is least expensive though we explicitly specify ‘IN’ or ‘Between’ clause in the query.

Try to decide by exploring the where condition column values…

Regards,
Balamurugan
Enthusiast

Re: Performance tuning-2

Thats the unfortunate thing here that we cant modify/edit the where clause of the query.