Issue in Select Statement

[Former Member]

Dear gurus

Refer to the below code. my query takes too much time to execute can it work more faster.

SELECT *
  FROM bseg
  INTO bseg
  WHERE belnr = doc_no
  AND buzei EQ '002'
  AND gjahr EQ gjahr.
  ENDSELECT.

  SELECT * FROM payr
    INTO payr
    WHERE vblnr EQ doc_no
    AND gjahr EQ gjahr .
  ENDSELECT.

  SELECT * FROM reguh
  INTO reguh
  WHERE vblnr EQ doc_no
     AND laufd EQ payr-laufd.
  ENDSELECT.

  SELECT SINGLE adrnr INTO adrnr
  FROM lfa1
  WHERE lifnr = reguh-lifnr.

  SELECT SINGLE street INTO zcheque_struct-stras
    FROM adrc
    WHERE addrnumber = adrnr.

SELECT SINGLE landx INTO zcheque_struct-land1
  FROM t005t
  WHERE
  land1 = reguh-land1
  AND spras = 'EN'.

  SELECT SINGLE budat INTO post_date FROM bkpf
   WHERE belnr = doc_no AND gjahr EQ gjahr.

  SELECT wrbtr qbshb rebzg belnr shkzg gjahr   "Problem Comes Here it take more then 3 min's to get details"
    INTO TABLE document_tab
    FROM bseg
    WHERE augbl = payr-vblnr
    AND  belnr NE payr-vblnr AND
     augdt EQ post_date.

[Former Member]

Hi,

Try to use all key fields in where clause in select statement.

BSEG is a cluster table and generally contains bulk data.

So I think 3 minutes is not a big issue because of bulk data.

Also check the data once in the table which you are fetching.

Thanks,

Sudheer

[Former Member]

Hi,

In your select query you are not using key fields of BSEG table,

SELECT wrbtr qbshb rebzg belnr shkzg gjahr   "Problem Comes Here it take more then 3 min's to get details"
    INTO TABLE document_tab
    FROM bseg
    WHERE augbl = payr-vblnr
    AND  belnr NE payr-vblnr AND
     augdt EQ post_date.

BUKRS, BELNR, GJAHR, BUZEI are the key fields of BSEG table. You are using only belnr to fetch data from BSEG, check if you can add more key fields in where condition.

Thanks,

Archana

[Former Member]

Hi,

Try to avoid the select from cluster table BSEG. if there is no other alternate then try to use all the key fields and also the order of the fileds should sequence comparing from BSEG table and also where condition should be same in the sequence as exist in the BSEG table.

The select query should be

SELECT belnr gjahr shkzg wrbtr qbshb rebzg "Problem Comes Here it take more then 3 min's to get details"

INTO TABLE document_tab

FROM bseg

WHERE belnr NE payr-vblnr and

augbl = payr-vblnr AND

augdt EQ post_date.

[SuhaSaha]

Hello Guys,

I have a problem on these statements & do not agree with you on this:

Try to avoid the select from cluster table BSEG.

How does selecting data from a cluster table effect your performance if you use all the key fields or atleast use an indes as a matter of fact?

if there is no other alternate then try to use all the key fields and also the order of the fileds should sequence comparing from BSEG table and also where condition should be same in the sequence as exist in the BSEG table

I think this is a fad. Although it is advisable to maintain the order of the fields (for comestic purposes only) it is not at all a MUST-DO.

Guys correct me if i am wrong.

@OP: If i am not wrong the SELECT is taking time because of the NE condition in the where clase.

A must read for all guys accessing BSEG: [/people/rob.burbank/blog/2007/11/12/quickly-retrieving-fi-document-data-from-bseg|/people/rob.burbank/blog/2007/11/12/quickly-retrieving-fi-document-data-from-bseg].

BR,

Suhas

Edited by: Suhas Saha on Feb 24, 2010 1:00 PM

[Former Member]

>

> @OP: If i am not wrong the SELECT is taking time because of the NE condition in the where clase.

Spot on!

He may either user Ranges instead of NE.

Cheers

[Former Member]

Hi,

Try to avoid Select endselect.Use internal tables.If possible specify the required field names in select statement instead of *.

It would improve the performance.

Thanks.

Ramya.

[Former Member]

The performance of the table BSEG is always slow because of large data volume.

BSEG is not a transperant table, its a cluster table and you can not create secondary indexes to improve the performance.

So better try to involve all the key fields.

[Former Member]

Hi,

I am fully agree with archana, to over come this problem just create a seconday index on BSEG table and

index it on aubel , belnr , auget

Rani.

[Former Member]

Hi,

But secondary indexes may not always help. If there are already some secondary indexes then it may create overload on database. So, always try to add key fields first, then check if we can use any other alternate table. If nothing works then go foe indexing.

Thanks,

Archana

[Former Member]

can you give me an example for indexing?

[Former Member]

hi

Please Check the below link for Index

Dont use select *

[Indexing in SAP Tables|http://wiki.sdn.sap.com/wiki/display/profile/2007/09/19/IndexinginSAP+Tables]

[mukundkansara]

Hi instead of using bseg table use BSIK,BSAK,BSID,BSAD,BSIS,BSAS table. it improve ur query speed and avoid use of

select --endselect statement Use for all entries with key fields or existing secondry Index

Edited by: Mukund Kansara on Feb 24, 2010 8:20 AM

[anup_deshmukh4]

First of all BSEG is a cluster table avoid quering on it...!

try to relate other tables....start from BKPF and you will find the link

Edited by: Anup Deshmukh on Feb 24, 2010 8:20 AM

[mukundkansara]

E.G. select bsak INTO TABLE it_bsak

WHERE belnr = doc_no

AND buzei EQ '002'

AND gjahr EQ gjahr.

select bsaD INTO TABLE it_bsad

WHERE belnr = doc_no

AND buzei EQ '002'

AND gjahr EQ gjahr.

select bsas INTO TABLE it_bsas

WHERE belnr = doc_no

AND buzei EQ '002'

AND gjahr EQ gjahr.

if it_bsak[] is NOT INITIAL.

SELECT * FROM payr

INTO it_payr

FOR ALL ENTRIES IN it_bsak

WHERE vblnr EQ it_bsak-augbl

AND gjahr EQ it_bsak-gjahr.

endif.

[Former Member]

hello,

dont select all fields from bseg means dont use select *

and the select which is taking more time there use for all entries of above table payer

and also dont use select endselect.

regards,

srinivas

[Former Member]

Thank yOu all.

[Former Member]

>

> Hi instead of using bseg table use BSIK,BSAK,BSID,BSAD,BSIS,BSAS  table. it improve ur query speed and avoid use of  
> select --endselect statement  Use for all entries with key fields or existing secondry Index

Hi Saad,

Please do not mislead the whole community by assigning wrong p-o-i-n-t-s. If in future any other aspirants come across this thread he will completely get the wrong impression soon as he will witness the above statements of Mukund. Which is incorrect.

I appeal you to please have a look again at the blog which Suhas has given to you.

Cheers

[Former Member]

Hi,

Don't use select and end select.Use internal tables.I think it improves the performance.

Thanks.

Ramya.