select Query taking 3 hr to fetch 160 Million records

Database
Fan

select Query taking 3 hr to fetch 160 Million records

Hi

I am using teradata SQL assistant export result functionality to fetch the results for below qyery and its taking 3 hrs to fetch 160 million record.

 

Select
aa.ABC_Date
, aa.ABC_ID
, aa.ABC_DCode
, aa.ABC_Sys_Code
, aa.ABC_Key
, aa.ABC_Ctr_Id
, aa.ABC_AccId
, aa.ABC_P_Id
, aa.ABC_Rate
, aa.ABC_Entrd_Amt
, aa.ABC_M_Num
, aa.ABC_Name
, bb.PQR_Id
, bb.MNP_Id

From zkp1tb.ABCtab aa
LEFT OUTER JOIN zkp1tb.PQRtab bb
ON aa.ABC_Key = bb. PQR_Key
and '20130931' between bb.F_Date and bb.T_Date
Where aa.ABCDate ='20130931'
and aa.ABC_Ctr_Id = '057577646';

 

is there anyway we can tune the query?

3 REPLIES
Enthusiast

Re: select Query taking 3 hr to fetch 160 Million records

Normally I'd what is the explain plan and physical design, but what about this join:

and '20130931' between bb.F_Date and bb.T_Date
Where aa.ABCDate ='20130931'

 

You are using the same date, but are not joining the fields together? Also no data type conversion, is it a date field or varchar?

Senior Apprentice

Re: select Query taking 3 hr to fetch 160 Million records

How many GBs is the result?

Are you sure it's a slow execution on Teradata?

In SQL Assistant's history there's DBS Time and Fetch Time, probably most of the time is the fetch part.

 

For 160 million rows you better switch to TPT, if you still want to use SQLA you should use a .NET connection instead of ODBC.

Fan

Re: select Query taking 3 hr to fetch 160 Million records

bb.F_Date , bb.T_Date and aa.ABCDate are date type. Also please note the tables have monthly partitions created.