#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7

    Inefficient VIEW


    I have a view that gets used 3 times by another query and am being told that the view is inefficient.

    Any reccomendations on how I could change this?

    I noticed this view is getting the value of acad_prog three times.

    Once from ps_acad_prog when finding the MAX of effdt, again from ps_acad_prog finding the MAX of effseq, and from ps_acad_prog when find the MAX of effdt from the ps_acad_plan_tbl.


    PHP Code:
    SELECT a.emplid 
     
    a.acad_career 
     
    ,r.effdt 
     
    c.acad_plan 
     
    d.descr 
     
    a.institution 
     
    a.acad_prog 
     
    b.descr 
     
    d.degree 
     
    b.acad_group 
      FROM ps_acad_prog a 
      
    ps_acad_prog_tbl b 
      
    ps_acad_plan c 
      
    ps_acad_plan_tbl d 
      
    ps_ntsr_ref2_tbl r 
     WHERE a
    .emplid c.emplid 
       
    AND a.institution=b.institution 
       
    AND a.institution=d.institution 
       
    AND a.acad_career=b.acad_career 
       
    AND a.acad_career=c.acad_career 
       
    AND a.acad_prog=b.acad_prog 
       
    AND a.acad_prog=d.acad_prog 
       
    AND c.acad_plan=d.acad_plan 
       
    AND a.effdt c.effdt 
       
    AND a.effseq=c.effseq 
       
    AND r.emplid=a.emplid 
       
    AND r.acad_career=a.acad_career 
       
    AND r.institution=a.institution 
       
    AND a.effdt = ( 
     
    SELECT MAX(prg.effdt
      
    FROM ps_acad_prog prg 
     WHERE prg
    .emplid a.emplid 
       
    AND prg.acad_career a.acad_career 
       
    AND prg.stdnt_car_nbr a.stdnt_car_nbr 
       
    AND (prg.acad_prog BETWEEN '200' AND '309' 
        
    OR prg.acad_prog '350' 
        
    OR prg.acad_prog BETWEEN '401' AND '457'
       AND 
    prg.effdt <= SYSDATE 
       AND 
    a.effseq = ( 
     
    SELECT MAX(prg1.effseq
      
    FROM ps_acad_prog prg1 
     WHERE prg1
    .emplid a.emplid 
       
    AND prg1.acad_career a.acad_career 
       
    AND prg1.stdnt_car_nbr a.stdnt_car_nbr 
       
    AND Prg1.effdt a.effdt 
       
    AND (prg1.acad_prog BETWEEN '200' AND '309' 
        
    OR prg1.acad_prog '350' 
        
    OR prg1.acad_prog BETWEEN '401' AND '457')) 
       AND 
    a.acad_career = ( 
     
    SELECT MIN(ac.acad_career
      
    FROM ps_acad_prog ac 
     WHERE ac
    .emplid a.emplid 
       
    AND ac.institution a.institution
       AND 
    b.effdt=( 
     
    SELECT MAX(ed.effdt
      
    FROM ps_acad_prog_tbl ed 
     WHERE ed
    .acad_prog b.acad_prog 
       
    AND ed.eff_status b.eff_status 
       
    AND ed.institution b.institution 
       
    AND ed.effdt <= a.effdt
       AND 
    d.effdt = ( 
     
    SELECT MAX(ed1.effdt
      
    FROM ps_acad_plan_tbl ed1 
     WHERE ed1
    .acad_plan d.acad_plan 
       
    AND ed1.eff_status d.eff_status 
       
    AND ed1.acad_prog=d.acad_prog 
       
    AND ed1.institution d.institution 
       
    AND ed1.effdt <= a.effdt
       AND 
    b.eff_status 'A' 
       
    AND b.eff_status d.eff_status 
       
    AND (a.acad_prog BETWEEN '200' AND '309' 
        
    OR a.acad_prog '350' 
        
    OR a.acad_prog BETWEEN '401' AND '457'
       AND 
    c.acad_plan NOT LIKE '%MNU' 
       
    AND a.prog_status <> 'CM' 
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    First step in analyzing performance problems: look at the execution plan.

    First step when posting here: post the table definitions including all defined indexes together with the query and the execution plan.

IMN logo majestic logo threadwatch logo seochat tools logo