Performance of select statement

[Former Member]

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

(lt_v_where_clauses)

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".

Please help.

Thanks & Regards,

Ravi.

[Former Member]

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

(lt_v_where_clauses)

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".

Please help.

Thanks & Regards,

Ravi.

[Former Member]

Hi Ravikiran,

An BW extractor must of time can't run in dialog so the timeout may be a normal behavior for dialog if the dynamic parts of your query provoke this.

About the SQL, this is a standard one? From which extractor?

How many records have one table pvwty?

Are you experimenting it on RSA3? Try filter with some VERSIONGUID.

To analyse what can be optimized on this access further information is required, not only about how many fields but what kind of selection you will do (filter), but saying again this timeout can be normal for your case.

Kind regards, Fernando Da Ros

[Former Member]

Hi Fernando,

I appreciate your response.

Here are my comments

About the SQL, this is a standard one? From which extractor? --> This is a standard extractor 0AU_WTY_ITEM

How many records have one table pvwty? --> no. of records currently present in our production system in table PVWTY = 52.964.952

Are you experimenting it on RSA3? Try filter with some VERSIONGUID. --> I created a generic extractor replicating the standard extractor; I made Internal Number of iPPE Node (PVWTY-VERSION_GUID) and Repair date (PNWTYH-REFDT) as selection fields.

I also tried replacing the select join with a view by selecting all the possible fields and conditions

OPEN CURSOR WITH HOLD g_cursor FOR
      SELECT (lt_fields)
                FROM
                     zwtybw_item
                WHERE version_guid IN l_r_guid AND
                       refdt       IN l_r_hdate AND
                      timstmp > lv_timestamp
                 ORDER BY timstmp ascending.
    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.

I'm able to run the extractor successfully with a give range of GUIDs and repair date - 1 day (say 10.10.2007)

if I give a range for repair date (even for 2 days 10.10.2007 to 11.10.2007) it is giving me a short dump saying "time out" - both on R/3 side and BW side.

You can check my previous questions posted on SDN - but no replies

On analysis - I came to know that the problem is with fetching the records using this selecti statement.

Please help me in improving the performance of this generic extractor.

My requirement is to extract historical data from 2009 till date. atleast with a date range of 1 Month (say from 01.01.2009 - 31.01.2009; 01.02.2009 - 28.02.2009.... 01.04.2010 - 30.04.2010.

Let me know if any other information I have to provide.

Please provide me your contact so that we can check this issue together.

I appreciate your help in this regard.

Thanks,

Ravikiran.

[Former Member]

WHERE
(lt_v_where_clauses)

Please ask an answerable question!Locked.

Rob