Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-10-14 : 15:44:27
|
| Hi,Can someone explain me this SQL? Select a row from PAY_OTH_EARNS with a specific company, paygroup, offcycle and pay end date which is not in PAY_EARNINGS, right? Can this be written in a different way? Can I build an index on any of these tables to improve the retrival time?-------------Query Cost relative to batch - 100%-----------------------------------select * FROM PS_PAY_OTH_EARNS WHERE COMPANY='DTC'AND PAYGROUP='DWS'AND PAY_END_DT= '10/04/03'AND OFF_CYCLE= 'N'AND NOT EXISTS (SELECT 'X' FROM PS_PAY_EARNINGS B WHERE B.COMPANY=PS_PAY_OTH_EARNS.COMPANY AND B.PAYGROUP=PS_PAY_OTH_EARNS.PAYGROUP AND B.PAY_END_DT=PS_PAY_OTH_EARNS.PAY_END_DT AND B.OFF_CYCLE=PS_PAY_OTH_EARNS.OFF_CYCLE AND B.PAGE_NUM=PS_PAY_OTH_EARNS.PAGE_NUM AND B.LINE_NUM=PS_PAY_OTH_EARNS.LINE_NUM AND B.ADDL_NBR=PS_PAY_OTH_EARNS.ADDL_NBR)-----------------Index Info-------------------1--PSAPAY_OTH_EARNS --nonclustered located on PRIMARY --COMPANY, PAYGROUP, PAY_END_DT, OFF_CYCLE, PAGE_NUM, --LINE_NUM, SEPCHK, ADDL_NBR, ERNCD--2--PS_PAY_OTH_EARNS--clustered, unique located on PRIMARY --COMPANY, PAYGROUP, PAY_END_DT, OFF_CYCLE, PAGE_NUM, --LINE_NUM, ADDL_NBR, ERNCD Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-14 : 16:36:31
|
How about this: SELECT A.* FROM PS_PAY_OTH_EARNS A LEFT JOIN PS_PAY_EARNINGS B ON WHERE B.COMPANY=A.COMPANY AND B.PAYGROUP=A.PAYGROUP AND B.PAY_END_DT=A.PAY_END_DT AND B.OFF_CYCLE=A.OFF_CYCLE AND B.PAGE_NUM=A.PAGE_NUM AND B.LINE_NUM=A.LINE_NUM AND B.ADDL_NBR=A.ADDL_NBR WHERE COMPANY='DTC' AND PAYGROUP='DWS' AND PAY_END_DT= '10/04/03' AND OFF_CYCLE= 'N' AND B.COMPANY IS NULL AND B.PAYGROUP IS NULL AND B.PAY_END_DT IS NULL AND B.OFF_CYCLE IS NULL AND B.PAGE_NUM IS NULL AND B.LINE_NUM IS NULL AND B.ADDL_NBR IS NULL And create an index on the 1st 4 predicates...This is Peoplesoft isn't it?Brett8-) |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-10-14 : 18:46:21
|
| Yep! If I cannot change SQL (since it is PS's vanilla code), can I add any more indexes to make this faster?Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
|
|
|
|
|
SQL - Execution Plan