topic Re: Performance while using Select query. in Application Development and Automation Discussions

Performance while using Select query.

former_member130219 — Fri, 22 May 2009 06:52:41 GMT

Hi all

If we use a select query with sub-queries to acces some records of two or tables using inner join or outer join.

Then is it good to access data using these select query for each user parameter from the database or should we copy the data to an inetrnal table...

And can we use join and where conditions simultaneously.

Please comment on this.

Thank u.

Re: Performance while using Select query.

Former Member — Fri, 22 May 2009 07:01:44 GMT

Hi,

It is recommended that, if your requirement is fulfilled by below process, use it.

First select the data into some internal table based on some conditions if any, and only for those entries in the internal table, using the addition FOR ALL ENTRIES, now fetch the data from the dependeny tables.

Do the required processing on the internal tables.

Use READ on the internal tables for maximum number of times rather than selecting the data multiple times.

Regards,

Santhosh.

Re: Performance while using Select query.

Former Member — Fri, 22 May 2009 07:04:48 GMT

Hi ,

please avoid using the inner join on the select query ,

it will be better if you use saperate select query for the diffrent table . With data

from one standard table in internal table.

It will in prove your performance.

Try following ways to improve the performance.

1) Use package size in the select query if data fetched is too big.

2) Use open curser and close curser.

3) Try to hit the index maintained for the standard table

hpe this will help

regards

Prashant

Re: Performance while using Select query.

Former Member — Fri, 22 May 2009 07:08:08 GMT

Hi,

Instead of joins use the for all entries option.

for eg.

select matnr, mtart

into table imara

from mara

where matnr in s_matnr (select-option)

select matnr labst

into table imard

from mard

for all entres in table imara

where matnr = imara-matnr

Thanks &Regards,

Kimaya

Re: Performance while using Select query.

Former Member — Fri, 22 May 2009 07:13:29 GMT

Try this , here performance has been improved to very high extent

SELECT kokrs "Controlling Area

belnr "Document Number

refbz "Posting row of reference document

bukrs "Company Cod

owaer "Currency Key

objnr "Object number

kstar "Cost element

buzei "Posting row

INTO TABLE t_coep

FROM coep

WHERE objnr IN lr_objnr AND

kstar IN lr_kstar AND

gjahr IN s_gjahr AND

%_HINTS ORACLE'INDEX ("&TABLE&" "COEP~2")'.

*Oracle hints is use to explictly hit the index

***filtering the data based on the selection screen

company code, cost element.

LOOP AT t_coep WHERE

NOT bukrs IN p_bukrs OR

kokrs <> p_kokrs OR

NOT kstar IN lr_kstar.

DELETE t_coep.

ENDLOOP.

IF NOT t_coep[] IS INITIAL.

***Fetch data from CO Object: Document header based

***on all the relevant conditions.

SELECT kokrs "Controlling Area

belnr "Document Number

refbn "Reference Document Number

refgj "Fiscal year of reference document

refbk "Company code of FI document

bltxt "Doc.header text

awtyp "Reference procedure

aworg AS awkey "Reference organisational units

awsys "Logical system of source document

INTO TABLE t_cobk

FROM cobk

FOR ALL ENTRIES IN t_coep

WHERE kokrs = t_coep-kokrs " controlling area

AND belnr = t_coep-belnr . " acc. document

filtering the data based on the selection screen.

LOOP AT t_cobk .

IF NOT t_cobk-refbt = c_acc_doc

OR NOT t_cobk-gjahr IN s_gjahr

OR NOT t_cobk-refbn IN lr_refbn

OR NOT t_cobk-perab IN s_monat.

DELETE t_cobk.

ENDIF.

ENDLOOP.

ENDIF.

hope this will help

regards

Prashant

Re: Performance while using Select query.

Former Member — Sat, 23 May 2009 19:00:01 GMT

Hi ,

As a Thumb rule whenever there is minimal optimal no of database fetches then that is the desired phenomenon in any database fetch situation thus an 2 stage internal table phenomenon is always better than a one step complex select query .

More over from a programatical stand point it is also desirable to do so becoz we would be able to identify the exact process of fetch if an internal table is initialized and updated at one step level instead of a complicated fetch and join operation which is generally not considered effecient using and also the order of data retrival is not very transparent when debugged becoz only the final fetch result of the sellect query will be reflecting in the internal table . -- In other words your process is transparent

Regards.

Re: Performance while using Select query.

Former Member — Mon, 25 May 2009 04:49:36 GMT

HI,

perormance wise it will be good if you use inner join some times it will fetch unwant data also.

regards,

Munibabu.K