There is table which contains 200 rows where as the (RR/Count = Reads pr. Row) is 20000000. This is causing a system performance issue?
Do any one have any suggestion how this frequencey can be lowered?
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
Can you explain the problem more? You are saying that there are only 200 rows, but the reads/row = 20,000,000? IF that is the case it sounds like the process used for reading is horribly inefficient...
This is my understanding.
You have a table to read which has 200 rows. And, you will be reading each of these rows 20,000,000 times. This will slow down the execution.
This is my solution.
We need to reduce the number of database access.
You may read the 200 rows only once and put them into an internal table.
Read each row 20,000,000 times from the internal table.
Thank u all for your reply. Please see the query below.
SELECT
T2.ERROR_CODE, T3.FILEREF, T3.TRANS_NO, T4.SEGMENT_NO, T4.SEGMENT_ID, T4.SEGMENT_VALUE, T2.NO_OF_RETRIES, T3.MARK
INTO
:ERR_CODE, :FILEREF, :TRANS_NO, :SEG_NO, :SEG_ID, :SEG_VALUE, :NO_OF_RETRIES, :MARK
FROM
EDI.FILESTATUSTEXT T1
INNER JOIN MAL.PARK_ERR_INFO T2 ON
CHAR(T2.ERROR_CODE) = SUBSTR(T1.STATUSKEY,9,4)
INNER JOIN MAL.PARK_MAIN_INFO T3 ON T2.FILEREF = T3.FILEREF AND T2.TRANS_NO_MAIN = T3.TRANS_NO
INNER JOIN MAL.PARK_SEG_INFO T4 ON T2.FILEREF = T4.FILEREF AND T2.TRANS_NO_MAIN = T4.TRANS_NO_MAIN AND T2.SEGMENT_NO = T4.SEGMENT_NO
WHERE T1.LOADABLE = 'Y' AND T2.NO_OF_RETRIES < T1.MAX_TRIES AND T3.MARK = ' '
***************
I am not sure if something like SUBSTR(T1.STATUSKEY, 9, 4) is indexable...
I think that the clause mentioned below is responsible for low system
performance.
INNER JOIN MAL.PARK_ERR_INFO T2 ON
CHAR(T2.ERROR_CODE) = SUBSTR(T1.STATUSKEY,9,4)
I feel that the possible solution to this problem is to - Split the query into two parts.
1. To get the loadable errors from EDI.FILESTATUSTEXT
2. Search for the each error code in PARK_ERR_INFO.
I am not sure if something like SUBSTR(T1.STATUSKEY, 9, 4) is indexable...
I think that the clause mentioned below is responsible for low system
performance.
INNER JOIN MAL.PARK_ERR_INFO T2 ON
CHAR(T2.ERROR_CODE) = SUBSTR(T1.STATUSKEY,9,4)
CHAR() and SUBSTR() are stage 2 predicates thus they're not indexable. If you need to use SUBSTR to get meaningful data, then it's not normalized. And in this particular case, normalization will do you good. You can try to breakedown the STATUSKEY field into distinct columns and create a index for the error code part. Any data conversion function is a stage 2 predicate, don't use them if you need index access.
Also, you don't need to guess where's the problem in your query. Re-write your SQL from the ground up, putting one predicate at a time and see what's going on. That way you can pinpoint the issues.
