topic Re: runtime performance in Application Development and Automation Discussions

runtime performance

Former Member — Mon, 22 Oct 2007 02:33:04 GMT

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.

Re: runtime performance

Former Member — Mon, 22 Oct 2007 02:36:30 GMT

Hi simon,

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

Regards,

Atish

Re: runtime performance

Former Member — Mon, 22 Oct 2007 02:53:01 GMT

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.

Re: runtime performance

Former Member — Mon, 22 Oct 2007 02:57:46 GMT

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

Re: runtime performance

Former Member — Mon, 22 Oct 2007 03:05:50 GMT

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

Re: runtime performance

Former Member — Mon, 22 Oct 2007 03:08:37 GMT

Yes. You are right.

This will definately improve performance.

Regards,

Atish

Re: runtime performance

Former Member — Mon, 22 Oct 2007 09:55:21 GMT

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

Re: runtime performance

Former Member — Mon, 22 Oct 2007 09:55:48 GMT

Hi Simon,

Regards

Ali S

Re: runtime performance

Former Member — Mon, 22 Oct 2007 09:56:49 GMT

I mean create INDEXES.

Regards

Ali S

Re: runtime performance

Former Member — Mon, 22 Oct 2007 13:41:24 GMT

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

Re: runtime performance

Former Member — Mon, 22 Oct 2007 22:18:42 GMT

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

Re: runtime performance

Former Member — Tue, 23 Oct 2007 01:13:52 GMT

Hi there,

i am interested in what you suggest.

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

pls advise. thanks.

Re: runtime performance

Former Member — Tue, 23 Oct 2007 01:26:19 GMT

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

Re: runtime performance

Former Member — Tue, 23 Oct 2007 02:24:40 GMT

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

Re: runtime performance

Former Member — Tue, 23 Oct 2007 04:27:55 GMT

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

Re: runtime performance

Former Member — Tue, 23 Oct 2007 12:24:21 GMT

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