topic SQL trace of a select statement in Application Development and Automation Discussions

SQL trace of a select statement

Former Member — Wed, 10 Dec 2008 09:36:47 GMT

Hi,

SELECT bukrs vkorg kunag kunnr matnr zzdatin zzstock

INTO TABLE t_zrs06_zzstock

FROM zrs06

FOR ALL ENTRIES IN t_zrs06

WHERE bukrs = t_zrs06-bukrs

AND vkorg = t_zrs06-vkorg

AND kunag = t_zrs06-kunag

AND kunnr = t_zrs06-kunnr

AND matnr = t_zrs06-matnr

AND zzdatin LE t_zrs06-zzdatin.

The above select statement is causing a performance issue.I have run the sql trace for this statement.The driver internal table t_zrs06 is having 8 entries.But when I clicked on REOPEN statement of this sl query in ST05 transaction it is showing only 5 entries for matnr field.What would be the reason?

Re: SQL trace of a select statement

HermannGahm — Wed, 10 Dec 2008 09:51:38 GMT

Hi,

the reason is the blocking of FAE (FOR ALL ENTRIES statements). See parameters

rsdb/max_blocking_factor

rsdb/max_in_blocking_factor (for FAE with only 1 'join field' can result in IN lists)

and documentation in RZ11.

You should find another statement with the remaining 3 values in the same trace.

Kind regards,

Hermann

Re: SQL trace of a select statement

Former Member — Wed, 10 Dec 2008 12:08:54 GMT

Hi,

I guess you should reduce fields in where clause without affecting the result because you are cheking many fields in where clause.

rather than if you take all the data from zrs06 into t_zrs06_zzstock and after that

loop at t_zrs06 into workarea

read t_zrs06_zzstock into workarea1 with key

vkorg = t_zrs06-vkorg

kunag = t_zrs06-kunag

kunnr = t_zrs06-kunnr

matnr = t_zrs06-matnr

zzdatin LE t_zrs06-zzdatin.

your code

endloop.

this will reduce (permitation combination while extracting data from database) and result is database hit

will reduce.

ABAP performanace will increase but it will okay....Database is very importanat.

Re: SQL trace of a select statement

Former Member — Wed, 10 Dec 2008 16:53:41 GMT

> But when I clicked on REOPEN statement of this sl query in ST05 transaction it is showing only 5

> entries for matnr field.

after the FETCH or FETCHES tthere should be another REOPEN with 3 entries ...

I can not see the performance problem, how many records come back and how long does it take?

Siegfried