1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Instead of trigger takes ages for UPDATE

Discussion in 'SQL PL/SQL' started by many_yammy, Sep 6, 2014.

  1. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi all,

    I had already once asked about my problem but I still need your help.
    I have two tables one called "CAFM_FACADE_ELEMENT" with the sets of attributes regarding buildings and one table named "SURFACE_AREA" which should be populated in run time from the first table.
    The "SURFACE_AREA" table is an aggregation of the table "CAFM_FACADE_ELEMENT". So whatever happens in the "CAFM_FACADE_ELEMENT" the "SURFACE_AREA" should be updated.

    "CAFM_FACADE_ELEMENT"
    Code (SQL):

    CREATE TABLE CAFM_FACADE_ELEMENT_m
       (    "ID" NUMBER NOT NULL ENABLE,
        "FK_CAFM_FACADE" NUMBER NOT NULL ENABLE,
        "STOREY" VARCHAR2(40 BYTE),
        "FK_FACADE_ELEMENT_TYPE" NUMBER,
        "FK_OUTSIDE_MATERIAL" NUMBER,
        "FK_OUTSIDE_HEIGHT_CLASS" NUMBER,
        "OUTSIDE_SURFACE_AREA" NUMBER(10,3),
        "OUTSIDE_SURFACE_AREA_ACCURACY" NUMBER(*,2),
        "FK_INSIDE_MATERIAL" NUMBER,
        "FK_INSIDE_HEIGHT_CLASS" NUMBER,
        "INSIDE_SURFACE_AREA" NUMBER(10,3) DEFAULT 0,
        "INSIDE_SURFACE_AREA_ACCURACY" NUMBER(*,2),
        "FK_ACCESSIBILITY" NUMBER,
        "SECURITY_AREA" NUMBER(1,0),
         CONSTRAINT "CAFM_FACADE_ELEMENT_PK" PRIMARY KEY ("ID"));
     
    "SURFACE_AREA"
    Code (SQL):


      CREATE TABLE SURFACE_AREA
       (    "ID" NUMBER NOT NULL ENABLE,
        "FK_CAFM_BUILDING" NUMBER,
        "FK_CAFM_FACADE" NUMBER,
        "FK_FACADE_ELEMENT_TYPE" NUMBER,
        "FK_MATERIAL" NUMBER NOT NULL ENABLE,
        "FK_HEIGHT_CLASS" NUMBER NOT NULL ENABLE,
        "SURFACE_AREA" NUMBER(10,3) NOT NULL ENABLE,
        "SURFACE_AREA_ACCURACY" NUMBER(*,2),
         CONSTRAINT "SURFACEAREA_PK" PRIMARY KEY ("ID"));
     
    I wrote a trigger to do this when any change happens in the first table (insert/delete/update).
    Because there is no unique id in the "CAFM_FACADE_ELEMENT" whenever this table is updated(insert/update/delete) I need to delete the data in the table "SURFACE_AREA" and again populate it.

    Trigger:
    Code (SQL):

    CREATE OR REPLACE TRIGGER AGG_TO_SURFACE_AREA_INSERT
    INSTEAD OF INSERT OR UPDATE ON VIEW_CAFM_FACADE_ELEMENT


    DECLARE

      BUILDING_ID NUMBER;
      FACADE_ID NUMBER;
      ELEMENT_TYPE NUMBER;
      MATERIAL NUMBER;
      HEIGHT_CLASS NUMBER;
      AREA_ACCUR NUMBER;
      SURFACE_AREA NUMBER(10,3);
      SEQ_ID NUMBER;
     
      CURSOR AGG_
      IS
      SELECT AO, AA, AB, A, B, BO, SUM(SUMA)
      FROM (
      SELECT f.FK_CAFM_BUILDING AS AO,
        f.ID AS AA,
        fe.FK_FACADE_ELEMENT_TYPE AS AB,
        fe.FK_OUTSIDE_MATERIAL AS A,
        fe.FK_OUTSIDE_HEIGHT_CLASS AS B,
        fe.OUTSIDE_SURFACE_AREA_ACCURACY AS BO,
        SUM(fe.OUTSIDE_SURFACE_AREA) AS SUMA
      FROM VIEW_CAFM_FACADE_ELEMENT fe, CAFM_FACADE f
      WHERE fe.FK_CAFM_FACADE = f.ID
      GROUP BY f.FK_CAFM_BUILDING,
        f.ID,
        fe.FK_FACADE_ELEMENT_TYPE,
        fe.FK_OUTSIDE_MATERIAL,
        fe.FK_OUTSIDE_HEIGHT_CLASS,
        fe.OUTSIDE_SURFACE_AREA_ACCURACY

      UNION ALL
     
      SELECT f.FK_CAFM_BUILDING AS AO,
        f.ID AS AA,
        fe.FK_FACADE_ELEMENT_TYPE AS AB,
        fe.FK_INSIDE_MATERIAL AS A,
        fe.FK_INSIDE_HEIGHT_CLASS AS B,
        fe.INSIDE_SURFACE_AREA_ACCURACY AS BO,
        SUM(fe.INSIDE_SURFACE_AREA) AS SUMA
      FROM VIEW_CAFM_FACADE_ELEMENT fe,
        CAFM_FACADE f
      WHERE fe.FK_CAFM_FACADE = f.ID
        AND fe.FK_INSIDE_HEIGHT_CLASS != 0
      GROUP BY f.FK_CAFM_BUILDING,
        f.ID,
        fe.FK_FACADE_ELEMENT_TYPE,
        fe.FK_INSIDE_MATERIAL,
        fe.FK_INSIDE_HEIGHT_CLASS,
        fe.INSIDE_SURFACE_AREA_ACCURACY
      )S
      GROUP BY AO, AA, AB, A, B, BO;
     
    BEGIN
       DELETE surface_area;

         
                         
      OPEN AGG_;
      LOOP
        FETCH AGG_ INTO
        BUILDING_ID,
        FACADE_ID,
        ELEMENT_TYPE,
        MATERIAL,
        HEIGHT_CLASS,
        AREA_ACCUR,
        SURFACE_AREA;
        EXIT WHEN AGG_%notfound;
       
         SELECT (NVL(MAX(ID ),0)+1) INTO SEQ_ID FROM SURFACE_AREA;
       
      INSERT INTO SURFACE_AREA
      (ID,
      FK_CAFM_BUILDING,
      FK_CAFM_FACADE,
      FK_FACADE_ELEMENT_TYPE,
      FK_MATERIAL,
      FK_HEIGHT_CLASS,
      SURFACE_AREA_ACCURACY,
      SURFACE_AREA)
       VALUES (SEQ_ID , BUILDING_ID, FACADE_ID, ELEMENT_TYPE, MATERIAL, HEIGHT_CLASS, AREA_ACCUR, SURFACE_AREA);
     

      END LOOP;
      CLOSE AGG_;
     
    END;
     
    Because of mutating error I created a view of "CAFM_FACADE_ELEMENT" and applied INSTEAD OF TRIGGER.
    I have a set of problems:
    1) Is there any solution to avoid deleting the content of "SURFACE_AREA" table?

    2) I am quite new in PL/SQL and I think the way I wrote the trigger is inefficient. Has anybody idea to make it in the right way?

    3) I am using the software FME for importing the data to my database. At the first time the insert process is fast and there is no problem. but since I need to update the data It takes ages to complete the process. I am sure the problem is because of the trigger. Could you please help me to solve the problem?

    Thank you in advance
    Many
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You give very few details; yes, a trigger can increase processing time but so can not having proper indexes on the source table. What indexes are present on CAFM_FACADE_ELEMENT? Have you generated an execution plan for the updates? If so please post this plan.
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Well if you continue with your current logic, one way to immediately and easily improve the performance would be to replace the line that DELETEs the records from surface_area with a TRUNCATE statement. That would at least reduce the time taken to clear the existing data in your second table. That said, rebuilding the entire table every time one record is updated in another is never going to be truly efficient.

    From your code, it appears that the two tables are related by CAFM_FACADE.ID = SURFACE_AREA.FK_CAFM_FACADE. It would seem possible to delete and rebuild only the records from SURFACE_AREA whose FK_CAFM_FACADE value match the ID of the record that was inserted/updated.
     
  4. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Thank you for reply.

    Actually I am wondering to know if the trigger is programmed correctly or not?! Because already had a discussion here that my trigger shouldn't work but it seems working.

    Code (SQL):

    CREATE INDEX "CAFM_FACADE_ELEMENT_INDEX1" ON "CAFM_FACADE_ELEMENT" ("FK_CAFM_FACADE")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS" ;
     
    I indexed separately all columns in the CAFM_FACADE_ELEMENT as above.

    And I haven't generated any execution plan (since I didn't know about how to work with it).

    What I am thinking is instead of deleting the whole data every time I just delete the corresponding buildings which have modifications and update them again but don't know how this can be implemented.
     
  5. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Thank you Ocprep. I just replyed Zargon and now considering you also recommended me sth about deletes. I haven't thought of TRUNCATE. But I think It is impossible to have truncate statement in an INSTEAD OF trigger. isn't it?
     
  6. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    To make it clear I give an example:

    I insert a new object to the CAFM_FACADE_ELEMENT. The FACAD_ID already exists but this object has a different material. So at the moment I say delete table SURFACE_AREA and then populate it. But a better idea is to check the material, facade id, facade element type and height class in the SURFACE_AREA with new inserted object in CAFM_FACADE_ELEMENT and if there is no similar object insert it to the SURFACE_AREA or update it when it already exists. But just in this case it makes the trigger more complicated and I don't know if it necessarily improve the performance. (though I have problem to implement this idea)

    But what I cannot understand is when I insert the data for the first time the performance is promising and there is no problem. But only for update I have this terrible problem considering the fact that in both insert and update process I am primarily deleting the table.
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Yep. It is. My bad -- wasn't really thinking in terms of the imbedded COMMIT.
    That said, my real suggestion was a more targeted delete and rebuild.
     
  8. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Because your current code deletes and rebuilds the entire SURFACE_AREA table, the performance impact will depend on how large that table is. If it is a handful of rows, the impact will be minimal. If it is several hundred rows, the performance impact is likely to be noticeable. If the table is several thousand rows, you are likely to see some severe degradation in all INSERT/UPDATE operations that fire the trigger.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    Is there some good reason you 'indexed separately all columns in the CAFM_FACADE_ELEMENT as above'? Doing that causes Oracle to choose ONE index to use for a given transaction. It's not the recommended practice for index creation. It isn't difficult to generate a plan:


    SQL> set autotrace on
    SQL> [put your statement here and execute]
    SQL> set autotrace off


    You will get the execution plan for the current run of the code. Spool all of that output to a file so you can post the information here. When we can see an execution plan we can better assess what is going on and how best to address your issue.
     
  10. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Well, I think I should check again the indexing structure. As you said It just uses one index.

    Below is the explain plan:
    Dear Zargon, could you please advice me how is better to indexing my table?

    Best regards,
    Many
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, since you haven't provided any queries that you run against that table. Indexing is intended to speed access; indexing each column individually can work against you as Oracle can only choose one B-Tree index (without the use of hints) and it could very well choose the wrong index.


    Examine the queries against this table and look closely at the WHERE clauses. You need to know HOW queries are searching for data before you can effectively index that table.
     
  12. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    The problem is I am using FME software and it randomly choose a sql statement to update my table. So I don't know exactly how is the sql statement (the order of where clauses). The only thing I know is except ID, INSIDE_SURFACE_AREA and OUTSIDE_SURFACE_AREA all the columns will be updated in the case of UPDATE.

    But except indexing there is no way to optimize the trigger?

    Is the delete statement problematic? My problem is sometimes there meight be one entry inserted but should be updated into the SURFACE_AREA and therefore, I need to even delete or Update the table.