CODEWITHSUNDEEP
sqloracle-databaseperformanceinner-join
Pokis
Pokis

Reputation: 35

pl/sql and msql joins, slow performance

I am trying to make a reverse calendar. That is when supplied with normal date, it could distinguish what date from the end of the month it is for example.

t_calendar is just a temporary calendar I use, that has the dates, but I want to update it and set inverse_day and inverse_week_of_month columns to the inversed dates.

I succeded to do so on MSQL with the following:

   UPDATE @t_calendar
   SET inverse_day = b.max_day_of_month - a.day + 1,
       inverse_week_of_month = ( b.max_day_of_month - a.day ) / 7  + 1
   FROM @t_calendar a
   INNER JOIN ( SELECT year, month, MAX( day ) max_day_of_month FROM @t_calendar GROUP BY year, month ) b  ON b.year = a.year AND b.month = a.month;

And I did similar in pl/sql with the following that works:

    UPDATE t_calendar a 
   SET inverse_day = customDate1.max_day - a.day + 1,
   inverse_week_of_month = TRUNC(( customDate1.max_day - a.day ) / 7)  + 1
   WHERE customDate1.year = a.year AND customDate1.month = a.month;

But it is VERY slow, that is if MSQL takes 2 second, pl/sql will take few minutes. So this is not a good solution I suppose. I tried duplicate the MSQL code on pl/sql, which doesn't seem to work in the same manner as before, as my unit tests are failing:

   UPDATE t_calendar
   SET (inverse_day, inverse_week_of_month) = (
        SELECT b.max_day_of_month - a.day + 1, TRUNC(( b.max_day_of_month - a.day ) / 7)  + 1
   FROM t_calendar a
   inner JOIN ( SELECT  year, month, MAX( day ) max_day_of_month FROM t_calendar GROUP BY year, month ) b  
   ON  b.year = a.year AND b.month = a.month
   where rownum <= 1);

What ways can I optimise this in pl/sql, as I am quite new to it or make the join work as intended.

Upvotes: 0

Views: 85

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17944

Your second and third snippets do not perform the same logic as your first snippet, I think. The equivalent to your 1st snippet in Oracle SQL (not PL/SQL, which is Oracle's procedural language) would be:

UPDATE t_calendar a
SET (inverse_day, inverse_week_of_month) = 
(  SELECT max(b.day) - a.day + 1, TRUNC(( max(b.day) - a.day ) / 7)  + 1
   FROM   t_calendar b
   WHERE  b.year = a.year
   AND    b.month = a.month
 ) u

An index on t_calendar like this one would probably help too:

CREATE INDEX ON t_calendar ( year, month, day desc )

Sorry I am not in front of an Oracle database to check for syntax errors, etc.

Upvotes: 2

Related Questions