topic Issue in Select Statement in Application Development and Automation Discussions

Issue in Select Statement

Former Member — Wed, 24 Feb 2010 06:33:56 GMT

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.

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 06:40:21 GMT

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

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 06:41:29 GMT

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

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 06:43:44 GMT

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.

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 06:44:01 GMT

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.

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 06:44:32 GMT

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.

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 06:49:25 GMT

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

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 06:56:10 GMT

can you give me an example for indexing?

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 07:15:08 GMT

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]

Re: Issue in Select Statement

mukundkansara — Wed, 24 Feb 2010 07:20:16 GMT

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

Re: Issue in Select Statement

anup_deshmukh4 — Wed, 24 Feb 2010 07:20:18 GMT

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

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 07:22:24 GMT

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

Re: Issue in Select Statement

SuhaSaha — Wed, 24 Feb 2010 07:27:24 GMT

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

Re: Issue in Select Statement

mukundkansara — Wed, 24 Feb 2010 07:27:41 GMT

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.

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 08:13:36 GMT


> @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

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 08:16:44 GMT

Thank yOu all.

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 08:22:45 GMT

Hi,

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

Thanks.

Ramya.

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 08:27:59 GMT


> 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

Re: Issue in Select Statement

Former Member — Wed, 24 Feb 2010 08:30:59 GMT

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.