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

Problem with Select in Production

hi all,

My Select query is working fine in Devlopment server, but it is going to Dump in Production. In ST05, it is showing time-out error, may be due to heavy data, i guess. Is there any way to modify this query sothat it wont go to Dump in Production. Plz have a look:

select a~budat as sdate

a~vkont as contAcc

a~xblnr as contract

b~hkont as subTrans

b~betrw as amount

c~ktext as product

d~vkbez as customer

e~zzdepot as depot

into corresponding fields of table iData

from dfkkop as a

inner join dfkkopk as b

on aopbel = bopbel

and aopupk = bopupk

left join cskt as c

on ckostl = bkostl

inner join fkkvkp as d

on dvkont = avkont

inner join but000 as e

on epartner = dgpart

where

a~vkont in rAcc

and a~budat in rDate

and a~hvorg = 'CMS'

and a~bukrs in sobukrs

and a~tvorg in rComp

and a~opupz = 000

and zzdepot in rDepot.

  • rDate, rComp, rDepot, sobukrs are select-options in ****selection screen

Thanks in advance,

Regards,

HKM

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2006 at 09:48 AM

    Hi

    If possible try to reduce the joins.

    May be you can split the query into multiple selects and then you can insert the data into table iData by using read table statements.

    Just try using this may be it will work.

    Thanks & Regards

    Deepti

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 27, 2006 at 09:49 AM

    Hi,

    just a suggestion.

    Probably u don't fill any select-option.

    Try to fill just one of them.

    If this doesen't help, try to watch the estimated cost of the query to see if the right indexes are used.

    U can see the estimated cost from st04 -> Sql requests -> check the pid of the workprocess used by the query

    Hope it helps

    Bye

    Andrea

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 27, 2006 at 09:49 AM

    hi vinny,

    some sugestions.

    1. avoid join and use FOR ALL ENTRIES.

    2.never write SELECT query inside the loop.

    3.never use INTO CORRESSPONDING FIELD OF TABLE, use INTO TABLE.

    4.use READ TABLE WITH BINARY SEARCH, after SORTING.

    5.Inside the condition, if the condition is satisfied, use EXIT.

    rgds

    anver

    if helped mark points

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2006 at 09:44 AM

    Hi,

    remove inner join and use all entries and follow below steps

    1) Remove corresponding from select satement

    2) Remove * from select

    3) Select field in sequence as defined in database

    4) Avoid unnecessary selects

    i.e check for internal table not initial

    5) Use all entries and sort table by key fields

    6) Remove selects ferom loop and use binary search

    7) Try to use secondary index when you don't have

    full key.

    8) Modify internal table use transporting option

    9) Avoid nested loop . Use read table and loop at itab

    from sy-tabix statement.

    10) free intrenal table memory when table is not

    required for further processing.

    11)

    Follow below logic.

    FORM SUB_SELECTION_AUFKTAB.

    if not it_plant[] is initial.

    it_plant1[] = it_plant[].

    sort it_plant1 by werks.

    delete adjacent duplicates from it_plant1 comparing werks

    SELECT AUFNR KTEXT USER4 OBJNR INTO CORRESPONDING FIELDS OF TABLE I_AUFKTAB

    FROM AUFK

    FOR ALL ENTRIES IN it_plant1

    WHERE AUFNR IN S_AUFNR AND

    KTEXT IN S_KTEXT AND

    • WERKS IN S_WERKS AND

    AUART IN S_AUART AND

    USER4 IN S_USER4 AND

    werks eq it_plant1-werks.

    free it_plant1.

    Endif.

    ENDFORM. "SUB_SELECTION_AUFKTAB

    Regards

    amole

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2006 at 09:51 AM

    HI,

    in ur code u r accessing 5 db tables and going with innerjoins.

    so first select data from table and put in each itab.

    based on the data in itab1 ,go with 'for all entries'

    this will dont give u dump.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2006 at 09:54 AM

    I would advice to split the SELECT, thereby reducing the number of joins.

    select a~budat as sdate

    a~vkont as contAcc

    a~xblnr as contract

    b~hkont as subTrans

    b~betrw as amount

    b~kostl

    a~vkont

    into corresponding fields of table iData

    from dfkkop as a

    inner join dfkkopk as b

    on aopbel = bopbel

    and aopupk = bopupk

    where

    a~vkont in rAcc

    and a~budat in rDate

    and a~hvorg = 'CMS'

    and a~bukrs in sobukrs

    and a~tvorg in rComp

    and a~opupz = 000.

    ivkont[] = idata[].

    ikostl[] = idata[].

    sort ivkont by vkont.

    delete adjacent duplicates in ivkont.

    sort ikostl by kostl.

    delete adjacent duplicates in ikostl.

    select data from cskt using for all entries in ikostl.

    select data from fkkvkp and but000 using for all entries in ivkont.

    Loop at idata.

    read icsktdata.

    read ifkkvkpdata.

    modify idata.

    endloop.

    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.