sbornstein2
asked on
PL/SQL Sub Query question
I have an oracle pl/sql query that performance is really bad and hoping I can adjust if possible. I have the following query and where you see the nested sub queries I have about 10 of those just showing 2. Is there any better way to handle this for the sub queries as the timing is really bad when running with the nested sub queries.
SELECT
c.CUST_ID,
c.CUST_NAME,
, (select r.FORMATTED_RESULT
FROM TEST t
JOIN TEST_TEMPLATE tt ON t.TEST_TEMPLATE_ID = tt.TEST_TEMPLATE_ID AND tt.NAME = 'Test1'
JOIN RESULT r ON t.TEST_ID = r.TEST_ID
JOIN RESULT_TEMPLATE rt ON r.RESULT_TEMPLATE_ID = rt.RESULT_TEMPLATE_ID AND rt.NAME = 'Result1'
JOIN (SELECT MAX(TEST_ID) AS TEST_ID, CUST_ID, NAME
FROM TEST
GROUP BY CUST_ID, NAME) g ON g.TEST_ID = t.TEST_ID AND g.CUST_ID = t.CUST_ID AND g.NAME = t.NAME
WHERE t.CUST_ID = C.CUST_ID) "CustTest1"
, (select r.FORMATTED_RESULT
FROM TEST t
JOIN TEST_TEMPLATE tt ON t.TEST_TEMPLATE_ID = tt.TEST_TEMPLATE_ID AND tt.NAME = 'Test2'
JOIN RESULT r ON t.TEST_ID = r.TEST_ID
JOIN RESULT_TEMPLATE rt ON r.RESULT_TEMPLATE_ID = rt.RESULT_TEMPLATE_ID AND rt.NAME = 'Result2'
JOIN (SELECT MAX(TEST_ID) AS TEST_ID, CUST_ID, NAME
FROM TEST
GROUP BY CUST_ID, NAME) g ON g.TEST_ID = t.TEST_ID AND g.CUST_ID = t.CUST_ID AND g.NAME = t.NAME
WHERE t.CUST_ID = alq.CUST_ID) "CustTest2"
FROM CUSTOMER C
SELECT
c.CUST_ID,
c.CUST_NAME,
, (select r.FORMATTED_RESULT
FROM TEST t
JOIN TEST_TEMPLATE tt ON t.TEST_TEMPLATE_ID = tt.TEST_TEMPLATE_ID AND tt.NAME = 'Test1'
JOIN RESULT r ON t.TEST_ID = r.TEST_ID
JOIN RESULT_TEMPLATE rt ON r.RESULT_TEMPLATE_ID = rt.RESULT_TEMPLATE_ID AND rt.NAME = 'Result1'
JOIN (SELECT MAX(TEST_ID) AS TEST_ID, CUST_ID, NAME
FROM TEST
GROUP BY CUST_ID, NAME) g ON g.TEST_ID = t.TEST_ID AND g.CUST_ID = t.CUST_ID AND g.NAME = t.NAME
WHERE t.CUST_ID = C.CUST_ID) "CustTest1"
, (select r.FORMATTED_RESULT
FROM TEST t
JOIN TEST_TEMPLATE tt ON t.TEST_TEMPLATE_ID = tt.TEST_TEMPLATE_ID AND tt.NAME = 'Test2'
JOIN RESULT r ON t.TEST_ID = r.TEST_ID
JOIN RESULT_TEMPLATE rt ON r.RESULT_TEMPLATE_ID = rt.RESULT_TEMPLATE_ID AND rt.NAME = 'Result2'
JOIN (SELECT MAX(TEST_ID) AS TEST_ID, CUST_ID, NAME
FROM TEST
GROUP BY CUST_ID, NAME) g ON g.TEST_ID = t.TEST_ID AND g.CUST_ID = t.CUST_ID AND g.NAME = t.NAME
WHERE t.CUST_ID = alq.CUST_ID) "CustTest2"
FROM CUSTOMER C
YES, OMG YES can that be rewritten.
Then you think you need to repeat the same thing over and over, there is a problem. About 100% of the time, when you need to re-query the same table over and over, there is a problem.
Instead of posting the entire query, can you provide some sample data and expected results?
I see no need to select from TEST then get the MAX from test in a subquery for EVERY SINGLE result.
The tt.name and rt.name sohould be simple CASE statements to get the results.
Maybe something like moving them from the join clauses to the outer query like:
Maybe a MAX with them as the old PIVOT method:
or a PIVOT itself?
it is hard to guess without a better idea of the actual data and results.
Then you think you need to repeat the same thing over and over, there is a problem. About 100% of the time, when you need to re-query the same table over and over, there is a problem.
Instead of posting the entire query, can you provide some sample data and expected results?
I see no need to select from TEST then get the MAX from test in a subquery for EVERY SINGLE result.
The tt.name and rt.name sohould be simple CASE statements to get the results.
Maybe something like moving them from the join clauses to the outer query like:
..
case when tt.name='Test1' and rt.name='Result1' then r.formatted_result end as custtest1,
case when tt.name='Test2' and rt.name='Result2' then r.formatted_result end as custtest1,
...Maybe a MAX with them as the old PIVOT method:
..
MAX(case when tt.name='Test1' and rt.name='Result1' then r.formatted_result end) as custtest1,
MAX(case when tt.name='Test2' and rt.name='Result2' then r.formatted_result end) as custtest1,
...or a PIVOT itself?
it is hard to guess without a better idea of the actual data and results.
Also, maybe this query is used in a PL\SQL package (or function or procedure) in your application, but what you posted here is a SQL query, not PL\SQL code.
Can you describe the business problem that this query is intended to help with?
Can you describe the business problem that this query is intended to help with?
ASKER
How does the case statement handle getting the MAX(Test_Id). Yes the alq.Cust_Id was supposed to be C.Cust_Id my bad.
You might be able to move the MAX to the outside query?
You might be able to use the ROW_NUMBER window function to pull back the first one?
You might be able to use the new oracle LIMIT clause to pull back the first one?
I cannot say how I would write this for sure until I get sample data and expected results so I can set up a test case.
You might be able to use the ROW_NUMBER window function to pull back the first one?
You might be able to use the new oracle LIMIT clause to pull back the first one?
I cannot say how I would write this for sure until I get sample data and expected results so I can set up a test case.
Usually in Oracle, "running with nested sub queries" (or "in-line views") results in slow performance, especially when the number of rows returned by the nested sub-queries is large.
Can you describe the business or data problem that this query is intended to help with? There may be a way to solve this problems without using the nested sub-queries. Yes, we can make some guesses from the SQL you posted as to what the actual business problem or question might be, but if you can give us a simple explanation or description, that may make it much easier to suggest a simpler, and more-efficient way to solve the problem in SQL.
Can you describe the business or data problem that this query is intended to help with? There may be a way to solve this problems without using the nested sub-queries. Yes, we can make some guesses from the SQL you posted as to what the actual business problem or question might be, but if you can give us a simple explanation or description, that may make it much easier to suggest a simpler, and more-efficient way to solve the problem in SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You have NOT done us a favour by hiding the full query, for example where is the alias alq?
WHERE t.CUST_ID = alq.CUST_ID
Can you just paste in the full query please?