REALLY SLOW REPORTS 1

[navshiv]

I have these reports that run in a macro daily. There is a filter on these reports so that it only pulls data from yesterdays transactions. Under the line that opens the reports there is a line that says retriveall. These reports are running very slow (up to an hour on some) so could this be why they are running slow? Do I need that retriveall line if I am only looking at data for yesterday?

Thanks in advance

 

[quatermass]

Hi,

The solution will depend on what you are doung with the report.

If you are saving the data either externally or in a hotfile, you will need the retrieveall line.

If the report is for display purposes only, then you will not need the line as further data will be retrieved as the user scrolls through the data.

The 'retrieveall' line is the equivalent of running to the end of the report. If you are saving data via macros , it is advisable to perfom this command to ensure that you save all of the data.

Kevin **************************************************************
The difference between fiction and reality is that fiction has to make sense.
**************************************************************

 

[stanielson]

You might need to add some indexes to your tables and check the SQL that Impromptu is sending to your database enginge to determine whether or not your join logic needs to be optimized.

Stan

 

[amitkg01]

Hi,
See if the filter you have put in reports to fetch the records for yesterday is getting passed to the database or not. You can see it in Report->Query->Profile and then to SQL radio button. If the date filter is not getting passed to the database, it means report is fetching all data from the database and then applying the filter locally.

Use following function for the filter to pass to database. Suppose you are using filter on trans_date of some table in database use following expression

to_date(date-to-string(trans_date), 'yyyy-mm-dd') = add_days(now(), -1).

This should solve the problem.

Regards,
Amit

 

[griffindm]

amitkg01 probably has identified the problem. Impromptu often fails to pass date filters to the underlying database. His solution may work, or may not, depending on what your underlying database is. You may also be able to bypass this by creating a database VIEW of the data that ignores current day data, as in

Create View viewname as Select * from tablename where date-value <= SYSDATE-1;
(Oracle syntax).

This would automatically filter out current date data. You may have to tweak it a bit to handle the time portion of the date.

Hope this helps,

Dave Griffin

 

[navshiv]

I have solved the problem. The problem was Lotus Notes (surprise surprise!). Previously I had Editiors access to the database which ran reports extremley slow (upto an hour with some) but when I was put into Managers access the report went down to about 1 minute!

Thanks for your help.