Query Optimisation

Database

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

5 REPLIES
Enthusiast

Re: Query Optimisation

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,

Junior Contributor

Re: Query Optimisation

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.

Teradata Employee

Re: Query Optimisation

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

Re: Query Optimisation

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

Teradata Employee

Re: Query Optimisation

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.