need to optimise the procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: need to optimise the procedure

Hybrid View

  1. #1
    Join Date
    Jan 2002
    Posts
    83

    need to optimise the procedure

    hi

    i have a proc which needs to be optimised... also i need to transfer the code into pl/sql but i am not good in using pl/sql.

    can some help me on this proc...I would like to remove the delete statemnt and use insert and update only.
    Attached Files Attached Files
    • File Type: txt 1.txt (10.6 KB, 99 views)

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Are you serious?

    You post this huge script with no descriptions and no real comments and no schema and expect someone to convert it into optimized PL/SQL it for you? Good luck with that.

    But here are a few quick pointers:

    - You have SELECTs all over the place. Why? What do you plan on doing with the output in PL/SQL?

    - Garbage like this tells me your data model is far beyond my help:
    UPDATE AGENTMASTER SET AGENCYCATEGORY_ID = 'EMP'
    where (substr(agt_id,1,1) not in ( '0', '8', '9', 'I', 'G', 'R' ) or substr(agt_id,1,2) = '02' )
    and substr(agt_id,3,1) in ( '8', '9' )
    or substr(agt_id,1,3)='002';

    Looking at substrings of values for meaning is a sure sign that your model is in bad, bad shape.

    - All your INSERT/UPDATES can be merged into only INSERTS. Whatever modifications you want to do to any inserted columns - do them in the insert! Use DECODE or CASE in the SELECT clause if the INSERT to change the value from what is in the source table to what you want it to be in the target table.

    - PL/SQL is not the problem here, and it will not be the solution. Your data-model is screwed to begin with. After that, the SQL in this script is sad and innefficient. Worry about the SQL (since the model is likely set in stone) and collapse it down until you only have INSERTs - then simply throw a BEGIN/END; around it and viola - you have PL/SQL.

    And usually, such broad requests get completely ignored. You need to be able to stand on your own somewhat.

    Good luck,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width