topic Re: Select statement taking much time....... in Application Development and Automation Discussions

Select statement taking much time.......

kiran_k8 — Thu, 10 Apr 2008 03:19:06 GMT

Hi,

IF NOT i601[] is initial.

select vbelv

posnv

vbeln

posnn

vbtyp_v

matnr

from vbfa into table ivbfa

FOR ALL ENTRIES IN i601

where vbeln = i601-mblnr and

posnn = i601-zeile2 and

vbtyp_v = 'J'.

select vbeln

matnr

werks

lgort

vgbel

vgpos

mwsbp

from vbrp into table ivbrp

FOR ALL ENTRIES IN ivbfa

where vgbel = ivbfa-vbelv and

vgpos = ivbfa-posnv and

vgtyp = 'J' and

werks IN werks.

CLEAR i601.

FREE i601.

ENDIF.

At the above highlighted select statement it is getting stucked up.I was not able to figure out the reason.No loops nothing but still is not moving from the second select statement,quite a long time it is taking.Can any one here throw some light.By the way none of the fields in the where clause of the 2nd select are primary keys.

Thanks,

K.Kiran.

Re: Select statement taking much time.......

Former Member — Thu, 10 Apr 2008 03:25:39 GMT

Hi,

After first select statement u need to check the table

ivbfa is initial or not.

then only u need to write the second select statement.

if u don't check the table ivbfa then the second select statement fetches all records if there are no entries in the table ivbfa.

This is also because VBRP is the item table. it contains many entries. for each header record u may have many item records so generally it takes time to fetch all the corresponding entries .

reward if helpful

raam

Re: Select statement taking much time.......

Former Member — Thu, 10 Apr 2008 03:31:14 GMT

Hi,

In second table you are tring to extract the records without passing the primary key values...

Any how you have values vbeln ,posnr in internal table i601.So pass those values to VBRP.

IF NOT i601[] is initial.

select vbelv

posnv

vbeln

posnn

vbtyp_v

matnr

from vbfa into table ivbfa

FOR ALL ENTRIES IN i601

where vbeln = i601-mblnr and

posnn = i601-zeile2 and

vbtyp_v = 'J'.

IF NOT ivbfa[] IS INITIAL

select vbeln

matnr

werks

lgort

vgbel

vgpos

mwsbp

from vbrp into table ivbrp

FOR ALL ENTRIES IN ivbfa

where *vbeln = ivbfa-vbeln AND *

posnr = ivbfa-posnv AND

vgbel = ivbfa-vbelv and

vgpos = ivbfa-posnv and

vgtyp = 'J' and

werks IN werks.

ENDIF.

CLEAR i601.

FREE i601.

ENDIF.

Now check your program.

Pls. reward if useful.....

Re: Select statement taking much time.......

Former Member — Thu, 10 Apr 2008 03:31:14 GMT

Hi,

Its always a good practice to use sy-subrc after a select statement.

So after the first statement just put the below statement,

if sy-subrc = 0. and then use the second select statement.

Hope this solves the problem.

Pls reward if useful.

Thanks,

Sirisha.

Re: Select statement taking much time.......

kiran_k8 — Thu, 10 Apr 2008 03:32:14 GMT

Hi,

IVBFA is having records but still it getting stucked up there.

K.Kiran.

Re: Select statement taking much time.......

Former Member — Thu, 10 Apr 2008 03:32:40 GMT

Hi,

In the beginning of 2nd select statement

Check

IF NOT ivbfa[] is initial.

In the where condition you have written werks IN werks.

Check That also

And if possible try to use primary key values in where condition.

Regards

Sandipan

Re: Select statement taking much time.......

Former Member — Thu, 10 Apr 2008 03:37:32 GMT

check the duplicate records

Re: Select statement taking much time.......

kiran_k8 — Thu, 10 Apr 2008 03:46:06 GMT

Murali,

VBELN and POSNR if I use in the where clause I am not getting the Sales Invoice No,so I can't use vbeln and posnr in the where clause.

As suggested I will check if ivbfa[] is not initial .

But here I am debugging in the production,ivbfa is having records,so will checking for ivbfa is not initial make any difference.

If IVBFA is not having records then it is fine,but here IVBFA is having records but still it is taking time.Should I create an index on those fields in the where clause ?

Thanks,

K.Kiran.

Re: Select statement taking much time.......

kiran_k8 — Thu, 10 Apr 2008 04:19:38 GMT

Hi,

Kindly Opine.

Thanks,

K.Kiran.

Re: Select statement taking much time.......

Former Member — Thu, 10 Apr 2008 05:01:28 GMT

You can try to use Inner Join for this query like,

IF NOT i601[] IS INITIAL.

SELECT

vbfa~vbelv

vbfa~posnv

vbfa~vbeln

vbfa~posnn

vbfa~vbtyp_v

vbfa~matnr

vbrp~werks

vbrp~lgort

vbrp~vgbel

vbrp~vgpos

vbrp~mwsbp

INTO corresponding fields of table TABLE <itab>

FROM vbfa

INNER JOIN vbrp

ON vbfa_{vbelv = vbrp}vgbel AND

vbfa_{posnv = vbrp}vgpos

FOR ALL entries IN i601

WHERE vbfa~vbeln = i601-mblnr AND

vbfa~posnn = i601-zeile2 AND

where VBRP~VBELN = IVBFA-VBELN AND

vbrp~vgtyp = 'J'.

<itab> is an internal table which contains all the(required) fields of both vbrp and vbfa tables.

Re: Select statement taking much time.......

Former Member — Thu, 10 Apr 2008 07:04:04 GMT

hi kiran,

sort i601 by mblnr zeile2.

IF NOT i601[] is initial.

select vbelv

posnv

vbeln

posnn

vbtyp_v

matnr

from vbfa into table ivbfa

FOR ALL ENTRIES IN i601

where vbeln = i601-mblnr and

posnn = i601-zeile2 and

vbtyp_v = 'J'.

endif.

sort ivbfa by vbelv posnv werks.

if not ivbfa[] is initial.

select vbeln

matnr

werks

lgort

vgbel

vgpos

mwsbp

from vbrp into table ivbrp

FOR ALL ENTRIES IN ivbfa

where vgbel = ivbfa-vbelv and

vgpos = ivbfa-posnv and

vgtyp = 'J' and

werks IN werks.

endif.

CLEAR i601.

FREE i601.

regards,

ravi shankar reddy