I am a new DBA and have a developer who created a view in our HrMS application. When he quieries the view it processes forever and never returns any results. He gave me the project of fixing his view. When I look at it the sql in there has so many where clauses with nested select statements. I ran the index tunning wizzard and implemented it's suggestions but it didn't sugest anything for his view. I wanted to plug his code in the view into SQL analyzer but my company has standard edition and I don't see it installed. I was wondering if any one could help me out. Here is the script for his view.
SELECT A.ROLEUSER , A.DESCR , A.OPRID , A.EMAILID , A.FORMID , A.WORKLIST_USER_SW , A.EMAIL_USER_SW , A.FORMS_USER_SW , A.EMPLID , A.ROLEUSER_ALT , A.ROLEUSER_SUPR , A.EFFDT_FROM , A.EFFDT_TO , B.ROWSECCLASS , ' ' FROM PS_ROLEXLATOPR A , PSOPRDEFN B WHERE
A.EMPLID = B.EMPLID AND A.OPRID = B.OPRID AND B.ROWSECCLASS NOT IN ('HCDPALL','HCDP_ALT_EE','HCDPNJ01','HCDPMA01','EPEX9000',' ','HCDP_ALT11_SLSFLD','HCDP_ALT10_ALL','HCDP_ALT11_ALL','HCDP_ALT12_ALL') AND B.EMAILID <> ' ' AND B.EMAILID <> 'PSReports@AltanaPharma-US.com' AND B.EMAILID <> 'gonowhere@altanainc.com' AND B.OPRID NOT IN ( SELECT C.OPRID FROM PS_ROLEUSER_VW C WHERE C.OPRID = B.OPRID AND ( C.ROLENAME = 'ALT_WKFLOW_HIRE_TERM' OR C.ROLENAME = 'ALT_WKFLOW_XPAY' ) ) UNION SELECT A1.ROLEUSER , A1.DESCR , A1.OPRID , A1.EMAILID , A1.FORMID , A1.WORKLIST_USER_SW , A1.EMAIL_USER_SW , A1.FORMS_USER_SW , A1.EMPLID , A1.ROLEUSER_ALT , A1.ROLEUSER_SUPR , A1.EFFDT_FROM , A1.EFFDT_TO , B1.ROWSECCLASS , C1.EMPLID FROM PS_ROLEXLATOPR A1 , PSOPRDEFN B1 , PS_EMPL_CURRJOB_VW C1 WHERE A1.EMPLID = B1.EMPLID AND A1.OPRID = B1.OPRID AND A1.EMPLID = C1.SUPERVISOR_ID AND B1.ROWSECCLASS = 'HCDP_ALT_EE' AND B1.EMAILID <> ' ' AND B1.EMAILID <> 'PSReports@AltanaPharma-US.com'