All Forums Database
gtu4teradata 4 posts Joined 03/11
29 Jan 2014
Query Optimisation

Hi,
I am trying to do a self join with Flights table to get 3 leg flights. What is the best way to achieve the result.
The query for 2 leg looks something like this.
SELECT * FROM
JM_F1 F1 INNER JOIN JM_F2 F2
ON F1.DEST_AIRPT_CD = F2.ORIG_AIRPT_CD
AND F2.DEST_AIRPT_CD <> F1.ORIG_AIRPT_CD
AND F1.DEST_AIRPT_CD <> F2.DEST_AIRPT_CD
AND F2.SCHED_LOC_DEP_DT BETWEEN F1.ACTUAL_DAY1 AND F1.ACTUAL_DT;
PI of JM_F1 is DEST_AIRPT_CD
PI of JM_F2 is ORIG_AIRPT_CD
This is an ideal situation where the joining columns are PI. But the data for some airports are so large that the processing time is huge.
What are other optimisation techniques to reduce the processing time....like Partition etc.
Any help would be greatly appreciated.
Thanks....gtu
 

Raja_KT 1246 posts Joined 07/09
30 Jan 2014

How many nodes, amps etc your system has? I dont think airport data is so huge. Maybe you can try with exists/not exists in place of <> and see. If data volume is huge , yes partitioning is one thought.
 

How about this thought, obtaining min and max date if data volume is huge and fit this 

as a derived table into  your query.

 

(select min(F1.ACTUAL_DAY1) , max(F1.ACTUAL_DT) from F1 WHERE ='XXXX') AS dt(min_dt,max_dt) where SCHED_LOC_DEP_DT between dt.min_dt and dt.max_dt
Cheers,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
30 Jan 2014

SQL is not the best tool to do a shortest-path query :-)
If you don't start with specific airports you will create all possible combinations (similar to cross joining). And when you add a 3rd/4th leg, the number of rows will explode.
You might try it with intermediate table or in a loop in a SP where you filter most of the rows (based on price/duration/whatever) before you do the next step.
 

Dieter

Adeel Chaudhry 773 posts Joined 04/08
31 Jan 2014

Intermediate tables seem the best option in this scenario. You havent shared row-counts/time its taking.

-- If you are stuck at something .... consider it an opportunity to think anew.

edu.aman1489 1 post Joined 01/14
01 Feb 2014

Could you please help me with any great training institute for teradata ?

Adeel Chaudhry 773 posts Joined 04/08
03 Feb 2014

Wrong forum .... what do you wish to achieve? Do you have TD accesible on your machine? If not start by downloading TD from following website:
 
http://downloads.teradata.com/download/database/teradata-express/vmware
 
It includes the documentation, and you good to go.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.