[Solved] Is there any alter way to increase the execution speed. Currently the below query taking 6 seconds to execute

EverSQL Database Performance Knowledge Base

Is there any alter way to increase the execution speed. Currently the below query taking 6 seconds to execute

SELECT * 
FROM
    (SELECT  
         PER_CUR.DATEASSTRINGFORCLASS(PER_DATE_FROM,'GB') AS PERDATE,
         PER_NO,
         rangeutil.f_setpcdrangeperiod('M', PER_CUR.DATEASSTRINGFORCLASS(PER_DATE_FROM, 'GB')) AS PERIOD
     FROM   
         PER_W  
     WHERE  
         PERT_CODE = 'YM2' 
         AND PER_NO >= RANGEUTIL.ADDNUMPERIOD('YM2', RANGEUTIL.DATEPERIOD('YM2'), -4) 
         AND per_date_from < '02-JUN-21'
             
     UNION

     SELECT  
         PER_CUR.DATEASSTRINGFORCLASS(PER_DATE_FROM,'GB') AS PERDATE, PER_NO,
         rangeutil.f_setpcdrangeperiod('M', PER_CUR.DATEASSTRINGFORCLASS(PER_DATE_FROM,'GB')) AS PERIOD
     FROM 
         PER_W  
     WHERE  
         PERT_CODE = 'YM' 
         AND per_date_from > '01-JUN-21' 
         AND PER_NO >= RANGEUTIL.ADDNUMPERIOD('YM', RANGEUTIL.DATEPERIOD('YM'), -4))
WHERE
    per_no >= rangeutil.f_defaultPcdRangePeriod('M')
ORDER BY 
    2;

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Use UNION ALL instead of UNION (query line: 18): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.
The optimized query:
SELECT
        * 
    FROM
        (SELECT
            PER_CUR.DATEASSTRINGFORCLASS(PER_W.PER_DATE_FROM,
            'GB') AS PERDATE,
            PER_W.PER_NO,
            rangeutil.f_setpcdrangeperiod('M',
            PER_CUR.DATEASSTRINGFORCLASS(PER_W.PER_DATE_FROM,
            'GB')) AS PERIOD 
        FROM
            PER_W 
        WHERE
            PER_W.PERT_CODE = 'YM2' 
            AND PER_W.PER_NO >= RANGEUTIL.ADDNUMPERIOD('YM2', RANGEUTIL.DATEPERIOD('YM2'), -4) 
            AND PER_W.per_date_from < '02-JUN-21' 
        UNION
        SELECT
            PER_CUR.DATEASSTRINGFORCLASS(PER_W.PER_DATE_FROM,
            'GB') AS PERDATE,
            PER_W.PER_NO,
            rangeutil.f_setpcdrangeperiod('M',
            PER_CUR.DATEASSTRINGFORCLASS(PER_W.PER_DATE_FROM,
            'GB')) AS PERIOD 
        FROM
            PER_W 
        WHERE
            PER_W.PERT_CODE = 'YM' 
            AND PER_W.per_date_from > '01-JUN-21' 
            AND PER_W.PER_NO >= RANGEUTIL.ADDNUMPERIOD('YM', RANGEUTIL.DATEPERIOD('YM'), -4)
    ) 
WHERE
    per_no >= rangeutil.f_defaultPcdRangePeriod('M') 
ORDER BY
    2

Related Articles



* original question posted on StackOverflow here.