topic Re: Serious query optimization.. in Application Development and Automation Discussions

Serious query optimization..

Former Member — Wed, 31 Jan 2007 16:43:31 GMT

Hi,

I am facing a serious performance issue with a query. This query is fetching data definions of all the fields of a given table. Table /Insurance/DD02T has short descriptions of the fields, and hence I am doing inner join between dd03l and /Insurance/DD02T as follows

I was using query :

Loop

select A_{fieldname A}datatype A_{leng B}DDTEXT into table IT_TABDD03L

from DD03L AS A inner join /Insurance/DD02T AS B

on A_{fieldname = B}fieldname

AND A_{TABNAME = B}TABNAME

where A~tabname = P_TABLE

AND B~DDLANGUAGE = 'EN'.

Then read table with:

read table IT_TABDD03L with key fieldname = IT_TABFIELDS-fieldname.

Endloop.

But using above select inside loop was not agood idea, so I used seelct outside loop and then used read statement inside loop as follows:

select A_{fieldname A}datatype A_{leng A}tabname B~DDTEXT into table

IT_TABDD03L

from DD03L AS A inner join /Insurance/DD02T AS B

on A_{fieldname = B}fieldname

AND A_{TABNAME = B}TABNAME

where B~DDLANGUAGE = 'EN'.

Loop.

read table IT_TABDD03L with key fieldname = IT_TABFIELDS-fieldname TABNAME = P_table.

endloop.

But this select is more horrible then previous one which I was using inside loop. Please help me to optimize this..

Re: Serious query optimization..

Former Member — Wed, 31 Jan 2007 16:57:14 GMT

Hi,

In the first place why do you want a unlimited loop...endloop.

Loop.

read table IT_TABDD03L with key fieldname = IT_TABFIELDS-fieldname TABNAME = P_table.

endloop.

The select query is fine.

Regards

Subramanian

Re: Serious query optimization..

Former Member — Wed, 31 Jan 2007 17:07:57 GMT

Do a binary search:


sort IT_TABDD03L by fieldname.
read table IT_TABDD03L with key 
  fieldname = IT_TABFIELDS-fieldname TABNAME = P_table
  binary search.

Nested loops can be more of a performance problem that poorly coded SELECT statements.

Rob

Message was edited by:

Rob Burbank

Re: Serious query optimization..

Former Member — Wed, 31 Jan 2007 17:15:22 GMT

Hi,

Have you looked FM DDIF_FIELDINFO_GET instead?

Regards,

Ferry Lianto

Re: Serious query optimization..

Former Member — Wed, 31 Jan 2007 18:13:34 GMT

good

i think you have done some mistake in the condition that you have given in the second select statement ,plz check that again and do the changes accordingly.

Thanks

mrutyun^