[Solved] why query is taking too long

EverSQL Database Performance Knowledge Base

why query is taking too long

I have a query that should return around 10000 rows. The db itself is very large. I have run a simple query and it returned a result in less than 3 seconds. But when a more complex code it takes way too long.

In my code I have done a nested select and a case statement. However, when I run my code it takes over an hour to return a result. What can I do to the code that would decrease this execution time.

SELECT ticker_symb, day_sum_eff, cusip, 
clos_prc, 
nclos_prc,
 case
     when  clos_prc is null and nclos_prc is not null 
     then (nclos_prc - LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip)) 
     when  clos_prc is not null and nclos_prc is null
       then (LEAD( nclos_prc ignore nulls) OVER (ORDER BY cusip)- LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip))
       else NULL
   end DIFF
FROM (SELECT  
      day_sum_eff, 
      cusip,
      ticker_symb, 
      clos_prc, 
      nclos_prc,
      case
           when clos_prc is null and nclos_prc is not null 
           then (nclos_prc - LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip)) 
           when  clos_prc is not null and nclos_prc is null
           then LEAD( nclos_prc ignore nulls) OVER (ORDER BY cusip)- LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip)
          else NULL
          end DIFF
    from  MKTDATA.MARKET_DAILY_SUMMARY 
    WHERE day_sum_eff >=  '1-JUN-2017' and 
          day_sum_eff <=  '10-JUN-2017' )
order by  day_sum_eff_,fmr_iss_cusip OFFSET 0 ROWS FETCH NEXT 3 ROW ONLY;

EXCUTION PLAN TABLE

PLAN_TABLE_OUTPUT

Plan hash value: 831959278

----------------------------------------------------------
| Id  | Operation                 | Name                 |
----------------------------------------------------------
|   0 | SELECT STATEMENT          |                      |
|   1 |  VIEW                     |                      |
|   2 |   WINDOW SORT PUSHED RANK |                      |
|   3 |    WINDOW SORT            |                      |
|   4 |     PARTITION RANGE SINGLE|                      |
|   5 |      TABLE ACCESS FULL    | MARKET_DAILY_SUMMARY |
----------------------------------------------------------

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. Avoid Subqueries (query line: 20): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
The optimized query:
SELECT
        ticker_symb,
        day_sum_eff,
        cusip,
        clos_prc,
        nclos_prc,
        CASE 
            WHEN clos_prc IS NULL 
            AND nclos_prc IS NOT NULL THEN (nclos_prc - LAG(nclos_prc IGNORE NULLS) OVER (ORDER 
        BY
            cusip)) 
            WHEN clos_prc IS NOT NULL 
            AND nclos_prc IS NULL THEN (LEAD(nclos_prc IGNORE NULLS) OVER (ORDER 
        BY
            cusip) - LAG(nclos_prc IGNORE NULLS) OVER (ORDER 
        BY
            cusip)) 
            ELSE NULL END DIFF 
FROM
(SELECT
    MKTDATA.MARKET_DAILY_SUMMARY.day_sum_eff,
    MKTDATA.MARKET_DAILY_SUMMARY.cusip,
    MKTDATA.MARKET_DAILY_SUMMARY.ticker_symb,
    MKTDATA.MARKET_DAILY_SUMMARY.clos_prc,
    MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc,
    CASE 
        WHEN MKTDATA.MARKET_DAILY_SUMMARY.clos_prc IS NULL 
        AND MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IS NOT NULL THEN (MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc - LAG(MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IGNORE NULLS) OVER (ORDER 
    BY
        MKTDATA.MARKET_DAILY_SUMMARY.cusip)) 
        WHEN MKTDATA.MARKET_DAILY_SUMMARY.clos_prc IS NOT NULL 
        AND MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IS NULL THEN LEAD(MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IGNORE NULLS) OVER (ORDER 
    BY
        MKTDATA.MARKET_DAILY_SUMMARY.cusip) - LAG(MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IGNORE NULLS) OVER (ORDER 
    BY
        MKTDATA.MARKET_DAILY_SUMMARY.cusip) 
        ELSE NULL END DIFF 
FROM
MKTDATA.MARKET_DAILY_SUMMARY 
WHERE
MKTDATA.MARKET_DAILY_SUMMARY.day_sum_eff >= '1-JUN-2017' 
AND MKTDATA.MARKET_DAILY_SUMMARY.day_sum_eff <= '10-JUN-2017') 
ORDER BY
day_sum_eff_,
fmr_iss_cusip OFFSET 0 ROWS FETCH NEXT 3 ROW ONLY

Related Articles



* original question posted on StackOverflow here.