Performance Problem when executing the report for the first time.

[Former Member]

Hi,

We have a Zreport...to improve the performance i created Index on few Data Base table..before my changes it used to take more than 15 minutes after the changes it taking less than 1 min for the same variant and for other variants also.

But when executing the first in morning its taking the same time more than 15 min.Please let me know how to increase the performance when executing first time also,

Thanks,

Kiran.

[alejiandro_sensejl]

Hello Kiran B,

I think this is highly dependent on what your report is doing.

The only idea I can think of (based on the given information) is to try to schedule some jobs to avoid the runtime issue.

[Former Member]

Kiran,

Are you getting the same results when you execute the program in the morning and other times.

use tcode se30 for runtime analysis and check which piece of code is taking more time

Thanks

Bala Duvvuri

[Former Member]

Hi Bala,

Yes im getting the same result for my first time also , Did Se30 and ST05 when executing the first. time..i taking time when fetching data from table for which i created index.if execute the same report second time its taking less time.

May be when doing second time its taking data from Buffer but not sure why this is Happening. If this taki ng from BUffer does index created are usefull or not .Please let me know...im sure i did only changes to report is Created Index in respective data base tables.

This report to see Open Order.

Thanks,

Kiran.

[ThomasZloch]

I hope you investigated all options before creating new indexes, because this should be one of the last resorts.

Yes, it sounds very much like a buffering or caching symptom. Since you did not disclose any more details, it is not possible to give detailed advice. Try scheduling the program as a job early in the morning so that the buffers are alreday filled when the first user calls up the report online.

Thomas

[brad_bohn]

You should not rely on response time as others have indicated; you should rely on your database trace to indicate whether or not your index is working. It sounds like you did not create a good index if the optimizer isn't choosing it, or perhaps you just need a refresh of your DB stats on the server. You will find that your SELECT statement is cached in ST04. You can clear the cache (if you have the authorization) from the transaction and simulate 'first run' every time but this isn't really required.

[Former Member]

Hi all,

Appreciate your valuable replies...

@ Thomas: i do accept with your solution running a background job..but user is not accepting for it..thanks for your reply.

@Siegfried Boes : for testing purpose Im running this manytimes..but for user they may

require when ever they need to know open oders.the volume of the data is barongs 300-350 at

max.

@Brad Bohn : After creating the index the repose time reduced to half ...i hope i have

created a right index.

Observation here...I executed the report in production system more than 5 times it took the

same time, my changes are still in quality..so i think may be for selecting the right index

its taking this much time.and for the next time is taking right index n doing it so it taking

less.

Do you accept it?.if so Please let me know how to explicitly say the select statement to

use my index.

Thanks,

kiran.

[Rui_Dantas]

Hi Kiran,

Solving performance problems is often already hard with specific problems; asnwering in abstract terms is basically impossible.

You still haven't told us:

what is your select;

what fields and indexes you have in the table(s).

I also don't know if you have executed SE30 to confirm that you are really optimizing what needs to be optimized.

The more information you give to the forum the more the forum can give back to you.

Regards,

Rui Dantas

[Former Member]

Hi Rui Dantas,

Sorry for the delay..

I did SE30.before my chnages it taking Approx : 92% in Data fetching from Database.After my changes also it when i execute first time its taking same percentage...but when im executing second its taking approx 66 %..i check in ST05 select statements are taking my Index.

Thanks,

kiran.

[ravi_lanjewar]

Hi Kiran,

You program is working fine, But you should check with basis team for last database static update, It should not be older than one weak.

[Former Member]

What are you actually doing. Your program needs less than 1min in good performance, so how many data does it read?

Generally, you will always see that a second execution of a program using db SELECTs is faster the second time, because the db-cache is used. The factor is usually about 10 or 20 times. But why do you need that many data every day?

[Former Member]

Hello,

You can check if the buffer is actually reducing the 2nd execution time by adding

'BYPASSING BUFFER 'statement in Select queries. This will result in data being fetched always from

the database and your execution time by right should remain same irrespective of whether it is first or second time

execution.

Regards,

Abhilash