runtime performance

[Former Member]

Hi there,

I am new to abap programming.

Here i would like to ask what is the suggested method to retrive large amount of record from database. I spend a lot of time when fetching record from database, more than 30min. i try to do performance tunning but the result still not improve.

please advise.

thank.

[Former Member]

Hi simon,

If you paste your code we can tell you what may be wrong.

Regards,

Atish

[Former Member]

thank for reply.

my coding part something like this.

SELECT B1 H2 B3 B4 B5 B6 B7

FROM BSAS INTO TABLE T_BSAS

WHERE B1 = S_BUKRS

AND H2 IN ('0040000110', '0040000120')

AND B3 IN S_PDATE

AND ( B6 = 'DR' OR B6 ='RV' OR B6 = 'DA' OR B6 = 'SA' OR B6 = 'KS' OR B6 = 'TA' ).

the record in the database is huge and how can i improve the performance. any other method to fetch record more faster?

pls advise. thanks.

[Former Member]

Hi,

Can't you pass all the key fields to this table in WHERE clause.

This table will have huge data at any point of time.

So if you don't have other key fields value you can't do much in that case.

regards,

Atish

[Former Member]

Hi there,

meaning that i need to be more specified in the WHERE clause?

by doing this, will increase preformance or not?

pls advise.

thanks

[Former Member]

Yes. You are right.

This will definately improve performance.

Regards,

Atish

[Former Member]

Hi,

One more thing Simon,

Use IN list and not OR conditions.

You can use ,

SELECT B1 H2 B3 B4 B5 B6 B7
FROM BSAS INTO TABLE T_BSAS
WHERE B1 = S_BUKRS
AND H2 IN ('0040000110', '0040000120')
AND B3 IN S_PDATE
*AND ( B6 = 'DR' OR B6 ='RV' OR B6 = 'DA' OR B6 = 'SA' OR B6 = 'KS' OR B6 = 'TA' ).
AND B6 IN ('DR', 'RV', 'DA', 'SA', 'KS', 'TA').

I believe in this query <b>Primary Index</b> is already being used since you are using 2 key fields in correct sequence in where clause. I am not sure whether this field B3 is AUGDT or not. If it is then you are using 3 key fields in where clause which should trigger using Primary Index.

Alternatively you can check in ST05 whether it is being used or not.

Hope this helps.

Regards

Nishant

[Former Member]

Hi Simon,

Otherwise, if you feel the required selection can't be modified with the key fields, you can create INDEXS on the non-key fields at database level which you are using in 'WHERE' clause, this will definitely reduce the selection time.

Regards

Ali S

[Former Member]

Hi Simon,

Otherwise, if you feel the required selection can't be modified with the key fields, you can create INDEXS on the non-key fields at database level which you are using in 'WHERE' clause, this will definitely reduce the selection time.

Regards

Ali S

[Former Member]

Hi there,

i am interested in what you suggest.

can u give me an example for how to create indexex?

pls advise. thanks.

[Former Member]

Hi Simon,

It is not recommended that you create index on BSAS without considering many factors as it very commanly used table and creating index will again increase the space in DB,

Below is the documentation on how to create index

http://help.sap.com/saphelp_nw04/helpdata/en/cc/7c58b369022e46b629bdd93d705c8c/content.htm

Regards,

Atish

[Former Member]

I mean create INDEXES.

Regards

Ali S

[Former Member]

Best not to create indexes - would you post your actual ABAP code please? We could do better if we could see the data dictionary fields in the WHERE.

Rob

[Former Member]

Hi Simon,

One more thing you can do to your code is change your B6= to IN.

So it reads:

AND B6 IN ('DR', 'RV', 'DA', 'SA', 'KS', 'TA').

It is a huge table, Bukrs is part of the primary key but I don't know what your other fields are. I think though, it is using the primary index and probably scanning the whole table/index.

Secondary index would definitely help but you need someone to help determine the index fields and the sequence of the fields in the index.

Hope this helps.

Filler

[Former Member]

Simon,

You may not have access to create index, if this is so, you need someone from your Basis team if you don't have a performance team, to run ST05 and DB05 and it will show you how the statement is intepreted and how it will read the data.

Those people will know how to use these TCodes and they will have access to create the index.

The index must be created correctly with the fields in the correct order, otherwise it will not work properly and may be worse than not having one.

Filler

[former_member194613]

please use SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Tell us your total SQL time and the duration for the Top 3 in the SQL Summary.

Siegfried