issue in select query

[Former Member]

HI All experts,<br><br>

i have a program in which i have a select query <pre>

SELECT b~vertrag

a~anlage

a~operand

a~saison

a~ab

a~ablfdnr

a~bis

a~wert1

FROM ettifn AS a INNER JOIN ever AS b

ON a_{anlage = b}anlage

INTO TABLE li_captran PACKAGE SIZE p_pack

WHERE a~anlage IN s_anlage

AND a~ab <= l_date

AND a~bis >= l_date

AND a~operand IN ('ID-CAPLOAD','ID-OBLT')

AND b~einzdat <= l_date

AND b~auszdat >= l_date.</pre><br><br>

this query is taking a lot of time in running and we are facing lot of performance related issues

<br><br>

kindly guide me how i can break this into som simpler query

<br><br>

ettifn has 10 million records

ever has around 5 million records .

<br><br>

also please tell me how much data i can store into an internal table

can i store 5 million records in an internal table ??????

is it good in terms of performance of a program if not please tell me what is the better way of doing dat .

<br><br>

also tell me how secondry indexes help in improving performance of select query

is it good to maintain number of secondry indexes in a standard table or it decreases performance of a standard table .

<br><br>

<pre>

TYPES: BEGIN OF ty_eanlh,

anlage LIKE eanlh-anlage,

bis LIKE eanlh-bis,

tariftyp LIKE eanlh-tariftyp,

END OF ty_eanlh.

DATA: l_date TYPE datum.

DATA: li_eanlh TYPE HASHED TABLE OF ty_eanlh WITH UNIQUE KEY anlage

bis

WITH HEADER LINE.

DATA: li_captran TYPE HASHED TABLE OF ty_captran WITH UNIQUE KEY vertrag

anlage

operand

saison

ab

ablfdnr

WITH HEADER LINE.

DATA: l_tariftyp LIKE eanlh-tariftyp.

• l_date = p_date + 6.

l_date = p_date + 5. "As per Terry's Request 1/23/2009

CHECK NOT p_cap IS INITIAL.

• Get Operand Values

SELECT b~vertrag

a~anlage

a~operand

a~saison

a~ab

a~ablfdnr

a~bis

a~wert1

FROM ettifn AS a INNER JOIN ever AS b

ON a_{anlage = b}anlage

INTO TABLE li_captran PACKAGE SIZE p_pack

WHERE a~anlage IN s_anlage

AND a~ab <= l_date

AND a~bis >= l_date

AND a~operand IN ('ID-CAPLOAD','ID-OBLT')

AND b~einzdat <= l_date

AND b~auszdat >= l_date.

IF sy-subrc EQ 0 AND NOT li_captran[] IS INITIAL.

• Get Rate Category

SELECT anlage

bis

tariftyp

FROM eanlh INTO CORRESPONDING FIELDS OF TABLE li_eanlh

FOR ALL ENTRIES IN li_captran

WHERE anlage = li_captran-anlage

AND ab <= l_date

AND bis >= l_date.

• Get POD ID

SELECT a~anlage

a~int_ui

a~dateto

a~timeto

b~ext_ui

INTO TABLE i_pod

FROM euiinstln AS a INNER JOIN euitrans AS b

ON a_{int_ui = b}int_ui

FOR ALL ENTRIES IN li_captran

WHERE a~anlage = li_captran-anlage.

IF sy-subrc EQ 0 AND NOT i_pod[] IS INITIAL.

SORT i_pod BY anlage.

SELECT vertrag

int_ui

serviceid

FROM eservice

INTO TABLE i_servicect

FOR ALL ENTRIES IN i_pod

WHERE int_ui = i_pod-int_ui

AND service_start <= l_date

AND service_end >= l_date

AND service = 'ESUP'.

ENDIF.

LOOP AT li_captran.

i_ct-anlage = li_captran-anlage.

READ TABLE li_eanlh WITH KEY anlage = li_captran-anlage.

IF sy-subrc EQ 0.

i_ct-tariftyp = li_eanlh-tariftyp.

READ TABLE i_rate WITH KEY tariftyp = i_ct-tariftyp.

IF sy-subrc NE 0.

CONTINUE.

ENDIF.

ELSE.

i_ct-tariftyp = '0000'.

ENDIF.

READ TABLE i_pod WITH KEY anlage = li_captran-anlage.

IF sy-subrc EQ 0.

READ TABLE i_servicect WITH KEY int_ui = i_pod-int_ui.

IF sy-subrc EQ 0.

i_ct-serviceid = i_servicect-serviceid.

ENDIF.

ENDIF.

IF li_captran-operand = 'ID-CAPLOAD'.

i_ct-cap = li_captran-wert1.

ELSEIF li_captran-operand = 'ID-OBLT'.

i_ct-tran = li_captran-wert1.

ENDIF.

COLLECT i_ct.

CLEAR: li_captran, i_ct.

ENDLOOP.

ENDIF.

ENDSELECT.</pre><br><br>

this code is taking a lot of time to execute and decreasing the system performance .<br><br>

please guide me how can i increase the performance of this code .<br><br>

Thanks in advance<br><br><br><br>

Edited by: Matt on Oct 11, 2009 9:45 PM

[Former Member]

Are you done with your issue?

[matt]

I've fixed the formatting and moved to the right forum, but I guess I'm wasting my time.

[Former Member]

Hi matt ,

no i am not thru bcoz of formatting i just trying to remove it and i just marked it answered so that it will not come in my unanswered because unanswered questions are limited.

also could you please help me in this question .

now how can i change this question to unanswerd .

plz guide me

Thanks in advance

[Former Member]

You can click on "Mark as unanswered".

Please do not duplicate post this question again.

Cheers,

Julius

[matt]

>

> You can click on "Mark as unanswered".
> 
> Please do not duplicate post this question again.
> 
> Cheers,
> Julius

Julius asked you nicely, and you didn't pay attention.

I've just deleted/rejected a few duplicate of this question. If you post it again, then is shows that you are incapable of following the rules, and your account will be deleted.

[Former Member]

guyz plz help me in this

[Former Member]

plz guide me in this issue

[Former Member]

Moderator message - Please see before posting - post locked

Rob