performance issue in peoplesoft Open Item Reconciliation process

Question

performance issue in peoplesoft Open Item Reconciliation process

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First, I reformatted your code. I was able to determine that you are joining the two tables on 24 columns. Not really having time to work through the DDL you did post I have a simple question you should be able to answer, are all the columns in the join using the same data types? I am assuming so as I didn't see in implicit data conversions in the estimated execution plan.

Here is the reformatted code:

SELECT

A.LEDGER,

A.ACCOUNT,

A.ALTACCT,

A.DEPTID,

A.OPERATING_UNIT,

A.PRODUCT,

A.FUND_CODE,

A.CLASS_FLD,

A.PROGRAM_CODE,

A.BUDGET_REF,

A.AFFILIATE,

A.AFFILIATE_INTRA1,

A.AFFILIATE_INTRA2,

A.CHARTFIELD1,

A.CHARTFIELD2,

A.CHARTFIELD3,

A.PROJECT_ID,

A.BOOK_CODE,

A.GL_ADJUST_TYPE,

A.CURRENCY_CD,

A.DATE_CODE,

A.FOREIGN_CURRENCY ,

SUM(A.FOREIGN_AMOUNT) ,

A.OPEN_ITEM_KEY,

(CONVERT(CHAR(10),MAX(A.JOURNAL_DATE),121)) ,

SUM(A.MONETARY_AMOUNT)

FROM

PS_OPEN_ITEM_GL_bkp A

inner join PS_OPEN_ITEM_TAO4_bkp339870 T

on ( A.BUSINESS_UNIT = T.BUSINESS_UNIT

AND A.LEDGER = T.LEDGER

AND A.OPEN_ITEM_KEY = T.OPEN_ITEM_KEY

AND A.ACCOUNT = T.ACCOUNT

AND A.ALTACCT = T.ALTACCT

AND A.DEPTID = T.DEPTID

AND A.OPERATING_UNIT = T.OPERATING_UNIT

AND A.PRODUCT = T.PRODUCT

AND A.FUND_CODE = T.FUND_CODE

AND A.CLASS_FLD = T.CLASS_FLD

AND A.PROGRAM_CODE = T.PROGRAM_CODE

AND A.BUDGET_REF = T.BUDGET_REF

AND A.AFFILIATE = T.AFFILIATE

AND A.AFFILIATE_INTRA1 = T.AFFILIATE_INTRA1

AND A.AFFILIATE_INTRA2 = T.AFFILIATE_INTRA2

AND A.CHARTFIELD1 = T.CHARTFIELD1

AND A.CHARTFIELD2 = T.CHARTFIELD2

AND A.CHARTFIELD3 = T.CHARTFIELD3

AND A.PROJECT_ID = T.PROJECT_ID

AND A.BOOK_CODE = T.BOOK_CODE

AND A.GL_ADJUST_TYPE = T.GL_ADJUST_TYPE

AND A.CURRENCY_CD = T.CURRENCY_CD

AND A.DATE_CODE = T.DATE_CODE

AND A.FOREIGN_CURRENCY = T.FOREIGN_CURRENCY)

WHERE

A.OPEN_ITEM_STATUS ='O'

AND T.PROCESS_INSTANCE = CONVERT(DECIMAL(10,0),339870)

GROUP BY