-
Need halp analyzing the sql in a view
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'
-
repost it using the code tags and paragraphs, it's impossible to read like that
-
I am not sure what you mean by code tags but does this format help.
Thanks
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'
-
Sure you need the UNION, not a UNION ALL?
-
The union all made it alot faster. I don't know why, but thank you.
-
Because UNION is an implicit DISTINCT on the result set
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|