Skip to Content
author's profile photo Former Member
Former Member

SQL - Running slow..

Hi,

I've some performance problem in our production system.

It seems that the table EKPO takes long time...

I've tried some index stuff but with no luck.

Any pointers ?

select x_ekpo~ebeln

x_ekpo~lgort

x_ekpo~matnr

from ekpo as x_ekpo

inner join ekko as x_ekko on x_ekkoebeln = x_ekpoebeln

into corresponding fields of table i_ekpo_forbidden

for all entries in i_zpo_filter

where

(where_clause)

AND x_ekko~lifnr = i_zpo_filter-lifnr

AND x_ekpo~werks = v_werks

AND x_ekpo~bstyp = 'F' " Purchasing doc.

AND x_ekpo~loekz = space " Deletion indicator

AND x_ekpo~elikz = space. " Delivery Completed indicator

//Martin

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2005 at 09:23 AM

    Just Change it like this -

    select x_ekpo~ebeln

    x_ekpo~lgort

    x_ekpo~matnr

    from ekko as x_ekko

    inner join ekpo as x_ekpo on x_ekkoebeln = x_ekpoebeln

    into corresponding fields of table i_ekpo_forbidden

    for all entries in i_zpo_filter

    where

    (where_clause)

    AND x_ekko~lifnr = i_zpo_filter-lifnr

    AND x_ekpo~werks = v_werks

    AND x_ekpo~bstyp = 'F' " Purchasing doc.

    AND x_ekpo~loekz = space " Deletion indicator

    AND x_ekpo~elikz = space. " Delivery Completed indicator

    Cheers.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2005 at 09:29 AM

    hi,

    your query seems to be ok.

    create a index for Lifnr and try with that.

    there is no pointers in the sql query. how many records you are retrieving ?

    and include some date range ( if possible ) and before the statement use

    delete adjacent duplicates from table i_zpo_filter by comapring lifnr.

    cheers,

    sasi

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2005 at 09:38 AM

    Hi,

    here is some points that slow down the query

    - 'for all entries in' has the same effect as

    LOOP @ ITAB. SELECT... ENDLOOP.

    so it is not performent at all if the table izpo_filter is big.

    - dynamic where clause is not performant neither.

    if i did give you some usefull info let me know by rewarding me.

    regards,

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2005 at 10:09 AM

    Check also that i_zpo_filter has entries, because if it has no entries it will do a select of the whole table without filter and it will be very slow....

    check not i_zpo_filter[] is initial.

    Regards,

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2005 at 02:12 PM

    Have you done a performance trace to see which index is being used?

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2005 at 03:18 PM

    Try using views like MASSEKPO.

    YOu can look for other views that might satisfy your conditions. Use where used from SE11 for table EKPO, select 'Views' from the options.

    Choose something that fits your requirement.

    Look at view WB2_V_EKKO_EKPO2, I ran a comparison between your select and selecting from this view. The view takes just half the time. Beware some fields are named with _I extension.

    Like MATNR is MATNR_I.

    I did not check the join conditions in the view. Pls. check that too.

    Message was edited by: Cyril Alex

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.