topic Re: Optimized select statement - performance issue in Application Development and Automation Discussions

Optimized select statement - performance issue

Former Member — Sat, 17 Mar 2012 11:12:27 GMT

Hi,

I have a select statement on table FMFCTRT which is hitting the performance badly in my report which is called inside a BSEG table loop. I have included all the primary keys in the where condition still I do not see the difference in performance. Can anyone check and see if this is a optimized select statement on FMFCTRT table ?

SELECT BESCHR INTO LS_FILSTRU-BESCHR

UP TO 1 ROWS FROM FMFCTRT

WHERE SPRAS = SY-LANGU

AND FIKRS = L_FIKRS

AND FICTR = LS_FILSTRU-FISTL

AND DATBIS => LS_FILSTRU-BLDAT.

Regards

Praneeth

Re: Optimized select statement - performance issue

former_member189779 — Sat, 17 Mar 2012 19:35:12 GMT

Hi,

Remove select statement if its inside the loop. Write before looping.

Then use READ TABLE inside your bseg loop to read respective entries from your FMFCTRT internal table.

Hope this helps.

Re: Optimized select statement - performance issue

vijaymanikandan — Sat, 17 Mar 2012 22:23:37 GMT

Hello

Avoid using select inside Loop as mentioned by Vinit. Apart from this check the database statistics in DB20 for the table and ask your basis team to update the same if its needed.

Br,

Vijay V

Re: Optimized select statement - performance issue

Former Member — Mon, 19 Mar 2012 15:03:50 GMT

Well, I would use a SELECT SINGLE rather than UP TO 1 ROWS, but I don't think that is your problem. Check you SELECT on BSEG.

Also - how many records in the BSEG loop?

Rob

Re: Optimized select statement - performance issue

Former Member — Tue, 20 Mar 2012 04:49:16 GMT

Hi,

Just as Vinit said: Remove SELECT STATEMENT inside the loop. Get it before looping using FOR ALL ENTRIES then in your looping use READ TABLE.

Regards,

Jake

Re: Optimized select statement - performance issue

Former Member — Tue, 20 Mar 2012 14:35:43 GMT

I really don't think having the SELECT in the LOOP is the problem.

It's more likely that the BSEG internal table has multiple entries for the same value of the primary keys. So the samne SELKECT is done over and over for no particular reason.

Only do the SELECT once for each unique value of the primary key.

Rob

Re: Optimized select statement - performance issue

PeterJonker — Tue, 20 Mar 2012 14:58:57 GMT

I also think you should not use the select within a loop. For every entry in the loop a database select is performed. Better is to select all entries at once and use a read.

Furthermore, when you are only dealing with cleared items, open iotems or customer items, you should consider to use the tabel BSIS, BSAS or BSID instead, since they are for that purpose.