CODEWITHSUNDEEP
sqloracle-database
gp88
gp88

Reputation: 23

How to improve performance for a view with a where clause that uses a subquery with max()?

I have the following view which is used to check how much each agent has sold each year:

SELECT C.SUBSIDIARY_ID AS SUBSIDIARY_ID,
       SUM(CI.CURRENT_VALUE) AS TOTAL_SALES,
       C.AGENT_USERNAME AS AGENT,
       TO_CHAR(C.SIGN_DATE, 'YYYY') AS SIGN_YEAR
  FROM CONTRACT_INFO CI
 INNER JOIN CONTRACT C ON C.ID = CI.CONTRACT_ID
 WHERE CI.UPDATE_DATE = (SELECT MAX(CI2.UPDATE_DATE) 
                        FROM CONTRACT_INFO CI2 
                       WHERE CI.CONTRACT_ID = CI2.CONTRACT_ID)
 GROUP BY SUBS.NAME, C.SUBSIDIARY_ID, C.AGENT_USERNAME, TO_CHAR(C.SIGN_DATE, 'YYYY');

This view is queried by an application that might filter by SIGN_YEAR, SUBSIDIARY_ID and/or AGENT

The CONTRACT_INFO table is constantly updated with new renegotiation values and other things, so we need the max update_date to get the latest values.

Everything works fine when we query by SIGN_YEAR and/or SUBSIDIARY_ID. When queried by AGENT, it takes over 5min to execute the query.

Each of these tables contain over 30 columns, SIGN_YEAR, SUBSIDIARY_ID and AGENT are indexed in their tables, and I can't use an id instead of the agent's username because reasons(expected values for agent's username: 'john.doe').

How can I improve the performance when queried by AGENT?

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

For the query as you have written it, you want an index on contact_info(contract_id, update_date)

However, there are other ways to write the query. With the same index, you can try:

SELECT C.SUBSIDIARY_ID AS SUBSIDIARY_ID,
       SUM(CI.CURRENT_VALUE) AS TOTAL_SALES,
       C.AGENT_USERNAME AS AGENT,
       TO_CHAR(C.SIGN_DATE, 'YYYY') AS SIGN_YEAR
FROM CONTRACT C JOIN
     (SELECT CI.*,
             ROW_NUMBER() OVER (PARTITION BY CONTRACT_ID ORDER BY UPDATE_DATE DESC) as SEQNUM
      FROM CONTRACT_INFO CI
     ) CI
     ON C.ID = CI.CONTRACT_ID
WHERE seqnum = 1
GROUP BY SUBS.NAME, C.SUBSIDIARY_ID, C.AGENT_USERNAME, TO_CHAR(C.SIGN_DATE, 'YYYY');

Upvotes: 1

Related Questions