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.
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.
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.
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:
It includes the documentation, and you good to go.