select query optimization

[Former Member]

Hi,

I am having a select query on LIPS table which is giving timeout error when i write using non-primary key fields. Please suggest me some way to optimize.

SELECT vbeln posnr matnr arktx abgru prodh faksp kwmeng kbmeng  
             netwr netpr charg mvgr1 mvgr2 ps_psp_pnr zzlicense FROM vbap
             INTO TABLE t_vbap FOR ALL ENTRIES IN t_prps
                               WHERE aedat IN o_aedat
                                     AND matnr IN o_matnr
                                     AND ps_psp_pnr = t_prps-pspnr.

SELECT vbeln
                posnr
                vgbel
                vgpos
           FROM lips
           INTO TABLE t_lips_hng
           FOR ALL ENTRIES IN t_vbap
           WHERE vgbel = t_vbap-vbeln
             AND vgpos = t_vbap-posnr.

There are only few records(100s) but still getting timeout dump. I tried creating secondary index on LIPS table for fields vgbel and vgpos, Still no result.

Regards,

Mahidhar.

[former_member491621]

Hi Mahidhar,

You can try using the standard view LIPS_VLPMA for you purpose.

Or else you can try creating view on these 2 tables for your requirement.

[Former Member]

Hi mahidhar,

if sy-subrc eq 0. 

1st sort the internal table by joining field.

2nd delete adjacent duplicates entries from t_vbap comparing joining field.

3rd delete t_vbap where <joining field> = space.

endif.

if t_vbap is not initial.

SELECT vbeln
                posnr
                vgbel
                vgpos
           FROM lips
           INTO TABLE t_lips_hng
           FOR ALL ENTRIES IN t_vbap
           WHERE vgbel = t_vbap-vbeln
             AND vgpos = t_vbap-posnr.

endif.

hope you dont get time out dump.......problem will be solved.

Regards

Sabyasachi

[Former Member]

Hi Mahidhar ,

Did you checked the sequence of the field as in table and while fetching.

I think there is some problem in your where clause use primary key fields as well while fetching the values.

Check the primary index for LIPS table.

sort the internal table by primary key field before using it in for all entries.

check sy-subrc as well.

Delete the duplicate values if u don't need them.

Hope this will help .....

[Former Member]

Hi Amit,

Its not the problem with the sequence of selection fields because when i run it in background its working fine..but taking lot of time.

User requirement is such that i have to use non-pri key fields.

Suggest me if you have alternate ways.

Regards,

Mahidhar.

[yuri_ziryukin]

Hello Mahidhar,

there is a standard index on the table LIPS (LIPS~VGB). This index has fields MANDT and VGBEL.

Please check if it is active in your system. If not - activate. This index is normally selective enough.

Make sure that you update statistics for table LIPS in DB20 transaction after activation of an index.

Performance of your statement also depends very much on the number of entries in the t_vbap internal table.

As already advised above, just make sure that this internal table is not empty before you do the select.

If there are only few hundreds of entries in t_vbap, then it should not take longer than a few seconds.

Regards,

  Yuri

[RaymondGiuseppi]

First read Note 185530 - Performance: Customer developments in SD. then replace access to LIPS by VGBEL = VBELN... by access first to VBFA WHERE VBELV = VBELN...  and VBTYP_N = 'J' then access to LIPS WHERE vbeln = vbfa-vbeln ... either use JOIN or FOR ALL ENTRIES to link both select statements.

Regards,

Raymond