2nd post: opening datasets on loading reports

rbuser

After a minor hiccup last week I now have my ASTA 2.06 and RB 6.03 project
working well in terms of performance and report production. I now have just
2 issues on loading reports from the explorer to solve. I have already
posted 1 question but didnt receive any information so I thought I would
repost and ask 1 other question.

Q1. The report explorer appears to need to read all items from the rbItems
table when it is opened including the blob field that holds the report
definition. As I am using ASTA I would prefer to extract only the none blob
fields initially and then when a report is required to be run from the
explorer fetch the blob data then. Is this possible?

Q2. When a report is loaded either from the Report Explorer or from a file
RB
opens the dataset, this is only to display the designer and not actually run
the report, even if the report is only previewed from the explorer the
dataset is opened once when the report is loaded and then reopened (and thus
re-executed) for the preview. This effectively means the query is executed
twice for every report. If this query returns a large result set this gives
a very slow performance. Can be minutes to just load a report. I am using
ASTA 2.6 and RB 6.03. Can something be done about this? Is it necessary for
RB to execute the query on loading a report? I have no objections to users
waiting several minutes for their report to print but just to load a report
is unacceptable to most people. ASTA does have the facility to restrict the
number of rows returned to its client dataset, is there a way I could modify
the Asta Dade such that it creates the dataset for loading the report with a
restricted result set and then removes this when the dataset is recreated
for the report execution, in other words is there a way to tell the
difference between these to events from inside the Dade?

Regards

Simon Callcott

digitalmetaphors

There is the ASTA SQL options property which allows it to conditionally load
BLOBs or not. However, the current report explorer does not support this
functionality. With ASTA you might be able to get this working by modifying
the source or using the Template.OnLoadStart event to add FetchBlobs to the
SQL options set property. Haven't tried it, but it might work. This is a
public event on the template that you'll have to assign in code.

RB should not execute a query when a report is loaded. We'll have to
install ASTA 2.6 and test it to see if we get the same behavior as you are
experiencing.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Thanks Jim,

I am familar with the FetchBlobs in the ASTA SQL options but not the
Template.OnLoadStart. Where can I find the Template object for the report
explorer. Sorry for my ignorance. If possible I would rather avoid modifying
the explorer source directly if possible, but if I only have to assign code
to an event that should be OK.

I can confirm that the query is being run on loading the report, either from
a file or from the explorer. The ASTA server shows me all SQL statements
executed and the reports query is executed just going to the designer. What
functionaility of the Dade should be executed on loading a report? I could
then check using my line profiler to see what functions are actually called.

Best Regards

Simon Callcott

digitalmetaphors

See the Templates thread in our TechTips newsgroup for info on using
template events.


Cheers,

Jim Bennett
Digital Metaphors

digitalmetaphors

We'll have t research why it is firing it for ASTA. For the other databases
we connect to, it seems to work ok. We'll let you know how our test goes.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

I have found the code in daAsta that is causing the SQL to be executed as
the report is loaded,
procedure TdaAstaQueryDataView.Loaded;
begin
inherited Loaded;
{ TODO -ochris -cGeneral : Is this definitely necessary? }
SQLChanged; <<<< --- THIS LINE DOES IT.
end;

In the Loaded event the SQLChanged function is called, this sets the
astaclientdatasets created for the dataview to the magicSQL for the report
and opens them. I have removed it and the reports seem to work fine, so as
the comment above suggests it looks as if this is unnecessary. Do you see
any need for this call?

Best Regards

Simon Callcott

digitalmetaphors

Good work! Sure, take it out. It is calling OpenDataset and that causes the
query to be set to Active = True, which you definitely do not want happening
with this particular timing. Looks like someone needed this call for their
own good reasons, but obviously you have no need to refresh the query. That
should be the fix as SQLChanged should be called by the ancestor dataview
classe when the dataview is out of sync or the sql is set on it. Those
should be the only two times in which that routine should be called.


Cheers,

Jim Bennett
Digital Metaphors