topic Re: Need Help in Select Statement in Application Development and Automation Discussions

Need Help in Select Statement

Former Member — Thu, 28 May 2009 05:30:12 GMT

Dear gurus.

Below is my select statement fetching data from table BKPF and comparing it from my internal table.

Problem is that BKPF table contain more then 50,000 entries. which take my program to hang

is there any solution to help my problem.

My Program is a Print program which take two inputs

1) Document No.

2) Facial year.

SELECT belnr bldat xblnr
     FROM bkpf
    INTO TABLE zbkpf
    FOR ALL ENTRIES IN document_tab
    WHERE belnr EQ document_tab-belnr
            AND bukrs EQ 'TPF'
          and  awtyp = 'BKPF'
          AND gjahr = document_tab-gjahr.

Please Help

regards

Saad.Nisar

Re: Need Help in Select Statement

Former Member — Thu, 28 May 2009 05:36:37 GMT

Dear Nisar,

1. Try to use inner join instead of for all entries.

2. in below stmt follow key fields order i.e

SELECT belnr bldat xblnr

FROM bkpf

INTO TABLE zbkpf

FOR ALL ENTRIES IN document_tab

WHERE belnr EQ document_tab-belnr

AND gjahr = document_tab-gjahr

AND bukrs EQ 'TPF'

and awtyp = 'BKPF'.

rgds,

Kiran

Re: Need Help in Select Statement

Former Member — Thu, 28 May 2009 06:19:06 GMT

Are you sure this statement is causing an error? Well for all entries will cause performance issues. But then 50000 records to be queried. Not much I feel. Check your trace to see which statement is causing performance issues. It could be the nested loops or some bad program construct.

Also check if the table used in for all entries is not initial.


if not table[] is initial.
  select ...
  from bkpf
  for all entries in table
  where ....
endif.

Regards,

Abdullah Ismail

Edited by: ZAFCO ABAP on May 28, 2009 10:23 AM

Re: Need Help in Select Statement

Former Member — Thu, 28 May 2009 07:06:22 GMT

Hi,

For All Entries is not always better than Join.

But, I think 'For All Entries' should do for this query because the query is dealing with just one database table. As a suggestion, try sorting the internal table, remove all the duplicate entries, sort the database table and also check if the internal table is not initial before it processes the query. This will avoid execution of the query if the internal table is empty (For All Entries queries all records of the database table if the internal table is empty). On sorting, the performance could possibly tune itself decelty because the query need not scan the entire table for finding an entry.

Edited by: Nitwick on May 28, 2009 9:09 AM

Re: Need Help in Select Statement

Former Member — Thu, 28 May 2009 07:14:16 GMT

first put if document_tab not initial command,

then put some more where condition in the select table

just check secondary index in bkpf table

Re: Need Help in Select Statement

Former Member — Thu, 28 May 2009 11:23:29 GMT

Hi Saad,

Please check this code:

If sy-subrc = 0.

Sort document_tab by xblnr gjahr.

If not document_tab[] is initial.

SELECT belnr bldat xblnr

FROM bkpf

INTO TABLE zbkpf

FOR ALL ENTRIES IN document_tab

WHERE belnr EQ document_tab-belnr

AND bukrs EQ 'TPF'

and awtyp = 'BKPF'

AND gjahr = document_tab-gjahr.

endif.

Re: Need Help in Select Statement

Former Member — Thu, 28 May 2009 15:52:24 GMT

Hi Saad Nisar,

You can write the code in this way:

IF NOT document_tab[] IS INITIAL.

SORT document_tab BY BELNR

SELECT belnr bldat xblnr

FROM bkpf

INTO TABLE zbkpf

FOR ALL ENTRIES IN document_tab

WHERE belnr = document_tab-belnr

AND bukrs = 'TPF'

AND gjahr = document_tab-gjahr

AND awtyp = 'BKPF'.

ENDIF.

Hope this may some what increase the performance.

Or Try to check if there is any secondary index present.

Regards,

Prashant

Re: Need Help in Select Statement

Jelena_Perfiljeva — Thu, 28 May 2009 16:26:06 GMT

As correctly suggested above, always check if the table is not initial. Otherwise it will cause the full table scan and the program will run forever.

Additionally, I would recommend using the same order in the SELECT statement as in the primary key of the table, like this:

SELECT belnr bldat xblnr
FROM bkpf 
INTO TABLE zbkpf
FOR ALL ENTRIES IN document_tab 
WHERE bukrs = 'TPF'
AND belnr = document_tab-belnr
AND gjahr = document_tab-gjahr

Although normally the optimizer should take care of this, sometimes coding in the exact order helps (also it makes the code a bit easier to maintain). There is no need for a secondary key since all the fields from a primary key are known.

Re: Need Help in Select Statement

Former Member — Thu, 28 May 2009 18:51:33 GMT

> Although normally the optimizer should take care of this, sometimes coding in the exact order helps

is this really the case, in simple selects I have never seen that

> (also it makes the code a bit easier to maintain).

yes, this is true, also for cursor cache, it is recommended to have the same order as in the table

But if there are more conditions than the primary key, then all conditions should be used, the additional

one are restrictions, i.e. not all lines come into the result !!!

Additionally check whether the FOR ALL ENTRIES has duplicates, if yes, do sort and delete adjacent duplicates.

At the then if there 50.000 records, expected, then it can need 50.000.000 micrsosec, i.e. 50seconds

as a rough estimate. How many records are in BKPF and who fast is your hardware. Even good performance takes some time

Siegfried

Re: Need Help in Select Statement

Former Member — Fri, 29 May 2009 04:20:47 GMT

Hi,

One thing I would like to bring into your notice is that, when ever you are using for all entries, do select all the key fields, weather you are using those or not using those fields.

Else unexpected result will come.

Thanks,

Krishna..

Re: Need Help in Select Statement

romanweise — Sat, 06 Jun 2009 22:56:02 GMT

Hi,

i am wondering why the biggest issue in this select is not mentioned. Whenever you use FOR ALL ENTRIES try all you can to use only one field of the table in the WHERE clause. As soon as you use more than one table field in the WHERE clause the table buffers are bypassed. So check if in your context one of the field might be the same for all entries.

Rgds.

Roman

Re: Need Help in Select Statement

Former Member — Sun, 07 Jun 2009 05:14:25 GMT

Hi,

To increase the performance first do a sort function and add binary search with the key fields.

Regards,

subhashini