Select From BSEG

[Former Member]

Hi

The below mentioned piece of code is throwing a dump in Production system.

Is there any way of alternate selection.

IF NOT it_pos[] IS INITIAL.

SELECT * FROM bseg INTO

TABLE it_bseg

FOR ALL ENTRIES IN it_pos

WHERE bukrs IN dd_bukrs

AND belnr = it_pos-belnr

AND gjahr = it_pos-gjahr.

ENDIF.

Regards

Subin.S

[Former Member]

Hi

The below mentioned piece of code is throwing a dump in Production system.

Is there any way of alternate selection.

IF NOT it_pos[] IS INITIAL.

SELECT * FROM bseg INTO

TABLE it_bseg

FOR ALL ENTRIES IN it_pos

WHERE bukrs IN dd_bukrs

AND belnr = it_pos-belnr

AND gjahr = it_pos-gjahr.

ENDIF.

Regards

Subin.S

[Former Member]

Failure to specify the company code (BUKRS) as an equals will result in a table scan that will cause the select to time out.

You need to get the company code from wherever you are getting the document number and fiscal year into your table it_pos, and then select as follows:

IF NOT it_pos[] IS INITIAL.
SELECT * FROM bseg INTO
       TABLE it_bseg
       FOR ALL ENTRIES IN it_pos
*       WHERE bukrs IN dd_bukrs   "Replace this line
       WHERE bukrs = it_pos-bukrs "<<< Change to this
         AND belnr = it_pos-belnr
         AND gjahr = it_pos-gjahr.

ENDIF.

Andrew

[Former Member]

Hi Andrew

in the above mentioned code, dd_bukrs can hadly have 2 or 3 entries.

In an ideal case 1 entry.

Your suggestion would sound well, if the it had more entries.

Is there any other way to tune it ?

Thanks and Regards

Subin.S

[Former Member]

The other change you could make that would have a major difference is to select only the fields you need, not Select *.

BSEG has several hundred fields, and you probably only use 10 or so.

It would be best if you could match company to document - I assume the same document number will not appear in multiple company codes. Having three fields all with equality tests will give the database optimiser the best chance of getting the quickest access to the records - ie keyed read rather than table scan.

Andrew

[Former Member]

Hi Andrew

I have tried by specifying the fields, now it is showing only a small difference.

The funniest thing is that, there is 2 BSEG select in my program.

If i have to make it one, the only way for me is to give only gjahr and bukrs.

Warm Regards

Subin.S

[Former Member]

Hi Subin,

BSEG is a cluster table so naturally it will have lakhs of records mostly. So whenever your are writing select statements on cluster tables you have to take care few points.

1. Use all the primary keys in your where clause.

2.Select the whatever fields you want.

3.For each posting there are two values in BSEG one is debit and another one is credit.

Otherwise it will be huge performance problem and you may landed into ABAP dumps.

Regards,

Ameer Baba.

[Former Member]

Not quite so... you can have documents with single lines (e.g. downpayment requests) and documents with no lines (e.g. clearing documents)... and you will frequently get documents with lots of lines such as journals (up to 999).

As Andrew said, if we can get a bit more info about the context of the BSEG selection and the error you are getting it will be easier to suggest options for improvement.

[Former Member]

Hi Jonathan,

Thanks for your info. What I mean selection of the records which we wanted out of large no of entries.

Regards,

Ameer Baba.

[Former Member]

Hi

IF NOT it_pos[] IS INITIAL.

SELECT * FROM bseg INTO <b>corresponding fields of</b>

TABLE it_bseg

FOR ALL ENTRIES IN it_pos

WHERE bukrs IN dd_bukrs

AND belnr = it_pos-belnr

AND gjahr = it_pos-gjahr.

ENDIF.

[Former Member]

I agree with above

do not use SELECT *

please select the proper field that u need.

[Former Member]

IF NOT it_pos[] IS INITIAL.

SELECT * FROM bseg INTO

TABLE it_bseg

FOR ALL ENTRIES IN it_pos

WHERE bukrs IN dd_bukrs

AND belnr = it_pos-belnr

AND gjahr = it_pos-gjahr.

ENDIF.

this statement is OK. Just dont use SELECT *, SEELCT the fields explicitly , in that case you will be fetching less amount of data ( considering 288 Columns of BSEG).

I guess ur getting a dump because your internal table <b>it_pos</b> contains huge number of records. With so many records when you arec doing for all entries on BSEG , its getting a time out. I will suggest you to -

1. Change the select-option dd_bukrs to a parameter and make it mandatory. In that case the users will be selecting fewer records.

2. Or you can use the syntax PACKET SIZE ( of may be 5000 records) .

Cheers

SKC

[Former Member]

I think you need to post a few more details of the environment and error to assist people in offering solution.

Questions I think of are:

- What type of dump are you getting? - Out of Time? Out of Memory? Other?

- How many entries are in internal table it_pos[] before the BSEG select starts?

- How many entries are in BSEG approximately?

- Do you need all the BSEG records to be selected into an internal table at once or can you process them in smaller sets?

- Do you have a number of other large internal tables in your program? Can any of these be cleared to free up memory?

- can you run SQL trace (ST05) against the program to see the execution time for each fetch from BSEG and how many records each fetch returns?

- why are you doing two selects from BSEG? What is the difference between them? normally better to get all the fields at the same time instead of selecting twice.

- in Development or Test where there is less data, does tha program run OK? If so, can you run it in SE30 to see what that transaction highlights as performance or similar issues?

- what table or tables do you fill table it_pos[] from? are there any duplicate records in this internal table?

- what SAP version are you running? 32 bit or 64 bit? What database?

To solve an issue like this with a program these and probably dozens of other questions must be asked and answered - and as the person on the site you are the only one able to get the answers.

Posting more details will help forum readers to evaluate the issue in light of their experience and to provide further suggestions. The more information you can give - the more likely that someone will be able to answer.

thanks

Andrew