issue in select query

Former Member — Sun, 11 Oct 2009 18:38:44 GMT

HI All experts,

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>

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

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

ettifn has 10 million records

ever has around 5 million records .

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 .

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 .

<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

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.

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>

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

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

Thanks in advance

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

Re: issue in select query

Former Member — Sun, 11 Oct 2009 18:44:15 GMT

Are you done with your issue?

Re: issue in select query

matt — Sun, 11 Oct 2009 19:48:22 GMT

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

Re: issue in select query

Former Member — Sun, 11 Oct 2009 20:10:59 GMT

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

Re: issue in select query

Former Member — Sun, 11 Oct 2009 21:14:35 GMT

You can click on "Mark as unanswered".

Please do not duplicate post this question again.

Cheers,

Julius

Re: issue in select query

Former Member — Sun, 11 Oct 2009 22:25:47 GMT

plz guide me in this issue

Re: issue in select query

matt — Wed, 14 Oct 2009 08:25:06 GMT


> 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.

Re: issue in select query

Former Member — Mon, 26 Oct 2009 17:18:26 GMT

guyz plz help me in this

Re: issue in select query

Former Member — Mon, 26 Oct 2009 18:04:28 GMT

Moderator message - Please see before posting - post locked

Rob

topic Re: issue in select query in Application Development and Automation Discussions

issue in select query

Re: issue in select query

Re: issue in select query

Re: issue in select query

Re: issue in select query

Re: issue in select query

Re: issue in select query

Re: issue in select query

Re: issue in select query