Hi All,

I run the report from the Launch Page. It is taking atleast 15 minutes to genrate the report, It used to take not more than 2 minutes earlier.

Could anyone tell what might be the issue here, Where do I have to look for the problem to be resolved.

Thanks,

This message has been edited. Last edited by: nsk110483,

If you are going against a relational database you need to trap the SQL to see what is happening with your report. There are a number of posts on this topic in the forum. The most likely problem is your SQL is deoptimized. There are several ways I can think of that this might happen.

Check-list

1) Did pgm change recently?
2) Did database/table/Indexes get altered recently?
3) Did volume of qualifying data change recently?

If these are OK, start actual code review

4) -EXIT at each step in code and see which step seems to have increased in time.
5) If relational db, capture SQL and run some Explains.

Still do not see a problem? Pray and comeback next day with a fresh outlook to start over.

Sandeep Mamidenna

As part of defensive programming, I will often add a DM step to grab the time after each of various steps and then -TYPE the &vars at the end of the procedure such that the elapsed times are available by a 'view source.'
In so many instances, databases are optimized for maintenance, not reporting, and WF gets blamed for the glacial response.

Do your SQL Trace and copy that in to a SQL tool (SQL Developer or something like that) and do an "Explain Plan" to see where you may have a database / table / JOIN issue. Perhaps someone is hold the tables open as exclusive?

I have made a few changes on the code. all other reports in that domain which uses same tables are taking less time to execute.

I open the view source, but I dont know what exactly to look for in it, other than parameter passing.
I am new to WF, please help by giving all options, different scenarios.

Performance depends on knowing your data (tables, joins, indexes, etc) and also your server/network configuration, bandwidth etc.

If this program is long enough, did you try doing a -EXIT after each step to identify the bottle-neck?

Did you try having your DBA run an "Explain" on the executing query(ies)?

If it is a short piece of code, post it anyway. It will be difficult for an outsider to suggest performance improvements by looking at the code unless it is a blatant mis-step.

Sandeep Mamidenna

If nothing else works, it would not be a bad idea to open a case with IBI.

While it is not necessarily a techincal issue, IBI tech-support had been gracious with code help for us in previous instances.

Re: Blue Zone's and Doug's posts, since you changed the program and are new to WebFOCUS, you may have disrupted what WebFOCUS is sending to the backend data base engine.

Put the following lines at the beginning of your program:

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
SET XRETRIEVAL=OFF

then change your output to HTML. Run the program. Do a view source. Scroll to the bottom. You will see the generated SQL. If you see more than one SELECT statement, you will see error messages above that will indicate what the problem is. If you don't understand the messages, please post them and we can help you.