CODEWITHSUNDEEP
sqldatabaseperformanceteradata
visakh
visakh

Reputation: 2553

Tuning in Teradata (group by)

I am trying to tune a query in Teradata. It's pretty huge, so I am giving below the outline:

SEL column_1, column_2......column_20, sum(column_21), sum(column_22),.....sum(column_30)
from table_a a
inner join table_b b
on conditions...
group by column_1, ...,column_20;

I am trying to tune this. It's hitting a performance roadblock in the group by. The tables A and B are huge (more than 2 billion records).

I tried the following options, but none of them improved the performance:

1) Collected all necessary stats

2) Created a JI on the columns from table A and B

3) Created an AJI on the columns and the summations from table A and B

4) Created a SI on each of the tables for the columns involved in group by.

Can someone suggest how to proceed further?

Upvotes: 0

Views: 1669

Answers (1)

dnsmkl
dnsmkl

Reputation: 792

It is very hard to say anything , without having any details about:

  • query (is it always they same, or there is always different WHERE )
  • structure of A and B (primary indexes/partitioning)
  • execution plan
  • log of execution (to see where is the bottleneck)

But if we assume that this single query is exactly the same every time then AJI is created and used during execution, then then only improvement that I can think of would be trying to adjust primary index of JI, so its distribution among amp would be as uniform as possible

(BTW if AJI is used then steps 2 & 4 would be waste of your time and db space)

Upvotes: 2

Related Questions