SQLR shows time gaps between OPEN and FETCH

[Former Member]

Hello experts,

I have a program with poor performance. It contains a select loop (SELECT..ENDSELECT). I checked statistics records (STAD) and found that processing time is much greater than CPU time while other components of the processing time are zero. I analyzed the program run with SQL trace (ST05) and then looked through traces of SQL interpreter (SQLR) for my job. And I saw long time gap between operations OPEN and the first FETCH for my SELECT statement. This time gap had the same value for several program runs. I could not check summary total in SQLR because the gap is too long and I just got overflow dump when I tried to look at the summary total.

Does anybody have any ideas why the time gaps can appear?

Could anybody advise me some other tools which I can use to investigate the time gap reasons?

Maybe somebody knows any links where I could find any useful information?

Thanks a lot in advance.

[former_member184455]

Hi,

I guess a long time gap between OPEN and first FETCH could result from an inefficient search on the database until the first result set is returned. If you have an SQL trace for the statement, please post it here and also execute the 'Explain' function in the ST05 for the statement. Then one is able to judge how the database tries to access the data.

Best Regards, Randolf