Skip to Content
0
Former Member
May 19, 2010 at 05:40 AM

Performance of select statement with Cursors and Joins

1722 Views

Hi experts,

Please help me in improving the performance of this select statement.

Note: the minimum no of records expected in production system is 100.000

* First data package -> OPEN CURSOR
IF g_counter_datapakid = 0.

select single TIMESTAMP into (p_lv_timestamp) from wtybwc where extractor = p_extractor.

OPEN CURSOR WITH HOLD g_cursor FOR
SELECT (lt_fields)
FROM
pvwty AS p
INNER JOIN pnwtyv AS v ON v~pnguid = p~version_guid
INNER JOIN pnwtyh AS h ON h~pnguid = v~header_guid
INNER JOIN cwty001 AS c ON h~clmty = c~clmty
WHERE
( c~auto IS null OR
c~auto = space ) AND
c~recall = space AND
p~timstmp > lv_timestamp and
v~aktiv = 'X'
ORDER BY p~timstmp ASCENDING.

ENDIF. "First data package ?

FETCH NEXT CURSOR g_cursor
APPENDING CORRESPONDING FIELDS
OF TABLE lt_pvwty_t
PACKAGE SIZE l_maxsize.
IF sy-subrc 0.
CLOSE CURSOR g_cursor.
RAISE no_more_data.
ENDIF.

lt_fields - the no of fields selected is dynamically descided - currently it is fetching 69 fields

Currently when I try to execute the open cursor select statement with Joins it is giving me a short dump saying "TIMEOUT".

Let me know if any other information I have to provide - but do not lock this question.

Edited by: Ravikiran pbs on May 19, 2010 11:10 AM