topic Re: Problem in SELECT statement..... in Application Development and Automation Discussions

Problem in SELECT statement.....

Former Member — Wed, 30 Dec 2009 03:04:27 GMT

Hi Friends,

i have stuckuped with getting the data,

here am using select statement for getting data, but it gives TIME OUT problem when bulk records come, i ve tried other way but i am nt getting, anybody pls give me the alternative logic...

my existng code is:

IF NOT gt_bkpf[] IS INITIAL.

SELECT bukrs

belnr

gjahr

buzei

augdt

augcp

augbl

bschl

koart

FROM bseg

INTO TABLE gt_bseg1

FOR ALL ENTRIES IN gt_bkpf

WHERE bukrs EQ gt_bkpf-bukrs

AND augbl EQ gt_bkpf-belnr

AND augcp EQ gt_bkpf-cpudt

AND bschl NOT IN ('25', '38').

The above one is working, but going for Time out. i ve tested with opther ways but not getting data into my gt_bseg1 table.

i tried with the below code, but its not working...

IF NOT gt_bkpf[] IS INITIAL.

SELECT bukrs

belnr

gjahr

buzei

augdt

augcp

augbl

bschl

koart

rebzg

rebzj

FROM bseg

INTO TABLE gt_bseg

FOR ALL ENTRIES IN gt_bkpf

WHERE bukrs EQ p_bukrs

AND belnr IN s_belnr.

AND gjahr EQ p_gjahr.

IF sy-subrc EQ 0.

SORT gt_bseg ASCENDING BY bukrs belnr gjahr augbl.

DELETE ADJACENT DUPLICATES FROM gt_bseg.

ENDIF.

1. ( The below is Alternative code 1) not working.*

LOOP AT gt_bkpf INTO gwa_bkpf.

CLEAR gwa_bseg.

READ TABLE gt_bseg INTO gwa_bseg

with KEY bukrs = gwa_bkpf-bukrs

augbl = gwa_bkpf-belnr

augcp = gwa_bkpf-cpudt.

if sy-subrc eq 0 AND ( gwa_bseg-bschl <> '25'

*AND gwa_bseg-bschl <> '38' ).

append gwa_bseg to gt_bseg1.

ENDIF.

ENDLOOP.

2. the below one also not working....*

LOOP AT gt_bkpf INTO gwa_bkpf.

CLEAR gwa_bseg.

LOOP AT gt_bseg INTO gwa_bseg.

For gt_bseg1

IF gwa_bseg-augbl EQ gwa_bkpf-belnr

AND gwa_bseg-augcp EQ gwa_bkpf-cpudt

AND ( gwa_bseg-bschl NE gc_bschl_out

AND gwa_bseg-bschl NE gc_bschl_clr ).

APPEND gwa_bseg TO gt_bseg1.

ENDLOOP.

Can anyone plz help me out on this.

Thanks in Advance..

sudha.

Re: Problem in SELECT statement.....

Former Member — Wed, 30 Dec 2009 03:22:51 GMT

Hi Sudha,

I will suggest you to search SDN for performance tuning.

Also , you should try to avoid NOT IN .

Hope this helps you.

Re: Problem in SELECT statement.....

Former Member — Wed, 30 Dec 2009 04:26:59 GMT

HI Sudha,

Try to avoid fetching data in BSEG table and not in.

There are alternation tables for Bseg, Please sreach in SDN.

Regards

Re: Problem in SELECT statement.....

Former Member — Wed, 30 Dec 2009 04:39:01 GMT

Hi,

Problem with your select query is that your using BSEG table which is suppose to be a cluster table for 5 transparent table namely :

BSEC, BSED, BSEG, BSES & BSET

So your data should be present in few of the above table. rather fetching data from BSEG..try using joins on above table, whichever are applicable to you.

regards

vivek

Re: Problem in SELECT statement.....

Former Member — Wed, 30 Dec 2009 04:42:29 GMT

Dear Sudha ,

BSEG is a cluster table. Any select query on cluster table will degrade a performance for sure. SAP has provided alternatives for that.

Try using following tables in your select query instead of BSEG :

BSAD Accounting: Secondary Index for Customers(Cleared Items)

BSID Accounting: Secondary Index for Customers

BSAK Accounting: Secondary Index for Vendors (Cleared Items)

BSIK Accounting: Secondary Index for Vendors

BSAS Accounting: Secondary Index for G/L Accounts (Cleared Items)

BSIS Accounting: Secondary Index for G/L Accounts

try to use any of the above tables in your program . Performace will surely improve without breaking the business logic.

Revert to me in case of any help required.

Regards,

Nikhil

Re: Problem in SELECT statement.....

Former Member — Wed, 30 Dec 2009 04:57:28 GMT

Hi,

This query from BSEG will definitely go for a toss.

The reason is we are not using primary fields in the where clause, instead we are using

augbl

augcp

bschl

which do not have any secondary index, and more over it is not possible to build secondary indexes on them since BSEG is a cluster table.

My points for this would be:

1. Instead of BSEG, use all the following tables and append their records.

BSIS, BSAS, BSID, BSAD, BSIK, BSAK

a) This is because, these tables already have indexes on AUGBL.

b) Instead of AUGCP, you may have to use AUGGJ or AUGDT.

c) If you are using AUGGJ, then you may have to create secondary index on this field.

d) For BSCHL, do not write in where clause. instead fetch all records in itab, and then filter in one go.

I know this sounds complicated, but for BSEG queries there are not much options.

Regards,

Amit Mittal.

Re: Problem in SELECT statement.....

anup_deshmukh4 — Wed, 30 Dec 2009 06:50:55 GMT

Hello sudhasumathi

Insted of using Direct Comparision in your Query/ Code i would suggest to use VALUE RANGE for fix value comparision it would improve performance

Edited by: Anup Deshmukh on Dec 30, 2009 7:51 AM

Re: Problem in SELECT statement.....

Former Member — Wed, 30 Dec 2009 08:13:43 GMT

hi,

use all the PRIMARY KEYS in the selection query..

hope this solve ur problem

Regards

RItesh J

Re: Problem in SELECT statement.....

Clemenss — Wed, 30 Dec 2009 08:47:37 GMT

Hi,

the subject "Problem in SELECT statement....." is perfect. Now we know it is not "Problem with girlfriend....."

Regards,

Clemens