5
\$\begingroup\$

I am currently trying to improve on a query that is being used to build a view. The query is in PL/SQL, in an Oracle database. There are 3 different types of reports (100,200, and 300) that are generated at each building. We track the consecutive years that each report is generated, and based on the combination of (1) the type(s) of report(s) generated for a given year and (2) the consecutive years each report has been generated, we arrive at a Result type for that building.

Here is a description of the criteria for the Result types:

Result 600 - If all 3 report types have been generated in the current year, where: Level 1: all reports were generated in 1 consecutive year (this is the first year) Level 2: at least 1 report type has been generated for 2 consecutive years (none have 3 consecutive years) Level 3: at least one report type has been generated for 3 consecutive years

Result 100 - Only report type 100 has been generated in the current year, where: Level 1 - 1 consecutive year Level 2 - 2 consecutive years Level 3 - 3 consecutive years

Result 200 - Only report type 200 has been generated in the current year, where: Level 1 - 1 consecutive year Level 2 - 2 consecutive years Level 3 - 3 consecutive years

Result 300 - Only report type 300 has been generated in the current year, where: Level 1 - 1 consecutive year Level 2 - 2 consecutive years Level 3 - 3 consecutive years

Result 400 - Only reports 100 and 200 have been generated, where: Level 1: both reports were generated in 1 consecutive year (this is the first year) Level 2: at least 1 report type has been generated for 2 consecutive years (neither have 3 consecutive years) Level 3: at least one report type has been generated for 3 consecutive years

Result 500 - Only reports 100 and 300 have been generated, where: Level 1: both reports were generated in 1 consecutive year (this is the first year) Level 2: at least 1 report type has been generated for 2 consecutive years (neither have 3 consecutive years) Level 3: at least one report type has been generated for 3 consecutive years

Result 700 - Only reports 200 and 300 have been generated, where: Level 1: both reports were generated in 1 consecutive year (this is the first year) Level 2: at least 1 report type has been generated for 2 consecutive years (neither have 3 consecutive years) Level 3: at least one report type has been generated for 3 consecutive years

Here is the current code that is used to generate this view, which is simply a display of the result:

CREATE OR REPLACE FORCE VIEW REPORTS.REPORT_RESULT_VIEW
(
   BUILDING,
   BUILDING_NAME,
   GROUP,
   YEAR,
   TYPE,
   SUBTYPE,
   CONSEC,
   RESULT
)
AS
   WITH cte1
        AS (SELECT 1 ID_100,
                   1 ID_200,
                   1 ID_300,
                   '600 Level 1' RESULT
              FROM DUAL
            UNION ALL
            SELECT 2 ID_100,
                   2 ID_200,
                   2 ID_300,
                   '600 Level 2' RESULT
              FROM DUAL
            UNION ALL
            SELECT 3 ID_100,
                   3 ID_200,
                   3 ID_300,
                   '600 Level 3' RESULT
              FROM DUAL
            UNION ALL
            SELECT 1 ID_100,
                   1 ID_200,
                   2 ID_300,
                   '600 Level 2' RESULT
              FROM DUAL),

(note - there are 63 total combinations that are listed in the actual code... I only entered the first few to give you an idea of how it is set up)

 cte2
        AS (  SELECT MAX (ID_100) ID_100_CONSEC,
                     MAX (ID_200) ID_200_CONSEC,
                     MAX (ID_300) ID_300_CONSEC,
                     YEAR,
                     BUILDING
                FROM (SELECT CONSEC ID_100,
                             NULL ID_200,
                             NULL ID_300,
                             YEAR,
                             TYPE || SUBTYPE TYPE,
                             BUILDING
                        FROM REPORT_MASTER_VIEW
                       WHERE TYPE || SUBTYPE = '100'
                      UNION
                      SELECT NULL ID_100,
                             CONSEC ID_200,
                             NULL ID_300,
                             YEAR,
                             TYPE || SUBTYPE TYPE,
                             BUILDING
                        FROM REPORT_MASTER_VIEW
                       WHERE TYPE || SUBTYPE = '200'
                      UNION
                      SELECT NULL ID_100,
                             NULL ID_200,
                             CONSEC ID_300,
                             YEAR,
                             TYPE || SUBTYPE TYPE,
                             BUILDING
                        FROM REPORT_MASTER_VIEW
                       WHERE TYPE || SUBTYPE = '300')
            GROUP BY YEAR, BUILDING),
        cte3
        AS (SELECT c2.*, c1.RESULT
              FROM    cte2 c2
                   JOIN
                      cte1 c1
                   ON     NVL (c2.ID_100_CONSEC, 0) = c1.ID_100
                      AND NVL (c2.ID_200_CONSEC, 0) = c1.ID_200
                      AND NVL (c2.ID_300_CONSEC, 0) = c1.ID_300)
     SELECT t1."BUILDING",
            t1."BUILDING_NAME",
            t1."GROUP",
            t1."YEAR",
            t1."TYPE",
            t1."SUBTYPE",
            t1."CONSEC",
            t2.RESULT
       FROM    REPORT_MASTER_VIEW t1
            JOIN
               cte3 t2
            ON t1.BUILDING  = t2.BUILDING AND t1.YEAR = t2.YEAR
      WHERE T1.TYPE IN ('100', '200' '300')
   ORDER BY t1.BUILDING;

Now, because for every report combination, it has to run through all the possible combinations, this view takes about 24 seconds to build. In the app that it is referenced in, it takes nearly a minute to load the page. For this reason, I am trying to figure out ways to make the query more efficient. At first, I was thinking of using nested CASE statements, but I wasn't really sure how that would work.

\$\endgroup\$
6
\$\begingroup\$

cte1, besides needing a better name, needs to just be a fully-fledged table. With 63 combinations, it takes a while to process how to get all these in correctly. Putting this in a view doesn't speed up that CTE at all. So, just create a permanent table in the database preloaded with these values.

That should cut a massive chunk out of your query time.

\$\endgroup\$
3
  • \$\begingroup\$ So, then a process would have to be put in place to keep the table in sync with the rest of the data. Is the performance gain worth the increased complexity? \$\endgroup\$
    – RubberDuck
    Aug 12 '14 at 3:10
  • 1
    \$\begingroup\$ I don't understand what has to be kept in sync? Am I looking at cte1 wrong? It looks like it's just a set of constant values, right? But to your question "Is the performance gain worth the increased complexity?" In this specific case since I'm certain that this is a huge chunk of the really long query time, yes. In SQL in general, if the query runs slow enough that it is noticeable to the user (anything over 1 second), any performance game that will make a noticeable difference to the end user is usually worth it. \$\endgroup\$
    – nhgrif
    Aug 12 '14 at 11:24
  • \$\begingroup\$ You're right. I should have looked closer. Everything is being selected from DUAL. It should be a table. \$\endgroup\$
    – RubberDuck
    Aug 12 '14 at 11:27
5
\$\begingroup\$

I'll compliment in saying that formatting, capitalization, etc. are consistent.

Nitpicking

cte1 and cte2 are not very good names. Aliases should reflect what they actually represent.

UNION

To me the gorilla in the room is that UNION is used 64 times to explicitly spell out every possible scenario instead of using logic. Let me illustrate.

PS: Note I'm using PL/pgSQL (a.k.a. PostgreSQL) so the syntax might need a few adjustments. I don't have access to an Oracle machine.

-- making cte1
drop table if exists numbers;
create temporary table numbers(
    counter serial,
    n int
);
insert all
into numbers (n) values (null), -- no report of any type
into numbers (n) values (1),
into numbers (n) values (2),
into numbers (n) values (3)
;
drop table if exists NumbersWithResults;
create table NumbersWithResults as
    select 
        ID_100.n as ID_100,
        ID_200.n as ID_200,
        ID_300.n as ID_300
    from numbers as ID_100
    cross join numbers as ID_200
    cross join numbers as ID_300
;
-- Query returned successfully: 64 rows affected, 17 ms execution time.
-- if you need to check results, uncomment this:
-- select * from NumbersWithResults
-- order by ID_100, ID_200, ID_300;

As you can see, execution is fast, and you just now need to work in a few logical rules to add column and update values based on a set of conditions... for example:

alter table NumbersWithResults
    add column RESULT text
;
-- working in your business rules
-- examples only, add to or change as needed
update NumbersWithResults
    set RESULT = '600 Level 1'
        where   ID_100 = ID_200 and ID_200 = ID_300,
    set RESULT = '600 Level 2'
        where   COUNT(ID_100, ID_200, ID_300) = 2,
    set RESULT = '600 Level 3'
        where   COUNT(ID_100, ID_200, ID_300) = 3
    -- etc.

Hope this helps.

PS: Like @nhgriff said, this should be a permanent table, not a CTE, if this is getting called regularly. Also consider making a stored procedure/function.

PPS: Some of my conditions at the end may be wrong... make sure you test.

\$\endgroup\$
2
  • \$\begingroup\$ Oracle doesn't support multiple rows in VALUES, the way PostgreSQL does. \$\endgroup\$ Aug 12 '14 at 2:02
  • \$\begingroup\$ I changed the insert code to factor this in, thanks @200_success. \$\endgroup\$
    – Phrancis
    Aug 12 '14 at 4:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.