performance issue

[TimMuchena]

Moderator message: please use more informative subject in future

how can i improve the performance of the following select statement

g_viewname = VIAUF_AFVC

SELECT * FROM (g_viewname)

INTO CORRESPONDING FIELDS OF TABLE lt_task

FOR ALL ENTRIES IN lt_aufrueck

WHERE aufnr = lt_aufrueck-aufnr

AND rueck = lt_aufrueck-rueck

AND auart IN auart_o

AND iphas IN r_iphas

AND arbid IN arbid_o

AND werks IN werks_o

AND equnr IN equnr_o

AND tplnr IN strno_o

AND larnt IN larnt_o.

Edited by: Matt on Feb 3, 2009 9:56 AM

[Former Member]

the following things can improve performance drastically

1)avoid using select * and into corresponding fields of

instead use the select f1 f2 in to table

2) Order of where clause should be same as order in the view

change the order as

WHERE aufnr = lt_aufrueck-aufnr

AND iphas IN r_iphas

AND equnr IN equnr_o

AND auart IN auart_o

AND tplnr IN strno_o

AND arbid IN arbid_o

AND werks IN werks_o

AND larnt IN larnt_o

AND rueck = lt_aufrueck-rueck.

Edited by: Sumanth Nag Kristam on Feb 3, 2009 7:07 AM

[matt]

Please use more informative subject in future

[Former Member]

Hi

First check the table lt_aufrueck for values

Remove * and mention only the required fields

Also remove into corresponding.

mention the fields in table sequence.

Use primary keys in where condition.

if not lt_aufrueck is initial.

SELECT * FROM (g_viewname)

INTO OF TABLE lt_task

FOR ALL ENTRIES IN lt_aufrueck

WHERE aufnr = lt_aufrueck-aufnr

AND rueck = lt_aufrueck-rueck

AND auart IN auart_o

AND iphas IN r_iphas

AND arbid IN arbid_o

AND werks IN werks_o

AND equnr IN equnr_o

AND tplnr IN strno_o

AND larnt IN larnt_o.

endif.

regards

Shiva

[Former Member]

1.Instead of using the view VIAUF_AFVC(as in this SELECT u have done.. all the join conditions are not provided in the where condition) .It will be better to join the below tables(which ever is applicable), and SELECT the field (which ever is applicable) INTO TABLE It_tab.

Tables

AFIH

AUFK

AFKO

AFVC

AFVV

ILOA

Note:make sure to provide max. key fields or index fields in the where condition.

Cheers,

Mukundan.R

[Former Member]

hi...

well seeing the code i can say

1. dont use..... into corresponding fields of

2. Access only required fields in place of using * for selection

3. In where clause give the key fields first and then give other fields....and also the order of the fields should be like same as there in table for better performance

regards,

Mohit

Edited by: Mohit Kumar on Feb 3, 2009 12:57 PM

[Former Member]

Hi ,

Check if you can remove the View and use JOIN to fetch data from the repective tables and populating the fields as required in a simpler way.

ensure to utilize the maximum available key fileds to improve the performance.

Also check if the internal table conati some entries. (which is used in the for all entries)

Regards,

Radhika

[Former Member]

hi,

1.Please check lt_aufrueck is initial or not and then proceed with the selection

for example.

if lt_aufrueck[] is not initial.

here REFRESH lt_task .

write select here

endif.

2.Refresh the internal table before populating it.

REFRESH lt_task.

3.Dont use * and into corresponding.

4.Take all the primary keys when selecting fields.

thanks.

[Former Member]

Hi,

instead of using into corresponding fields which will affect performance

use field names individullay in the order as they are in int tab, also the where

condition in the same order

ie. if a int tab has fields f1 f2 f3...

then

select f1 f2 f3... from (view) into table int ..

where f1 =

and f2 = ...

Regards,

Manoj Kumar P

[Former Member]

Hi,

to my understanding i hope you are using * since you are not aware of the table from which you are fetching the records?

If Yes, Then try this way,

select fieldname from dd03l into table field_tab where tabname eq g_viewname.

Here g_viewname holds the view name that has been fetched at runtime.

field_tab declaration:

data: field_tab(000040) occurs 0 with header line.

Now to avoid Corresponding field of:

Create a Dynamic Internal table with View Structure.

We know the view name G_viewname.

DATA: xfc TYPE lvc_s_fcat,

ifc TYPE lvc_t_fcat.

CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'

EXPORTING

i_program_name = sy-repid

I_STRUCTURE_NAME = g_viewname

CHANGING

ct_fieldcat = ifc.

CALL METHOD cl_alv_table_create=>create_dynamic_table

EXPORTING

it_fieldcatalog = ifc

IMPORTING

ep_table = dreft.

data: dreft type ref to data,

drefl type ref to data.

field-symbols: <upload> type table,

<fs_worka> type any.

ASSIGN dreft->* TO <upload>.

CREATE DATA drefl LIKE LINE OF <upload>.

ASSIGN drefl->* TO <fs_worka>.

now in your query you can use as this.

SELECT (field_tab) FROM (g_viewname)

INTO TABLE <upload>

FOR ALL ENTRIES IN lt_aufrueck

WHERE aufnr = lt_aufrueck-aufnr

AND rueck = lt_aufrueck-rueck

AND auart IN auart_o

AND iphas IN r_iphas

AND arbid IN arbid_o

AND werks IN werks_o

AND equnr IN equnr_o

AND tplnr IN strno_o

AND larnt IN larnt_o.

Hope this might have helped you.

Thanks,

Prashanth

[Former Member]

Please run an SQL Trace and tell us what more about the actual problem.

How long does it run, how many records are read?

I don't see why you dynamic coding here.

Is the driver table empty, should not, how many records are in there?

Siegfried