performance tuning

UDA
N/A

performance tuning

Original:
---------
del FROM A_BCIDDB.CREDIT_ENTRIES_CT T1
WHERE EXISTS(SELECT '1' FROM
u_gisprdb.CREDIT_ENTRIES_TEMP T2
WHERE T1.MI_ACCOUNT_IDENTIFIER = T2.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT = T2.ENTRY_AMOUNT
AND ( (T1.ENTRY_SOURCE_CODE = T2.ENTRY_SOURCE_CODE
AND T1.ENTRY_CODE = T2.ENTRY_CODE
AND T1.ENTRY_DATE <= T2.ENTRY_DATE )
OR T1.ENTRY_SEQUENCE_NUMBER = T2.ENTRY_SEQUENCE_NUMBER)
)
AND T1.ENTRY_AMOUNT_CODE = 4;

I have modified the above query to the below one. but when executed they are not giving the same answer sets.i couldn't understand what
is the diff? please suggest.

Modified:
----------
del from A_BCIDDB.CREDIT_ENTRIES_CT T1
where (T1.MI_ACCOUNT_IDENTIFIER,T1.ENTRY_AMOUNT,T1.ENTRY_SOURCE_CODE,T1.ENTRY_CODE,
T1.ENTRY_DATE,T1.ENTRY_SEQUENCE_NUMBER) IN
( SELECT T2.MI_ACCOUNT_IDENTIFIER,T2.ENTRY_AMOUNT,T2.ENTRY_SOURCE_CODE,
T2.ENTRY_CODE,T2.ENTRY_DATE,T2.ENTRY_SEQUENCE_NUMBER
FROM u_gisprdb.CREDIT_ENTRIES_TEMP T2
wHERE T1.MI_ACCOUNT_IDENTIFIER = T2.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT = T2.ENTRY_AMOUNT
AND ( (T1.ENTRY_SOURCE_CODE = T2.ENTRY_SOURCE_CODE
AND T1.ENTRY_CODE = T2.ENTRY_CODE
AND T1.ENTRY_DATE <= T2.ENTRY_DATE )
OR T1.ENTRY_SEQUENCE_NUMBER = T2.ENTRY_SEQUENCE_NUMBER)
)
AND T1.ENTRY_AMOUNT_CODE = 4;

Thanks in advance:)
1 REPLY

Re: performance tuning

I guess, the 2nd SQL statement is deleting less number of rows, beacuse both the statements are not logically equivalent. Try this

del from A_BCIDDB.CREDIT_ENTRIES_CT T1
where (T1.MI_ACCOUNT_IDENTIFIER,
T1.ENTRY_AMOUNT /*,
T1.ENTRY_SOURCE_CODE,
T1.ENTRY_CODE,
T1.ENTRY_DATE,
T1.ENTRY_SEQUENCE_NUMBER */) IN
(SELECT
T2.MI_ACCOUNT_IDENTIFIER,
T2.ENTRY_AMOUNT /*,
T2.ENTRY_SOURCE_CODE,
T2.ENTRY_CODE,
T2.ENTRY_DATE,
T2.ENTRY_SEQUENCE_NUMBER */
FROM u_gisprdb.CREDIT_ENTRIES_TEMP T2
wHERE T1.MI_ACCOUNT_IDENTIFIER = T2.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT = T2.ENTRY_AMOUNT
AND ((T1.ENTRY_SOURCE_CODE = T2.ENTRY_SOURCE_CODE
AND T1.ENTRY_CODE = T2.ENTRY_CODE
AND T1.ENTRY_DATE <= T2.ENTRY_DATE)
OR T1.ENTRY_SEQUENCE_NUMBER = T2.ENTRY_SEQUENCE_NUMBER)
)
AND T1.ENTRY_AMOUNT_CODE = 4;