topic Re: optimising the below code in Application Development and Automation Discussions

optimising the below code

Former Member — Fri, 12 Sep 2008 10:01:38 GMT

Hi,

How to optimise this code as it giving performance problem..

SELECT * FROM ZZFIELDS WHERE SAP_OBJECT = ZZTOA-SAP_OBJECT AND

AR_OBJECT = ZZTOA-AR_OBJECT

ORDER BY FID.

HITCOUNT = 1.

IF DEBUG_FLAG = 'X'.

ULINE.

ENDIF.

Verarbeitung ist unterschiedlich, je nachdem ob die Daten aus
einer ArchiveLink Tabelle kommen, aus der Anwendungstabelle oder
einer Subtabelle

CASE ZZFIELDS-SUBTAB.

Daten stammen aus der ArchiveLink Tabelle (z.B. toa01)

WHEN 'ARCHLINK'.

IF DEBUG_FLAG = 'X'.

WRITE: / ZZFIELDS-FID, 'Suche nach Feld'(014),

ZZFIELDS-FNAME COLOR COL_TOTAL,

'in Tabelle'(015), ZZTOA-TABNAME COLOR COL_TOTAL.

ENDIF.

CONCATENATE 'ZZTOA-' ZZFIELDS-FNAME INTO TEMPSTRING.

FIELD-SYMBOLS TO BUFFER.

PERFORM INSERT_DATA USING 'ARCHLINK'

BUFFER

'1'.

Daten stammen direkt aus der Anwendungstabelle

WHEN ''.

IF DEBUG_FLAG = 'X'.

WRITE: / ZZFIELDS-FID, 'Suche nach Feld'(014),

ZZFIELDS-FNAME COLOR COL_TOTAL,

'in Tabelle'(015), SAP_TABELLE COLOR COL_TOTAL.

ENDIF.

Daten holen

PERFORM GET_DATA TABLES QUERYTAB

USING SAP_TABELLE

ZZFIELDS-FNAME

CHANGING RC.

Fehler, wenn kein Eintrag gefunden

IF RC <> 0.

IF SY-BATCH = 'X'.

MESSAGE I020

WITH ZZTOA-OBJECT_ID.

ELSE.

FORMAT COLOR COL_NEGATIVE.

WRITE: / 'Das Objekt '(016), ZZTOA-OBJECT_ID,

'wurde nicht in der Datenbanktabelle gefunden'(017).

FORMAT COLOR OFF.

ENDIF.

ERROR = 3.

EXIT.

ENDIF.

Daten stammen aus einer Subtabelle

WHEN OTHERS.

IF DEBUG_FLAG = 'X'.

WRITE: / ZZFIELDS-FID, 'Suche nach Feld'(014),

ZZFIELDS-FNAME COLOR COL_TOTAL,

'in Tabelle'(015), ZZFIELDS-SUBTAB COLOR COL_TOTAL.

ENDIF.

Querystring für Zugriff auf Subtabelle zusammensetzen

PERFORM GET_QUERY_SUBTAB TABLES QUERYTAB2

CHANGING RC.

Wenn die Where-Bedingung erfolgreich zusammengesetzt wurde,
koennen wir die Werte holen

IF RC = 0.

PERFORM GET_DATA TABLES QUERYTAB2

USING ZZFIELDS-SUBTAB

ZZFIELDS-FNAME

CHANGING RC.

ENDIF.

Wenn in der Subtabelle nichts gefunden wurde, oder nicht
auf die Subtabelle zugegriffen werden kann, können wir
entweder stoppen oder weitermachen (mit leerem Feld)

IF RC <> 0.

IF FORCE_SUB = 'X'.

IF DEBUG_FLAG = 'X'.

WRITE: / ZZFIELDS-FID,

'Es wurden keine Werte gefunden'(018).

ENDIF.

PERFORM INSERT_DATA USING ZZFIELDS-SUBTAB

'1'.

ELSE.

IF SY-BATCH = 'X'.

MESSAGE I013 WITH ZZFIELDS-SUBTAB.

ELSE.

FORMAT COLOR COL_NEGATIVE.

WRITE: / 'Für Beleg '(019), ZZTOA-OBJECT_ID,

'wurde in der Datenbanktabelle'(020),

ZZFIELDS-SUBTAB,

'kein passender Eintrag gefunden'(021).

ENDIF.

FORMAT COLOR OFF.

ERROR = 5.

EXIT.

ENDIF.

ENDCASE.

Wenn Fehler gesetzt wurde, können wir abbrechen

IF ERROR <> 0.

EXIT.

ENDIF.

ENDSELECT. " ZZFIELDS

Thnaks,

Babu.

Re: optimising the below code

Former Member — Fri, 12 Sep 2008 10:08:11 GMT

Hi Rambabu ,

I have 2 points to mention here :

1. Select ... Endselect --> Always acts like a loop .

2. In your code you have used Select * .. Instead you can mention the required fields .

Moreover , If there is a Sy-Subrc check after the Select statement , the further processing will not happen unnecessarily if the select fails.

Hope this will help you.

Regards,

Chitra

Re: optimising the below code

valter_oliveira — Fri, 12 Sep 2008 10:11:07 GMT

Without the code of forms GET_DATA, GET_QUERY_SUBTAB and INSERT_DATA we cannot help you

Regards.

Valter Oliveira.

Re: optimising the below code

Former Member — Fri, 12 Sep 2008 10:40:31 GMT

Hi,

Following points you can consider while performance tuning:

1. Remove SELECT ... ENDSELECT.

2. Remove ORDER BY from SELECT query. Instead use SORT after SELECT query.

3. For dependent data use SELECT query with FOR ALL ENTRIES.

4. Use combination of LOOP and READ statements.....

As per your code, these points are sufficient for help.

Regards,

Kunjal

Re: optimising the below code

Former Member — Fri, 12 Sep 2008 12:57:59 GMT

Hi,

Following points you can consider while performance tuning:

1. Remove SELECT ... ENDSELECT.

2. Remove ORDER BY from SELECT query. Instead use SORT after SELECT query.

3. For dependent data use SELECT query with FOR ALL ENTRIES. Only when the first table has entries, in other case you'll get a dump

4. Use sort tables with index. The reading is more faster

5. The tables must have the minimum size posible. Define your own tables with the fields needed

As per your code, these points are sufficient for help.

Regards,

Jorge