Forum Discussion
Nested Query Slowing Performance
Hi,
At the beginning of my SQL code I have a cte target table called TARGETS, which contains 3 columns TARGET_NAME, MODE AND VALUE.
Further down in my query I am trying to use a nested query to retrieve values from this table, however, it is really slowing down the performance. I am wondering if there is a better way to do this?
An example of the nested query looks like this
SUM(CASE WHEN LEG = 'Empty' AND LOCATION = 'Perth' THEN (SELECT VALUE FROM TARGETS WHERE TARGET_NAME = 'Empty Perth' AND MODE = 'Yes')
WHEN LEG = 'Empty' AND LOCATION= 'Melbourne' THEN (SELECT VALUE FROM TARGETS WHERE TARGET_NAME= 'Empty Melbourne' AND MODE = 'No')
etc... END) / COUNT(DISTINCT(LEG)) AS Target
FROM LEG_DETAILS
Many Thanks,
5 Replies
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.
