topic Re: performance issu... in Application Development and Automation Discussions

performance issu...

Former Member — Thu, 02 Feb 2006 07:57:41 GMT

how i increase the performance of this query.....

SELECT belnr buzei kschl hwste mwskz shkzg txgrp hwbas

INTO TABLE tax_tab

FROM bset FOR ALL ENTRIES IN itab_pur

WHERE belnr = itab_pur-belnr

AND gjahr = itab_pur-gjahr.

Re: performance issu...

Former Member — Thu, 02 Feb 2006 08:02:55 GMT

Hi,

if ITAB_PUR is initial then unnecessarily the select query would be executed. Hence before the select query put the following condition :

if not itab_put[] is initial.

select query ......

endif.

Apart from that the order(hierarchy) in which you are selecting the fields should be same as the one in database table BSET.

Also if possible include more primary keys in the where clause.

Best regards,

Prashant

Message was edited by: Prashant Patil

Re: performance issu...

hymavathi_oruganti — Thu, 02 Feb 2006 08:10:45 GMT

hi,

u didnt mention the DBTAB?????????

to increase performance u can use subqueries in place of for all entries.

try this

select belnr buzei kschl hwste mwskz shkzg hwbas from DBTAB AS F

INTO TABLE tax_tab where belnr = DBTAB-belnr

AND EXISTS ( SELECT * FROM DBTAB1 INTO ITAB_PUR WHERE

BELNR = F~BELNR

AND GJAHR = F~GJAHR).

Re: performance issu...

Former Member — Thu, 02 Feb 2006 08:18:51 GMT

Include also the company code (BUKRS) in the where clause, this will help the query at database level to run much faster.

SELECT belnr buzei mwskz txgrp shkzg hwbas hwste kschl

INTO TABLE tax_tab

FROM bset FOR ALL ENTRIES IN itab_pur

WHERE belnr = itab_pur-belnr

AND gjahr = itab_pur-gjahr.

Also align the fields in the internal table in the same way.

Re: performance issu...

Former Member — Thu, 02 Feb 2006 14:18:59 GMT

If you don't know the company code, you can do this:


ranges: r_bukrs for bset-bukrs.
data: begin of bukrs_int occurs 0,
        bukrs like bset-bukrs,
      end   of bukrs_int.

SELECT bukrs FROM  t001
       INTO TABLE bukrs_int.
MOVE 'EQ'     TO r_bukrs-option.
MOVE 'I'      TO r_bukrs-sign.
LOOP AT bukrs_int.
  MOVE bukrs_int-bukrs TO r_bukrs-low.
  APPEND r_bukrs.
ENDLOOP.
 
SELECT belnr buzei kschl hwste mwskz shkzg txgrp hwbas
  INTO TABLE tax_tab
  FROM bset FOR ALL ENTRIES IN itab_pur
  WHERE bukrs in r_bukrs
  AND   belnr = itab_pur-belnr
  AND   gjahr = itab_pur-gjahr.

It seems counter-intuitive, but it works.

Rob

Re: performance issu...

former_member186741 — Fri, 03 Feb 2006 01:49:29 GMT

as others have said,

1. use BUKRS in your where clause

2. check that itab_pur has some entries before doing your select

also,

3. ensure that itab_pur is sorted and has duplicates removed - you may need to create a subset table to do this if you need all the entries for other reasons

Re: performance issu...

Former Member — Fri, 03 Feb 2006 07:44:37 GMT

Hi,

Kindly also not that Select * is much faster than

Select <Field 1> <Field 2>

So if possible in your logic try incorporating this.

regards,

Sumeet Mishra