topic Re: improve performance in Application Development and Automation Discussions

improve performance

Former Member — Thu, 05 Apr 2007 07:12:32 GMT

Hi,

i need to improve the performance of this piece of code

IF p_specl NE 'X'.
      SELECT * INTO CORRESPONDING FIELDS OF TABLE t_zepc_bp_deduct FROM
      zepc_bp_deduct WHERE
      ( ded_cat = construc-garnamt AND ded_start <= w_garnish_date
        AND ded_end >= w_garnish_date )
      OR
      ( ded_cat = construc-garnamt AND ded_start <= w_garnish_date
        AND ded_end = '00000000' )
      OR
      ( ded_cat = construc-defcomp AND ded_start <= w_def_comp_date
        AND ded_end >= w_def_comp_date )
      OR
      ( ded_cat = construc-defcomp AND ded_start <= w_def_comp_date
        AND ded_end = '00000000' )
      OR
      ( ded_cat = construc-compayagr AND
        ded_start <= w_comm_pay_agree_date AND
        ded_end >= w_comm_pay_agree_date )
      OR
      ( ded_cat = construc-compayagr AND
        ded_start <= w_comm_pay_agree_date AND
        ded_end = '00000000' )
      OR
      ( ded_cat = construc-loantype AND ded_start <= w_loan_type_date
        AND ded_end >= w_loan_type_date )
      OR
      ( ded_cat = construc-loantype AND ded_start <= w_loan_type_date
        AND ded_end = '00000000' )
      OR
      ( ded_cat = construc-repded AND ded_start <= w_repeat_ded_date
        AND ded_end >= w_repeat_ded_date )
      OR
*      ( ded_cat = construc-repded AND ded_start <= w_repeat_ded_date
*        AND ded_end = space )
      ( ded_cat = construc-repded AND ded_start <= w_repeat_ded_date
        AND ded_end = '00000000' )

      ORDER BY bp_partner insobject priority.
    ELSE.
      SELECT * INTO CORRESPONDING FIELDS OF TABLE t_zepc_bp_deduct FROM
      zepc_bp_deduct WHERE
      ( ded_cat = construc-garnamt
      AND ded_start <= w_garnish_date
      AND ded_end >= w_garnish_date )
      OR
      ( ded_cat = construc-garnamt
      AND ded_start <= w_garnish_date
      AND ded_end = '00000000' )

      ORDER BY bp_partner insobject priority.
    ENDIF.

ded_start,ded_end,ded_cat are the not the key fields of the z-table which i am using.

can anyone suggest me any possible way to enhance the performance.

points assured.

thanks in advance.

regards,

maahi

Re: improve performance

Former Member — Thu, 05 Apr 2007 07:14:51 GMT

what are u doing ?

these many conditions in where clause.

Regards

Prabhu

Re: improve performance

Former Member — Thu, 05 Apr 2007 07:21:31 GMT

hi,

this is not done by me,and its performance is too bad.

i am asked to improve its performance.

and these many conditions are unavoidable..as i think.. if you can suggest me a better idea..it will be good.

or can i create secondary index for the fields which i am using in where clause in my z-table?

thanks and regards,

maahi

Re: improve performance

Former Member — Thu, 05 Apr 2007 07:29:49 GMT

Hi,

Few tips:

1> If possible replace Corresponding fields with the actual field name, coz this is a very heavy weight statement that checks for the structure of each field and has a worst case complexity of O(n*n)..

2>You can remove all the conditions from the database table and operate those conditions on a internal table, which can improve performance in some cases, depending upon the size of the table.

3> Some of the conditions are redundant and you can restructure them.

Re: improve performance

Former Member — Thu, 05 Apr 2007 08:09:28 GMT

1st select:

the first select has so many logic comparisons in its where condition. and moreover since the select fetches data from Ztable...guess u can do the comparison after the select. use if condition process the data accordingly.

It would be better if you can remove this Order by clause. this will definitely improve the peroformance.

In the else condition select:

1.select only the required fields instead of select *

2. Avoid into corresponding fields.

3. If the WHERE clause has constants in logical comparison..u can remove it from the select and delete the unwanted entries after the data is fetched from the table.

If the where condition doesnt have any primary key fields, try to create an index.

**reward if helpful.

regards,

madhu

Re: improve performance

Former Member — Thu, 05 Apr 2007 09:02:10 GMT

Very important :

- replace the "SELECT * INTO CORRESPONDING FIELDS OF TABLE t_zepc_bp_deduct" by the needed fields : "SELECT x y z INTO TABLE t_zepc_bp_deduct"

- remove the "ORDER BY ..." and add a "SORT t_zepc_bp_deduct by ..."

Hope that help.

Dieu

Re: improve performance

Former Member — Fri, 06 Apr 2007 05:11:40 GMT

haha!

sorry I cldn't help looking @ it.

You can do this by writing

Select <fld1>

<fld2>

<fld3>

ded_cat

ded_start

ded_end

from <watevr table>

into table <ur internal table>

where ded_cat in rg_ded_cat

and ded_start in rg_ded_start

and ded_end in rg_ded_end.

These ranges will contain all values your trying to get

say for ded-cat range is (all values from table contruc for field garnamt and all values from table contruc for field defcomp )

for ded_start it will be ( <= w_garnish_date...so on)

Then you can do read table <ur internal table > with the conditions that are put in the where condition

and BTW remove the CORRESPONDING and write specific fields that are being selected in place of fld1, fld2...etc.

hope this helps

else u can ask and I can give you my email

Also, is contruc an internal table coz if it is...will help if u can re-write the logic again coz I think this select query is in a loop!!

Regards

Nishant Rustagi

null

Re: improve performance

Former Member — Mon, 09 Apr 2007 06:28:05 GMT

Hi,

Try this:

select <fld list> into itab from z_table

where

ded_cat in s_ded_cat and

w_garnish_date between ded_start and ded_end and

In short use select options efficiently, better to write the codes from scratch,

Remove into corrosponding field clause

Never use order by in select instead sort itab.

Here ORing is used means solution will be easier, its matter of logic + knowledge of how sel options works.

u can use ded_end is initial option interad of '0000000'

Re: improve performance

Former Member — Tue, 10 Apr 2007 04:30:55 GMT

Hi Maahi,

Please check the logic which you need to implement from this select query.

This select query seems many no of logical conditions which itself inturn the poor performance of this select query.

Second point i like to add is instead of SELECT* refer required fields of structure where you need to get the required table values.

At the last, if possible get the alternate of logic implementation instead of using these LOC.

thanks.

Regards,

Hem

Re: improve performance

Former Member — Tue, 10 Apr 2007 07:12:54 GMT

Hi,

You can do like this ,

IF p_specl NE 'X'.

SELECT *

FROM zepc_bp_deduct

INTO CORRESPONDING FIELDS OF TABLE t_zepc_bp_deduct .

LOOP AT t_zepc_bp_deduct .

IF ( ded_cat = construc-garnamt AND ded_start <= w_garnish_date

AND ded_end >= w_garnish_date )

( ded_cat = construc-garnamt AND ded_start <= w_garnish_date

AND ded_end = '00000000' )

( ded_cat = construc-defcomp AND ded_start <= w_def_comp_date

AND ded_end >= w_def_comp_date )

( ded_cat = construc-defcomp AND ded_start <= w_def_comp_date

AND ded_end = '00000000' )

( ded_cat = construc-compayagr AND

ded_start <= w_comm_pay_agree_date AND

ded_end >= w_comm_pay_agree_date )

( ded_cat = construc-compayagr AND

ded_start <= w_comm_pay_agree_date AND

ded_end = '00000000' )

( ded_cat = construc-loantype AND ded_start <= w_loan_type_date

AND ded_end >= w_loan_type_date )

( ded_cat = construc-loantype AND ded_start <= w_loan_type_date

AND ded_end = '00000000' )

( ded_cat = construc-repded AND ded_start <= w_repeat_ded_date

AND ded_end >= w_repeat_ded_date )

ORDER BY bp_partner insobject priority.

ELSE.

SELECT * INTO CORRESPONDING FIELDS OF TABLE t_zepc_bp_deduct FROM

zepc_bp_deduct WHERE

( ded_cat = construc-garnamt

AND ded_start <= w_garnish_date

AND ded_end >= w_garnish_date )

( ded_cat = construc-garnamt

AND ded_start <= w_garnish_date

AND ded_end = '00000000' )

ORDER BY bp_partner insobject priority.

ENDIF.

I hope this is helpful for you.

Best regards ,

Satya.

Re: improve performance

Former Member — Tue, 10 Apr 2007 10:35:44 GMT

You keep "SELECT * INTO CORRESPONDING FIELDS" and "ORDER BY" , which are both bad for performance !