performence probelm

[Former Member]

Moved to correct forum by moderator. Please use a meaningful subject in future.

Hi All,

I am fetching data from EKPO, where I am using the below in my where conditions. Other than this I donu2019t have much suitable condition. As I am not using proper key the performance is getting problem now.

Select fld1 fld2 fld3 from EKPO into table I_EKPO where

AND loekz = ' '

AND pstyp <> '5'

AND knttp NOT IN ('C' ,'2').

So can anyone suggest me to improve the performance of select query? like how to avoid not conditions as this will kills the perfrmence.

Edited by: Matt on Dec 8, 2008 9:20 AM

[Former Member]

Moved to correct forum by moderator. Please use a meaningful subject in future.

Hi All,

I am fetching data from EKPO, where I am using the below in my where conditions. Other than this I donu2019t have much suitable condition. As I am not using proper key the performance is getting problem now.

Select fld1 fld2 fld3 from EKPO into table I_EKPO where

AND loekz = ' '

AND pstyp <> '5'

AND knttp NOT IN ('C' ,'2').

So can anyone suggest me to improve the performance of select query? like how to avoid not conditions as this will kills the perfrmence.

Edited by: Matt on Dec 8, 2008 9:20 AM

[Former Member]

Hi Raghu,

Are u using all primany keys in where condition?

Try to avoid NE condition.

Best Regards,

Flavya

[Former Member]

Instead of NOT IN .. declare a range and populate your values with

SIGN = 'E'.

[Former Member]

Hi,

Try to fetch the data based on key fields.

once the data is available in your interal tables ; delete the records from your internal table based on your conditions as per your requirements.

This will definately improve the performance.

Thanks.

[Former Member]

Hi,

Remember 2 very basic things, for performance issue.

1> Always use primary keys in select stmt.

2> Refresh internal table after your internal table work complets.

Thanks & Regards,

Krishna..

[matt]

Please use a meaningful subject in future.

[Former Member]

Hi,

Please see if you can use the index EKPO~1. You can probably use the material number, plant here. There are many other fields that can be used for this index in the order.

MATNR Material Number

WERKS Plant

BSTYP Purchasing document category

LOEKZ Deletion indicator in purchasing document

ELIKZ "Delivery Completed" Indicator

MATKL Material Group

Also try to join the query with EKKO and use some fields like creation date range , document types, company code, plant etc. Make these fields mandatory on the selection screen. Once you have the keys from ekko, then it would be easy to fetch data from ekpo based on the ebeln and ebelp and further fileter criterias.

regards,

Advait

[Former Member]

Dear Raghu,

range: lr_ebeln for ekpo-ebeln,

lr_ebelp for ekpo-ebelp.

Select fld1

fld2

fld3

from EKPO

into table I_EKPO

where ebeln in lr_ebeln

and ebelp in lr_ebelp

AND loekz = ' '

AND pstyp '5'.

delete i_ekpo[] where knttp eq 'C'

or knttp eq '2'.

here lr_ebeln and lr_ebelp are dummy ranges .

once fetching the data.. just deleted with the third condition.. it may improve your performance..

just try it.. all the best..

UR's

GSANA

[christine_evans]

>

> Dear Raghu,
> 
> range: lr_ebeln for ekpo-ebeln,
>        lr_ebelp for ekpo-ebelp.
> 
> Select fld1 
>        fld2 
>        fld3 
>   from EKPO 
>   into table I_EKPO 
>  where ebeln in lr_ebeln
>    and ebelp in lr_ebelp
>    AND loekz = ' '
>    AND pstyp '5'.
> 
> delete i_ekpo[] where knttp eq 'C'
>                    or knttp eq '2'.
>  
> here lr_ebeln and lr_ebelp are dummy ranges .
>  once fetching the data.. just deleted with the third condition.. it may improve your performance.. 
> just try it.. all the best.. 
> 
> UR's
> GSANA

How is using an empty LR_EBELN ranges table supposed to help performance? The query will just ignore it.

There is no magic way of getting data quickly out of a large table without using an index. The last resort, if you can find no other way to do it, is to create a custom index on the table.

[Former Member]

Hi Gsana,

why select MORE data than needed and delete it afterwards? You put some workload on Appserver and database and at the end you say: it doesn't matter. Imagine this would cause x% more rows to read from the DB... x = <any number because you can't know how much you will retrieve>

As Christine stated: There is no difference in an empty tables and removing the IN clause. At parsing time the empty IN clause would be removed anyway.

bye

yk

[Former Member]

>

> Select fld1 fld2 fld3 from EKPO into table I_EKPO where

> AND loekz = ' '

> AND pstyp <> '5'

> AND knttp NOT IN ('C' ,'2').

>

Hi,

to make better use of an index you can express the NOT IN as IN.

You can express pstype <> with LT or GT :

Select fld1 fld2 fld3 from EKPO into table I_EKPO where
                  AND   loekz = ' '
                  AND   (pstyp LT '5'  or pstyp GT '5')
                  AND   knttp  IN ('A' ,'9','8'....).

This would make an index more likely IF your WHERE fields have a sufficient row selectivity to allow for an index access (~ 2%- 5% of the rows ), and maybe you have to create an additional index with the WHERE fields as well.

bye

yk

[Former Member]

Hi,

Select fld1 fld2 fld3 from EKPO into table I_EKPO where

AND loekz = ' '

AND pstyp '5'

AND knttp NOT IN ('C' ,'2').

as you know that

primary key is MANDT EBELN EBELP and you are not using that so performanace issue will be there following tare the some ways by using that you can improve perfomanace.

No of Ways to improve performanace .

1. Create secondary Index (maintain the sequence of inndex as per database table sequence for field)

2. Do not use negative statements like NOT IN instead of select entries based on the loekz and pstyp and then delete the entries from internal table where knttp NE C'' or '2'

try to give less field in where condition so that permitation combination for table records will reduce and ultimately database hit will reduce.

I guess this will resolve your question

[Former Member]

Thanks all