topic Re: performance optimization in SQL in Application Development and Automation Discussions

performance optimization in SQL

Former Member — Wed, 23 Jan 2008 15:12:42 GMT

I have a SQL that retrieves data from BSAK with 13 million records and it is taking very long time. I have the following SQL and I don't see how else I can optimize it! If anyone has any suggestions, please let me know.

select b_{bukrs b}zuonr b_{gjahr b}monat b_{belnr b}buzei

b_{lifnr b}blart b_{bldat b}xblnr b~budat

b_{shkzg b}wrbtr b_{sknto b}augbl

b~augdt

into corresponding fields of table i_bsak

from bsak as b

where b~bukrs in s_bukrs

and b~lifnr in s_lifnr

and b~umsks in s_umsks

and b~umskz in s_umskz

and b~augdt in s_augdt

and b~augbl in s_augbl

and b~zuonr in s_zuonr

and b~gjahr in s_gjahr

and b~belnr in s_belnr

and b~buzei in s_buzei

and b~blart in s_blart

and b~monat in s_monat

and b~bldat in s_bldat

and b~xblnr in s_xblnr

and b~cpudt in s_cpudt

and b~budat in s_budat

and b~wrbtr in s_wrbtr

and b~sknto in s_sknto.

Thanks.

Mithun

Re: performance optimization in SQL

JozsefSzikszai — Wed, 23 Jan 2008 15:20:57 GMT

hi Mithun,

some suggestions:

"into corresponding fields of table i_bsak"

==> replace with INTO TABLE i_BSAK (and make sure that it has exactly the same structure like the fields you select)

than you have to consider if you really need all of these fields in the WHERE:

and b~gjahr in s_gjahr

...

and b~monat in s_monat

...

and b~budat in s_budat

==> either gajhr and monat OR budat only (gjahr and monat will come from budat, so there is no need to check all three)

and b~buzei in s_buzei

==> this one you definetly don't need

and so on...

hope this helps

Re: performance optimization in SQL

Former Member — Wed, 23 Jan 2008 15:24:00 GMT

Hello,

You are only using one table BSAK and there is no join involved. So you may use select <field1> <field2> ... into table i_bsak where ....

Also, do not use 'corresponding' and instead define internal table i_bsak with the fields you need.

Thanks,

Venu

Re: performance optimization in SQL

Former Member — Wed, 23 Jan 2008 15:26:15 GMT

What would really help is look for suitable secondary indices and see if you can use these fields in the where condition with value = instad of value in. Simgle value so to speak.

Good luck,

Bert

Re: performance optimization in SQL

Former Member — Wed, 23 Jan 2008 15:27:01 GMT

Just make sure there are entries in both s_bukrs and s_lifnr.

Rob

Re: performance optimization in SQL

ThomasZloch — Wed, 23 Jan 2008 15:31:43 GMT

Hi,

you'd better make sure that S_BUKRS and S_LIFNR have very narrow selection criteria. If possible, S_BUKRS should have only ONE company code.

Otherwise I see no way to quickly select from 13 million BSAK records.

Greetings

Thomas

Edit: did not see Rob's post before I posted mine

Re: performance optimization in SQL

Former Member — Wed, 23 Jan 2008 15:39:36 GMT

good

i would suggest you to check with st22, and se30 to more about the points that needs to change in your select statement.

check these points.

1-Check wheather you r accessing data using key fields or not

2-Check whether you can use any alternative table other than the table you have used .

thanks

mrutyun^

Re: performance optimization in SQL

ThomasZloch — Wed, 23 Jan 2008 15:48:18 GMT

Hi Mrutyunjaya Tripathy,

I'm curious why you are writing "good" in the second line of your posts.

Would you tell me? No offense intended.

Greetings

Thomas

Re: performance optimization in SQL

JozsefSzikszai — Wed, 23 Jan 2008 16:13:08 GMT

probably it is some traditional greeting, however I am curious as well

Re: performance optimization in SQL

Former Member — Wed, 23 Jan 2008 16:21:04 GMT

Hi,

1. The select query can be modified little by changing INTO CORRESPONDING FIELDS with INTO TABLE as there are huge no.of records to reduce lil bit of time.

2. Can check that there are actually entries in the Select-Options for Primary or Secondary index key fields.

3. Can use less no.of Select-Options in the selection screen if possible or else make sure tht Select-Options have values(use small range or if possible single values) before executing the report.

4. Check the performance with ST05(SQL Trace) or SE30.

Regards,

Srilatha