Forum Discussion
Nested Query Slowing Performance
What's the relationship between TARGETS and LEG_DETAILS? and sample data will help.
TARGET TABLE DATA
TARGET_NAME, MODE AND VALUE
Empty Perth, Yes, 10
Empty Melbourne, Yes, 5
Empty Sydney, Yes, 6
Empty Perth, No, 9
Empty Melbourne, No, 4
Empty Sydney, No, 3
etc...
LEG_DETAILS TABLE
ID, DATE, LEG, LOCATION, MODE, ACTUAL_DURATION
1, 26/11/24, Empty, Perth, Yes, 12
2, 26/11/24, Empty, Perth, Yes, 11
3, 26/11/24, Empty, Sydney, No, 6
4, 26/11/24, Empty, Sydney, Yes, 5
5, 26/11/24, Empty, Perth, No, 4
6, 26/11/24, Empty, Melbourne, Yes, 3
I am wanting to set the target dynamically. So for example it would look at my LEG_DETAILS TABLE and for example all legs that are empty, perth and Yes would return 10 as the target
Looks like TARGET_NAME of TARGET table = LEG +' ' + LOCATION of LEG_DETAILS table, right?
And how did you build the CTE table? How many rows in the source table of CTE and LEG_DETAILS?
Do they have indexes and what are the structures of the indexes.
Hey!
There are no indexes.
You are correct with the target name.
About 50 rows in the target CTE table and 1000 in the leg details table
